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.
The techniques of this post are limiting the use of ExecuteSQL and how to reduce wait times for a user when all else fails. For the time being, this is the last post in this series. After this, I will be focusing on more advanced techniques and on speed measuring. However, I may in the future return with an update to some of the topics in this low hanging fruits series. Keep an eye out, or even better, subscribe to receive news on updates in your mailbox.
10. Limit the use of ExecuteSQL
The ExecuteSQL ( ) was introduced in FM 12, and in my experience a real game changer. For at least three reasons. First of all, it allowed very easy-to-program access to data in not related tables. Secondly, one did not need to hard code relationships for it to work, they could be defined on the fly. It supported joins, after all. This has the advantage of de-cluttering the relationship diagram. Thirdly, since the function accepts the query as a regular text, the query could be determined at run-time instead of at programming time, which gave a whole new level of flexibility. When I started working with it, it was instant addiction.
However, it has one big downside, which is that, in a client-server setup it gets slow once bigger tables are involved. The reason for that is that all record data in affected tables has to be transferred to the client. In a way, it does the opposite of the Perform Script on Server script step which completely eliminates such transfer. On a stand-alone application, the use of ExecuteSQL is less demanding, since the data does not need to travel over the network.
There is a way to reduce the data transfer: cut up the affected tables into multiple tables, so that less data needs transferring. This is possible, but quite an undertaking, especially if it means refactoring inside a fully developed solution. Also, even if a table is cut up, the resulting parts may still be of significant size and continue to create noticeable delays.
So, it would be better to use those alternative means to get to the same result. That is, working through hard coded relationships, finds, value lists and other mechanisms.
10.A Limitations
An obvious limitation is that in some cases, there is no alternative but to use ExecuteSQL ( ). For example when the run-time level adjustment is a necessity. Relationships can not be made at run-time and pre-coding all possible necessary relationships maybe undo-able.
10.B Price
It should be clear by now that once excessive time-costly uses of ExecuteSQL ( ) in an existing solution need replacing, the amount of work can be considerable. Also, not using ExecuteSQL ( ) may result in significant expansion of the relationship diagram.
10.C Source
This is a well known technique under FM developers, and can be found in the Claris engineering blog.
11. Distribute wait times for the user
The last technique is left for the end of this series for a reason. It might be the last line of defense when all the low hanging fruit and all possible advanced performance increases have been implemented. The wait time for a certain action may not be reduced anymore, but it may be redistributed over time.
The idea is that users do not find it acceptable to wait, say, 10 seconds, but they might be willing to wait 10 times 1 second. A typical example would be that instead of processing the input from a big form after a user clicks on ‘Done’ or ‘Save’, the user is guided through a wizard which cuts up the form in smaller pieces. Such a change would not count as refactoring because the functionality does change, but the same approach could be used. When a long script involves preparation steps, those could be whizzed away to another occasion where the user would not object to a small delay, ( or to a timed script that runs on the server, but that would not fit the title of this chapter ).
11.A Limitations
As may have become clear above, the technique can not always be applied. The time eating script should be amenable to it, and there should be other wait occasions where the work can be transferred to.
11.B Price
One price to pay is that one needs to make sure that all the preparation has indeed been done when running the script from which it was moved away.
11.C Source
I thought this might be a ‘cool trick’ to end this series with. It turns out there is a whole science or at least a professional interest area of user waiting and the perception of waiting in software development. Search and one will find for example the Medium post ‘The UX of Waiting and the Perception of Time’, by Chriss Kiess, who promotes and draws on Designing & Engineering Time by Steven C. Seow
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.
Rubber Boots Data 