Two weeks ago I wrote an article about a FileMaker custom function I needed to preprocess some data imported from the web. I used this custom function in an auto-enter calculation to immediately preprocess the data while being imported. I created a server-side script that does the import every morning. But when I discovered that the import was taking over an hour every day, I saw it deserves some optimization. I used FM Bench Detective and optimized the custom function to evaluate up to several hundreds times faster…
I used FM Bench Detective to measure the exact time each depth of this recursive custom function took to evaluate, and even split the calc to three parts to find out which part was taking how much time.
The key discovery for me was that of the about 73 milliseconds every single recursion was taking, 69 milliseconds was occupied by the Substitute function that really only needed to be performed once int he topmost instance. As I mentioned in my Marvelous Optimization Formula, the more stupid is the original solution the more marvelous the optimization seems.
Just by making sure that the Substitute function is only used once, in the topmost instance, I was able to cut the evaluation time for every consecutive recursion from the 73 milliseconds to just about 5.9. Actually, I later discovered that the measuring overhed of FM Bench Detective was around 1.7 milliseconds per measurement and I had 3 measurement points inside the calc, so the actual time per recursion without the measuring code could be around 68 milliseconds before the optimization and less than 1 millisecond after the optimization.
I then further optimized the calc by replacing the PatternCount function with Position, cutting down the time per recursion to about 5.6 milliseconds including the measuring overhead.
Since the measuring overhead is so big I can only estimate that I cut the actual time per recursion from 68 to 0.8 milliseconds by eliminating the unnecessary Substitute and from 0.8 to 0.5 milliseconds by replacing PatternCount with Position.
Anyway, as a result, the custom function now evaluates about twice as fast when it dives into 1 recursion, and over 100 times faster when diving into the 100 levels deep recursion.
The recursion depth directly depends on the number of enumerated HTML entities in the source text (instances of the “&#” string), so the more enumerated entities you have in your text the more you will benefit from this optimization.
And by the way, it still can be optimized further, but I’ll leave that to your elaboration… ;-)
Download Sample fp7 File (193 KB)
Enjoy, and please let me know your optimization experiences by leaving comments either here or at the FM Bench Detective page.
