High Performance FileMaker
Creating a FileMaker solution which simply works is much easier than one which performs well. Yeah! You’d like to assume that FileMaker Inc. will take care of everything for you and your FileMaker solution, no matter how you build it, will just work perfectly and as fast as possible.
Here’s the catch. You can hand a hammer to pretty much anyone. However, using it, in the most efficient manner, is a totally different story.
You must remember, the FileMaker environment knows nothing about how people are going to use your solution and the data it contains.
Shove 500kb of data into a record and when anyone accesses it they’ll get 500kb back - even if they only need to see 50kb only 80% of the time!
This is where you, the developer, enters the picture and starts to answer the hard questions. What do I show? When? To who? How?
The answers to these questions dictate how you approach the structure and design of your solution. This is combined with the little pieces of know-how you pick up from resources like this magazine. With the right tools in hand, and the knowledge of how to use them, you can create a MUCH BETTER performing solution with only a slight bit more effort.
Comments
Refreshing Display Variables
Some nice techniques.
I tested the local display var approach (using the Hide If calculation) to generate some portal totals (instead of having a second portal on the layout to do summary total). The issue was that it did not refresh on record load when paging through records using the standard FM next/prev commands.
A Refresh Window will resolve it (refresh object is not sufficient).
Does this mean I need to trigger a window refresh on each record load (which seems expensive), or do I have another option?
Flushing join caches
Yes, in some cases, depending on how the data is retrieved, you'll end up having to use the Refresh Window (with Flushing join caches), particularly if it's data coming through FileMaker relationships.
In this case, you mention you were using a Summary field and showing it within a portal.
This would, in my option, possibly dictate a change in approach. A summary field is an aggregation of data. There are multiple ways to get at this. If the data is only going to be needed when viewed, then I would look into using ExecuteSQL and doing the aggregation there, using a SUM function or equivalent.
Whenever (and however) FileMaker has to display a summary field it must do the aggregation at time of evaluation because they data could have changed.
If the aggregate data is not SUPER critical to be "real-time live" then your best bet is typically running a server side script on a schedule and storing this data. That is typically the fastest. If you need it calc'd when a user views it then ExecuteSQL can be your friend because you can limit what is retrieved and shown.
Also, consider that when you retrieve data from ExecuteSQL, some aggregate values may already be there. A good example of this is the ValueCount() function on a return delimited list of data as opposed to running a second ExecuteSQL with a COUNT(*) function.
I hope this helps out.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Thanks Matt.
Thanks for the reply Matt.
I have converted most of the portals now to selector-connector types, with FQL used to populate the global field with keys, and then a simple summary totals for the displayed records (the portal supports five different filters, so was easier than trying to write the total as a FQL version of each. I think it is faster, but hard to judge in current stage of development.
One followup question on narrowing your tables. Let's say I take some infrequently used fields and push them out to a remote 1:1 table. E.g. Order_Data. Now on my Order detail layout, I still have those fields but they are hidden (e.g. revealed for certain scenarios).
Are those related fields still loaded every time the order record is pulled down anyhow (i.e. no saving), or does the related data only get pulled down from server when the HIDE IF resolves to false?
Twitter says your Devcon session was great, no surprise, but congrats!
Hidden fields are still transferred
You said: "Are those related fields still loaded every time the order record is pulled down anyhow (i.e. no saving), or does the related data only get pulled down from server when the HIDE IF resolves to false?"
As far as I know the record data is still transferred and the rendering, as to whether hidden or not, is handled when the layout is rendered.
-- Matt Petrowsky - ISO FileMaker Magazine Editor