The other day some colleagues were discussing a need to produce a JSON representation of the data in a summary report. Long time readers of this blog will know that I am obsessed with fond of the Fast Summary technique, and it seemed like it might be a good fit for this challenge, so I built some demos to find out if that would be the case.
Demos 1 and 4 will work with FM 16 or later. Demos 2 & 3 utilize recently-added JSON features, so require FM 21 or later.
- Fast Summaries to JSON, v1 (requires FM 16 or later)
- Fast Summaries to JSON, v2 (requires FM 21 or later)
- Fast Summaries to JSON, v3 (requires FM 21 or later)
- Fast Summaries to JSON, v4 (requires FM 16 or later)
General Info
The four files contain the same data and are identical in almost every respect, except for the script that produces the JSON. We’ll take a look at each of these scripts in a moment, but first some general info.
1. Open any of the files and click here.
2. When the report appears, click here.
3. Click “Arrays” or “Objects”.
4. And after a brief interval an informative dialog like so will appear.
Note: as per item #3 above, you can produce an array of objects…
…or an array of arrays.
Demo #1 (FM 16 or later)
As I mentioned a moment ago, under the hood the four files are virtually identical, except for the contents of this script: “fast summary output report to json”.
In case you aren’t familiar with the Fast Summary technique, or if you need a refresher, the basic idea is to loop through a sorted found set, using the GetSummary function to grab aggregate info — in this case, monthly sales totals for each product. We don’t need to visit every underlying record, just the first one for each unique group. This ability to skip lightly across the found set is what makes the technique fast.
Here the “fast summary” action takes place at steps 18-20 and then at step 29.
Depending on which button the user clicked, the JSON is constructed at lines 22-23 or lines 25-26. And here’s how long these two operations take on my Windows 10 laptop.
Demo #2 (FM 21 or later)
This demo takes the same approach as demo #1, except rather than declaring and incrementing an $index variable, we target array addresses using the new “[+]” argument for JSONSetElement.
The “[+]” argument streamlines coding but has no discernible effect on runtime performance. The JSON structures produced here take about as long to generate as in demo #1.
[Update 4 Sep 2024: make sure to check out part 2 for a simple trick to cut this execution time in half.]
Demo #3 (FM 21 or later)
In demo #3 we get to use another new-in-21 JSON addition, JSONMakeArray, and this time we get a jaw-dropping performance boost when we point that function at a list of JSON structures produced via Russell Watson’s awesome “Insert Calculated Result” trick.
And it is blazingly fast. So fast that when I first saw these results I figured there must be a bug in my script. Well, there wasn’t — these results are the real deal.
Demo #4 (FM 16 or later)
This is a backward-compatible version of the preceding, using simple substitution in place of JSONMakeArray…
…and the performance is similar to what we saw in demo #3.
Closing Thoughts
Today’s demos are based on a file I built more than ten years ago to accompany this article: Generating Sample Data. I left the scripts and interface in place so you can easily create more invoices should you be so inclined. Run the highlighted script…
…and you will be presented with this dialog.
If you’re not sure what to enter, try clicking Example Values.
Now I’ve added significantly more invoices to my copy of demo #4. There are about 20 times more array entries to generate, and the routine creates approximately 5,000 of these entries per second.
And I think that’s enough for today.
Insightful as ever Kevin. Interesting to see that the Insert Calculated Result trick continues to provide substantial performance improvements over the native JSON functions.
Amazing. Thanks for producing these examples and files!
Fast summaries! I love this technique, thx to your articles.
I noticed a JSON cache-busting issue in Demo1 and 2 causing the large speed disparity with Demo3 and 4. On my computer, it speeds up from 5100ms to ~3000 ms with this tweak:
The script should only “touch” one JSON object until its completely done writing it in a loop, in this case $$summary.report.json. So instead of first writing $object/$array, and then inserting that into $$summary.report.json, it can just write to $$summary.report.json directly. Something like this in Demo1:
// ✅ FAST ($$summary.report.json stays cached the whole loop)
Set Variable [ $$summary.report.json ;
JSONSetElement ( $$summary.report.json ;
[ “[” & $index & “].product” ; products::description ; JSONString ] ;
[ “[” & $index & “].year” ; invoices::year ; JSONNumber ] ;
[ “[” & $index & “].month” ; line_items::month_name ; JSONString ] ;
[ “[” & $index & “].amount” ; $amount ; JSONNumber ]
)
]
Instead of this:
// ❌ SLOW (it busts the json cache every time it creates $object)
Set Variable [ $object ;
JSONSetElement ( “{}” ;
[ “product” ; products::description ; JSONString ] ;
[ “year” ; invoices::year ; JSONNumber ] ;
[ “month” ; line_items::month_name ; JSONString ] ;
[ “amount” ; $amount ; JSONNumber ]
)
]
Set Variable [ $$summary.report.json ; Value:
JSONSetElement ( $$summary.report.json ; $index ; $object ; JSONObject ) ]
Thanks again for spreading the word about this awesome technique, and I hope that json cache trick is helpful!
Best, J
Thank you Josh. I just tested and your comment is spot on. I was already planning to do a “v2” follow up, and now I’m doubly incentivized. Really appreciate you taking the time to point that out.