Last October I began a series on Outer Joins, which explored a number of different ways to display summarized information in a grid, and at the time I concluded that the “fastest” method was to leverage the FileMaker relational model. And it was plenty fast, locally… and not too bad on a LAN… and technically, it was the fastest method on a WAN but only because the other methods we looked at were even dog-slower than it was.
I like to test solutions on a WAN, even if they’re only going to be deployed on a LAN, because it’s a great way to uncover performance bottlenecks. But recently I needed to deploy a summarized grid on a WAN, and was incentivised to come up with something faster… and after a bit (well, okay, a lot) of trial and error settled on the approach we’re going to look at today. To cut to the chase, with a million records in the test file, the previous best grid rendering time of 11 seconds on a WAN has been reduced by a factor of 10, to just over one second.
So the goal is to quickly render a 7-day grid showing total daily sales per employee, and today we have two demo files, and two versions of each… one with 30,000 sales records, and one with 1 million sales records:
- Grid Display v1
- Grid Display v1 (with 1 million sales records)
- Grid Display v2
- Grid Display v2 (with 1 million sales records)
Grid Display v1 is pretty much the same as Demo 3 in Outer Joins in FileMaker 12, part 1, or more precisely, Demo 3d from the comments at the bottom of the article. It is well documented there, so I’m not going to dwell on it, but will mention in passing that the heavy lifting is done via table occurrences and predicates, i.e., a standard FileMaker relational approach to building a grid. [To be clear, v1 is slow on a WAN — I include it for purposes of comparison.]
Also in the above-mentioned comments section is a suggestion from Arild Schonberg to try looping through the sales records and pushing summarized employee daily totals into $$variables. This is known as the Fast Summary technique — thank you Mikhail Edoshin for documenting it, promoting it, and whatever part you played in its creation. It’s a powerful, game-changing magic spell to have in your bag of tricks, which I wrote about in detail in June 2011 (Fast Summaries), and is in a large part responsible for the high-octane performance of v2, not to mention the leanness of the Relationships Graph.
Normally when an FM12 graph is this sparse, there’s a good chance the solution is using ExecuteSQL, but I promise that no SQL was executed in any of today’s demos. Now as you might imagine, each Employee has a primary key…
…which of course corresponds to a foreign key in Sales:
…and the heavy lifting is done by the “increment date” script.
Thanks to Fast Summaries, the script skips lightly through a found set in Sales for the seven day range we want to see in the grid, generating and populating a repeating global variable like so: $$Ennnn[x], with the main portion representing the employee id, and the x representing a repetition number between 1 and 7, corresponding to the date column position on the grid.
To accomplish this, the script uses a technique known as “dynamic variable instantiation“, which we first explored on this blog nearly two years ago:
Let’s look at a concrete example. Here are the seven days on the Week View:
Lester Downum, whose employee id is E0018, has sales on days 2, 3, and 5…
…and here are his variables in the Data Viewer.
These variables are rendered in the Week View by this repeating calculation:
(The ~wakeUp variable helps the summary repeater at the bottom of the layout stay responsive.)
And here’s the definition of that summary repeater:
A few other points of possible interest:
1. In all the demos, after the “increment date” script has executed, you can view $$elapsedMilliseconds in the Data Viewer, to see how long (in milliseconds) it took. Thank you Nick Orr for clueing me in to the existence of the Get(UTCmSecs) function, which a) makes this possible, and b) is undocumented.
2. In v2 I moved the date_g and date_r fields out of the Dev table and into Employees (to facilitate summary field responsiveness, as per the above ~wakeUp variable comment).
3. For readability, I declared these variables in the “increment date” script:
They are referred to in the step immediately below the two highlighted ones, and I suspected I could gain a slight performance boost by expanding that step from this…
…to this…
…and eliminating the two highlighted Set Variable steps above. But having tested both approaches multiple times, it doesn’t appear to make a bit of difference either way.
4. Variables can not only be dynamically instantiated; they can be dynamically evaporated as well. Since the number of columns in the week view is limited to seven, we know that’s the maximum rep # we’ll need to worry about… so like the valiant little tailor of legend, we can loop through the records in the Employees table, and kill seven at one blow, like so:
And I think that’s enough for today… at least from me. I am very interested to hear your thoughts about making this technique even faster, or if you see any problems with the v2 approach… I hope you’ll submit a comment and let me know.