Some time ago one FileMaker developer, when discussing calculation performance, responded to me like "If it evaluates in a few milliseconds then I don't really care how long exactly it takes." This is a very dangerous statement because you can easily get into a situation when every millisecond matters a lot.
Read the story below or watch the recording of the FMTraning.tv live stream to learn how a seemingly innocent calculation can saddently kill your app's performance. In my demo, I got time to switch between layouts from easily acceptable 129 milliseconds to insane 15 seconds!
How it all started
My encounter with the above mentioned dangerous statement happened a few years ago when I was working on the first version of my PNG Canvas example and I was looking for the fastest possible way to calculate a CRC-32 checksum. I had to admit there are not many people out there crazy enough like me that they would try to generate a valid PNG image solely using FileMaker calculation...
Yes, this was an extreme use case, but I already knew from my experience that there are situations when even a very simple and innocently looking calculation can get an otherwise well designed FileMaker solution to its knees very easily. That's why I submitted my idea to make the calculation engine significantly faster back in late 2020. Since then several new functions were added to FileMaker, the use of calculations in FileMaker solutions has kept growing quickly, especially with more people starting to work with JSON, but the core of the engine has remained the same.
So earlier this year I came up with the Initiative '24 idea and decided to focus on making the submitted idea become a reality. Some of you have already supported my by voting for my idea, because you trust me that it will make impact. For those of you who are not sure whether it's worth supporting yet, I have done a very simple test to verify and show you, how even a millisecond can really matter a lot.
Don't believe me, see for yourself
I took a small starter solution created by a well-known experienced FileMaker developer - the Confident Theme from Alexis Allen, and made a very few changes, including adding one calculation.
All the innocently looking changes were inspired by actual needs that arised in our own system many years ago. And I measured the impact of each change on one simple action - switching from the Contacts layout to the Invoices layout.
Then I demoed all the changes and measured the impact again live in the Richard Carlton's FMTraining.tv stream. Here's a recording of that session:
I did not use any complicated calculations with loops or recursion. My seemingly innocent calculation took less than 200 microseconds to evaluate. Yet it brought the simple solution from pleasantly performant to horribly slow with just a few changes.
How I measured the impact
To measure how much time FileMaker spends evaluating my calculation, I used the new Get(CurrentTimeUTCMicroseconds) function, which on its own has only negligible overhead of a few microseconds. For a reason that will become obvious later, I also used a global variable to count how many times my calculation gets actually evaluted.
As a benchmark reflecting something that user will notice, and thus having impact on the perceived performance of the solution, I decided to measure a response to a standard user action. I chose switching from one layout to another. To measure how long it takes as precisely as possible, I took advantage of the fact that a script scheduled to run does not start until FileMaker Pro becomes idle, which is after all previous actions are done and layout rendering after switching context is completely done.
I first discovered this technique back in 2017 and it may (and is likely to) become obsolete in the future, but for now we still seem to be able to measure rendering delays by setting an onTimer script trigger with 0.001 sec interval.
Now I was ready to make the (seemingly harmless) changes and see their impact....
The changes I made
To get a bit closer to a real situation, I uploaded the solution to a FileMaker Server and generated 5000 fake invoices. For each invoice I added between 1 and 20 random line items, and marked half of the invoices as paid and 90% of the unpaid ones as approved.
Alexis used a simple text status for invoice in her template. We started with something similar 24 years ago but very quickly realized that we need to know whether invoices were paid early, on time or late, and for the unpaid ones whether they over due or there is still time. Another real-life discovery was that customers don't always pay the full amount at once. So I simply added a field to hold the total amount already paid and date when the last payment was received. To indicate the more granular payment status I added this calculation:
To make it sensitive to the current date changing every day, I made the calculation unstored. Today with all the experience I would, of course, chose a different approach but for the sake of simplicity and to demonstrate the danger of that "few milliseconds don't matter" statement. I measured the calculation time on a few sample invoices and it took (seemingly negligible) 100-200 microseconds to evaluate.
So I added the PaidStatus field to my overview layout, and with 10 records shown it got evaluated 10 times, consuming 1.28 ms in total. Nothing unexpected, but here you may be already getting why I wanted to count the number of evaluations... Yes, many little strokes fell great oaks...
How often have you heard a manager asking: "Can you make the text red and bold when the invoice is overdue?" or something similar? There's no doubt that when FileMaker 9 brought conditional formatting it was one of the most welcome new features. And how painfully missing it was in the first version of FileMaker WebDirect! But one small insidious caviat came with it. So I added some conditional formatting to see this caviat in action...
Guess what, the number of times my calculation had to be evaluated for the same 10 records, jumped straight to 310 and the time spent evaluating it to over 35 milliseconds.
It's esy to understand why. When you add 5 rules, all of them referencing the calculation, it will trigger 5 times, because all rules will get evaluated. In addition to having multiple rules, I also applied them to multiple objects - actually 6 fields in this case. So to render the same 10 records in the list view, I got my 100-microsecond calculation evaluated 300 times just because of the conditional formatting.
This used to be even worse back in FileMaker 17, every rule got evaluated 4 times back then, probably to pre-calculate the styles for all 4 different states - inactive, active, hover and pressed (my guess). I am not sure which exact version has removed this inefficiency but I was happy to find out it's now over in FileMaker 2023 and 2024.
Until now I was still working as a full access user. The next change I made was defining a new privilege set for an intern. They were supposed to see only invoices which were due or overdue. So I added this simple restriction based on the PaidStatus:
This added another 420 evaluations and time to switch to the Invoices layout increased to 132 milliseconds.
Then I added one more change that finally killed the solution. I made the script switching to the Invoices layout also sort all the invoices by PaidStatus. With the calculation being evaluated 6463 times I had to wait almost fifteen seconds to see the invoices appear. And that all was on a local network. When I performed all the same tests over the internet, it was even worse.
What does all this mean?
Of course if you try the exact same thing you'll revert back immediately. At least in the case of the sort. This was an extreme example. but it was just a single calculation. In real life, you keep adding negligible overhead here and there and the solution gets slower and slower like a boiling frog (putting aside the fact that the popular story is actually false). With FileMaker having more and more places where you can actually use calculations, the number of calculations to be evaluated with every layout switch, every button click, every record load, every field modification, every <whatever>, keeps growing. So the number of reasons to make the calculation engine faster keeps growing with it...
Every vote counts
If you were not sure yet but the above information have convinced you this is something worth supporting, then don't hesitate and help us convince Claris as well. Help us make FileMaker faster for everyone. This is where we are in making it the #1 idea:
Already voted?
Please spread the word. You can help with more than one vote simply by telling your friends and colleagues who care about FileMaker. You can use the sharing buttons below.
