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.

In this post : one tip which applies to all programming, but has a maintenance trap, and one very straightforward performance tip regarding setting variables.

 

8 Test the most likely condition first

A likely thing to do in scripting when things get complicated is going down a row of calculations ( condition_A to D in the examples below ) to determine what to do and then do that thing. The thing to do could be as simple as returning a boolean value. For example

condition_A OR condition_B OR condition_C OR condition_D  

FileMaker does lazy evaluation, so as soon as a condition evaluates to True it will stop calculating the other conditions since the overall result will be True. Similarly, in a concatenation of AND functions, it will stop as soon as a condition evaluates to False.

Perhaps intuitively easier to understand, the Case ( ) function works in a similar way. I think that that is because of the nature of the function, not because of the lazy evaluation, but it does not matter for this tip: as soon as a condition evaluates to True the remaining conditions will not be evaluated anymore.

Case (
     condition_A ;
          do_A ;
     condition_B ;
          do_B ;
     condition_C ;
          do_B ;
     condition_D ;
          do_D ;
     do_default
)

And in scripting one can use the If, Else If, Else, and End If script steps and make a structure similar to the Case ( ) function.

For those who have not yet guessed the tip yet: for best performance and if the business logic allows(!), the condition that is most likely to prevent later conditions to be tested ( i.e. the condition that resolves into True in case of the concatenated Or functions, the Case ( ) function and the If script steps; and the condition that resolves in to False in the case of the concatenated And functions ) should be evaluated first, and all remaining conditions ordered in descending likelihood.

Do note the addition that the business logic should allow it. Sometimes, that is not the case. For example if the business logic prescribes that a process should pass the conditions in a given order. This clearly applies to the Case ( ) function and the If, Else If, Else, and End If script steps. The logic of the concatenated boolean functions in principle does not easily allow for programming an order.

8.A Trap

Also do note, that there is a trap in this tip. The chance that a given condition leads to further evaluation cessation depends on the data. And of course, the data may change over time through various means, which may – or may not – change this chance. This means that in order to maintain best performance, the data and the resulting chances for the different conditions should be checked on occasion or regularly ( which of the two again depends on the data ). One could in fact program a routine that checks this at certain intervals of time or number of data changes.

8.B Limitations

I am not aware of limitations to this technique.

8.C Price

One price to pay is less readable code. After all, ordering the conditions by their chance of occurring may result in a counter intuitive order.

8.D Source

This tip is based on Claris’ engineering blog ‘Top Tips: Optimizing Performance of FileMaker Custom Apps in the Cloud‘. I assume it is a well known tip among all coders and developers.

 

9 Use Let ( ) to set multiple variables in a script

If in a script one needs to set several variables in a row, then for performance improvement, one should replace them by one Set Variable with a Let ( ) function in its calculation.

The Let ( ) function allows to set not just variables scoped locally to the function, but also $ variables and $$ variables, and they can be mixed. They will be set in order of appearance, which means that variables set lower down in the function can access the variables that are set higher up. Here is one way to do this.

Set Variable [ $variable_n ;
     Let ( [
          $variable_1 ;
               calculation_1 ;
          $variable_2 ;
               calculation_2 ;

          ...

          $variable_n-1 ;
               calculation_n-1 ;
          ] ;
          calculation_n
     )
)

Notice that the calculations that define a variable can also contain Let ( ) functions, so in combination with a conditional function, different sets of variables could be created, simulating the use of If blocks in a script.

Both the Set Variable script step and the Let ( ) function come with a certain amount of overhead, so there is an n up to which setting n variables is faster when implemented as individual script steps and as of which it is faster when implemented through the Let ( ) function. Anecdotal testing ( is that the phrase for just one or two tests? ) taught me that that n equals 5, at least on my MacBook Air M1, running FM 19. Admittedly, more testing on the performance increase is needed. So perhaps it is not such a great low-hanging fruit after all. Follow this blog for more, or subscribe to be updated.

Note that instead of the Let ( ) function, one could also use the While ( ) function, ( introduced in FM 18.0 ) which has a similar part to initialize variables. The While ( ) function is however far more complex, so it is not the easiest choice. Well, unless, one want to use its iterative logic to set the variables. This would involve the use of a Let ( ) function inside an Evaluate ( ) function inside the While ( ) function’s logic part. This is far from low hanging fruit, and because of the use of the Evaluate ( ) function, probably not fast at all.

9.A Limitations

The limitations to this technique are given by the fact that a calculation can contain up to 30.000 characters.

9.B Price

The price for this technique is a less readable script compared to a series of Set Variable script steps. In the latter case, one can immediately see the variable name in the Script Workspace and at least a part of the calculation. With this technique, one has to go into the calculation setting of the one script step to see which variables are defined.

9.C Source

This must be a commonly known technique, but I could find no on-line source particularly advocating it. The reason is probably that most developers consider the price of less readability too high.