This is a great thread, thank you. A couple of questions for the noob that I am.
So if I understand correctly lots of very flat (one level) joins is 3x faster than less joins in a spider-web. It actually seems easier to understand too, which is a plus.
How do you get rid of joins in this case? So I have a "bookmarks" table which joins students to teachers. Each teacher has zero or more bookmarks. Each student has the same. The anchor looks great because it's so flat, but I feel like I'd need at least 3 levels on most of my tables.
I guess I could use filters on portals, is that preferred? My impression was that was pretty slow and it's faster to make those connections in joins.
We probably need to get you to use different terminology. I don't understand what you mean by "flat" and "one level."
The Spider graph describes the situation where every TO is connected to every other TO. The Anchor Buoy method is a set of rules around splitting up TOs into functional groups. The side effect of this is that these Table Occurrence Groups ( TOGs) are smaller than a Spider graph of the same application.
The thing that we see in the tests above has to do with CONTEXT. In a spider graph, every layout will be linked in some way to every piece of data in the application. So loading the data for the context could involve a lot of TOs and a lot of data. In an AB context, the background loading needed for the layout context is less. One of the conclusions from the experiment above is that context matters. Even when there is no explicit action, the work needed to prepare the context for an action takes time and energy.
1 Like
Your bookmark table is a join table, correct? So your students to teachers relationship is many-to-many. In a spider graph everything is connected in one big relationship. In Anchor Buoy there are multiple groups with the anchor (parent) on the left and the buoys flowing to the right. Each layout is based on a different anchor.
Thanks... yeah this is all new. The example screenshotted above only has one level of buoys to the right. So parent - 5 children, what if you need a join table, do you then go two levels deep, or three if really needed? I'm new to this level of optimization.
It's ok to go as deep as you need within your Table Occurrence Groups. If you have an anchor Teachers connected to TO Bookmarks connected to Students, that's totally fine.
The main point is that we should avoid linking all (or most) of our TOs together. It's better to keep relationships only as deep as needed and keep the various anchors/base-TOs unlinked, at least from the standpoint of PSOS, which might actually not really be that common in your system.
For a small system with just a few tables, this probably won't matter that much either way. My example above assumes 60 anchor TOs, and Honza's system was quite a bit more complex.
I guess I could use filters on portals, is that preferred? My impression was that was pretty slow and it's faster to make those connections in joins.
This is a good question and the answer should dictate what you do in the graph. If a relationship-level filter creates a better user experience, but requires adding an extra relationship in the graph, I think it's probably still worth doing. The penalty for one extra TO is small if it means your interface feels snappier.
1 Like
WAIT A MINUTE. Is the anchor Buoy trick ONLY pertaining to "perform script on server" or every interaction users have? I'd been thinking of it as a "general optimization trick", but is that true?
And thank you all for putting up with my noob questions.
I donāt view these as noob questions. Some of these questions would be best answered by the Claris engineers themselves, that's how non-noob they are!
In short, I donāt know, I havenāt tested. But the only thing Honza really talked about and the only thing I tested are the time it takes to start a new session in a filemaker file. Starting a new session includes opening the file from your client (eg Fmgo or fmPro), and also calling PSOS, which is basically starting a new client session on the server (thatās why it has its own context and variables).
Honza asserts that Clay (Claris) told him that filemaker maps out the graph when you first start a session, and that that takes longer for complex graphs.
So new sessions is all weāve really measured here. I suppose you could write some tests to check other things like @jormond mentioned above or portal filtering like you brought up.
4 Likes
Thanks appreciate the time you've taken to help me.
1 Like
Portal filtering takes the records it's fed from the relationship... then loops every record and runs your calculation on every one. So performance is 100% about record size, and complexity of the calc.
If your calc can be answered by simply using the index, it will be FAST. If not, all normal performance impacts apply. For semi-complex filters, if your calc takes 250 ms secs to resolve, and you have 10,000 records showing in your relationship... You are looking at 41 mins to resolve that portal.
Now 250 ms is an outrageously long time for a calc to resolve. It's rare, but it demonstrates the point.
If your calc takes:
- 10 ms | @10,000 records | 100,000 total ms ( 100 secs )
- 9 ms | @10,000 records | 90,000 total ms ( 90 secs )
- 8 ms | @10,000 records | 80,000 total ms ( 80 secs )
- 7 ms | @10,000 records | 70,000 total ms ( 70 secs )
- 6 ms | @10,000 records | 60,000 total ms ( 60 secs )
- 5 ms | @10,000 records | 50,000 total ms ( 50 secs )
- 4 ms | @10,000 records | 40,000 total ms ( 40 secs )
- 3 ms | @10,000 records | 30,000 total ms ( 30 secs )
- 2 ms | @10,000 records | 20,000 total ms ( 20 secs )
- 1 ms | @10,000 records | 10,000 total ms ( 10 secs )
- 0.5 ms | @10,000 records | 5,000 total ms ( 5 secs )
- 0.25 ms | @10,000 records | 2,500 total ms ( 2.5 secs )
- 0.1 ms | @10,000 records | 1,000 total ms ( 1 sec )
- 0.075 ms | @10,000 records | 750 total ms ( 0.7 secs )
- 0.05 ms | @10,000 records | 500 total ms ( 0.5 secs )
- 0.03 ms | @10,000 records | 300 total ms ( 0.3 secs )
- 0.01 ms | @10,000 records | 100 total ms ( 0.1 secs )
Since so many of the calculations can complete ~300-500 times before your screen refreshes, many calcs, once they have the data available, can complete in 0.03 - 0.01 ms per record.
Getting the data, well that depends on if your local cache already has the data, or if it has to retrieve it from the server. This is the slowest part of all database interactions. And this is where most system start to see performance issues. This is completely about database structure, and distance from the server, computer cores, network latency, etc.
If your calculation references data in another table, then you start to see even longer times, because the server now needs to send those records to FileMaker Pro to evaluate.
4 Likes
So the 2 important pieces here if you use portal filtering, is to
- limit the number of records you are getting through the relationship, to prevent unnecessary computer cycles.
- keep the calculation as simple as possible.
3 Likes
Anchor Buoy is not a trick. It's a method. It has a lot going for it, especially as a pretty-sensible-starting-point. It provides a set of guidelines that are easy to understand and implement. It has the very real benefit that it will allow your system to grow in complexity and in data sizes without causing you pain.
Breaking the system into small TOGs based around the functional needs of a table is a form of optimisation. You will find plenty of advice on getting better performance but AB gives you pretty good performance. It doesn't require anything clever or special, so your brain won't need extra bandwidth when you work with it.
picking up on @jormond 's two pieces of advice. If the calculation is a string comparison and the string is a constant, for example, address::state = "XYZ" then the filtering can be done on the server.
7 Likes
Howdy ... and thank you for the files Josh.
2 Likes
The other huge benefit of AB is it is like having comments written about the flow of data in the file. As a developer, and particularly if you arenāt the original developer, you can read what data the developer expected the user to see/use. If it wasnāt for AB, and a strong naming convention, Iād spend a lot of time trying to remember what I was doing!
4 Likes
Hi @gordoncox, welcome to the soup !
2 Likes