Rubber Boots Data

Introduction to the series.

Given a FileMaker production setup, such as a given server box and settings, concurrent user count and network connections, what can one do to improve the speed of the database? That is, how can one refactor it: redesign and reprogram to improve speed while keeping the functionality exactly as it is? There are many ways, but before getting into nitty-gritty topics, this post and the series it belongs to, will deal with low-hanging fruit. They give an overview of all the easy tips and tricks that I came across during the past ten years or so.

This post addresses two easy scripting and calculation improvements .

 

6 Insert instead of set a variable

One way to collect data from high number of records ( or websites or other sources ) is to visit them one by one and store the collected data in one variable. Suppose that one wants to collect all product descriptions that are stored in the field products::description. A script could loop through all records and add the description through the Set Variable script step

Set Variable [ $collected_descriptions ; Value: $collected_descriptions & ";" & products::description ]

Russel Watson, an FM developer also known as Mr. Watson, discovered in 2018 that using the Insert Calculated Result step resulted in dramatically better performance.

Insert Calculated Result [ Target: $collected_descriptions ; ";" & products::description ]

Note that the option to Select entire contents should NOT be selected so that the calculated result is added at the end of the variable.

Using this script step still will slow the script progressively down. However, with this step, progressively means linearly progressive, whereas with the Set Variable script step is means exponentially. Mr. Watson’s explanation for Set Variable being so much slower is that under the hood of FM, variables are so-called immutable objects: they cannot change after they are created. This means that to perform the Set Variable script step, a new variable is created and upon its creation, the entire contents of the old one is copied into the new one and then extended with the new data. In some way, he figured, Insert Calculated Results is based on a work around that extends “appends the text onto the end of the existing variable by resizing the existing string buffer.” ( source, stress in original ).

Typically, since computers are blazing fast nowadays, the effect will typically only be noticeable to uses with high iteration counts. Mr. Watson reported an improvement from more than three minutes to under seven seconds for 200.000 iterations.

6.A Limitations

Technically, one limitation sits in the maximum size of a variable. I can not find it in the technical limits sheet, but I believe a variable can contain as much text as a text field and according to the sheet, a text field ( in FM 2025 ) can contain 10.000.000 – 1 characters. For 200.000 iterations that would be on average 50 characters per iteration, which is something, but not a lot.

Another limitation is that the Insert Calculated Results has no equivalent function. So that it can not be used in a While ( ) function or a recursive custom function. However, a tail recursive custom function might emulate a similar mechanism as Insert Calculated Results. It would be worth the while to find out, but it would not be a low hanging fruit anymore. Keep an eye out for a later post on this topic. Or, even better, subscribe to updates, please!

6.B Price

As far as I am aware, there is no price to pay for using the Insert Calculated Results instead of Set Variable.

6.C Source

Mr Watson presented his finding at the DotFMP conference in Berlin June 2018 and later posted it at the then FileMaker forum, now the Claris community forum.

The finding created a shock wave in the community and found many re-posts and discussions. For example Kevin Frank in 2018, and Tony White in July 2020.

 

7 Choose Choose ( ) instead of Case ( ) in case you can

In complex situations, one may easily end up using a Case ( ) function to subsequently test for different conditions and execute the calculations declared at the first condition that resolves into True. Possible use cases can be found in applications that support pipeline type of processes that have to pass stations one by one, such as going through a payment process, renting out a machine, or supporting production lines in a factory.

If Case ( ) condition calculations are costly to calculate, then perhaps Choose ( ) can be an alternative. This works a lot faster because the Choose ( ) function requires only one calculation, which has to result in a natural number ( i.e. 0, 1, 2, 3, etc. ) and which has to have a calculation to perform for every natural number ( to the maximum that can be the case ).

7.A Limitations

One limitation is of course that the list of the subsequent conditions of the Case ( ) function needs to be projected onto the list of natural numbers, and it should obviously do so without using the Case ( ) function. If that is not possible or only at great costs, then this tip will obviously not be possible or no improvement. Hence the ‘in case you can’ in this chapter’s title.

In the pipeline use cases however, the last passed station number is the stored result of all the previous tests, so it embodies the logical result of a lot of previous calculations. Which means that there should only be a need for calculations that take care of moving from one step to the next or a next step. There should be no occasion for a Case ( ) function going through all different steps. It also means that the different calculations are distributed over time ( See also the post about that strategy – currently not yet written ).

Unfortunately, there is no equivalent script step of Choose ( ). In fact, there is only an equivalent of Case ( ).

7.C Price

If one introduces this method at a late moment in time of the database’s development, the price to pay is a lot of refactoring work. The sooner one takes it into account the better.

7.D Source

I don’t know of any source that published this method, but since the distinction between Case ( ) and Choose ( ) is so basic, not only in FileMaker, I believe that every developer is or should be aware of it.

 

 

About this post

If I can, I will give references to the on-line resources that first pointed out the tip or trick. If a reference is missing, or if you know an earlier reference, please do let me know through a direct message or comment.

I wrote the post myself, without the help of an AI. The post’ featured images were made with the help of WordPress’ AI driven image generator plus a lot of trial and error with prompting it.