In a previous post, I discussed how using FileMaker to test the speed of FileMaker databases runs into unexpected problems: the act of measuring influences the measurement. The question then is, how do we proceed if we do need to do some testing? In this post, I will discuss my approach to speed testing FileMaker with FileMaker, and how it makes sense to me. Well, until I discover or someone suggests a better approach. Do feel free to leave comments and suggestions!
The short and simple of my approach is:
- Remove external influences
- Remove database internal influences
- Be aware of FileMaker’s internal workings
- Test strategy
- Start simple, then increase complexity
- Average many measurements but inspect the individual measurements
- Compare different solutions
- Start simple, then increase complexity
The different steps are more complicated than the list suggests as will become clear in the following chapters dedicated to each step respectively.
1 Remove external influences
The more subtle version of this title is : Remove all known external influences as much as possible, and as much as makes sense.
Unpacking that sentence, let me start with ‘all known’. There is so much going on in ( and around ) a microprocessor, a computer system and an operating system that it may take decades to learn what all external influences could be. For purely pragmatic reasons, I settle for those that I do know, which will be discussed below.
‘As much as possible’: Since I lack the knowledge about all external influences, I also don’t know how to remove them. As will become clear in chapter 4, it is fine if some influences remain in place, as long as they influence the test in a constant and consistent way.
‘As much as makes sense’: many processes running on my computer, should in fact remain running in order to have a functioning computer in the first place.
Still, here is a list of things to consider when running tests:
- Other applications
If one does not need them while running the FileMaker application or somehow support the test, one also does not want them to interfere with the test. Just switch them off.
Do not forget the applications that are time triggered, such as the OS’s back up application, and automation apps like Shortcuts in MacOS.
- Notifications
Programs like the email client and the calendar, produce notifications, which may interrupt the test. Make sure they are switched off or that their notifications are switched off.
- WiFi, Bluetooth
The same applies here, but they may be needed while running the FM application.
- Low power mode
Make sure to either have it consistently on or consistently off. At least on a MacBook Air M1, I have seen low power mode kick in and significantly reduce processor time available to FM.
- Screen saver
During long running tests, the screen saver may kick in. It probably takes only a small percentage of the processor but it may have indirect effects. For example, one may have to log in to see what is happening, which will temporarily require resources.
- Available disk space
When disk space is getting low, the operating system may suddenly start using processor power and disk access to manage disk space and/or make more available.
- The cat
One thing not to do while running a test is pressing a key. FileMaker may respond with the prompt ‘Before Typing, press Tab or click in a field, or choose the New Record menu command’ and require the user to click ‘ok’. That will interrupt the running test.
Disclaimer for the following items in this list: I know about FileMaker server software but I am not a server expert.
- Other databases and other users
The server that runs the test database, may run other databases and the test database may be used by other users. This may influence how much time the server can dedicate to the session that runs the test, and thus influence the test. Unless one wants to run the test on the live server/production server, it would obviously be better to eliminate these influences by using a dedicated server with only the test database open.
- Whatever else is on the server
If you want to test on a server, the obvious thing to ask is what else is the server computer doing? One may rent a cloud server or use a non-dedicated hosted server. In those cases, other people are running other databases or even other services. All of which may influence how much processor time the server dedicates to a database and a session.
Once these influences are checked and eliminated, it is time to move to the next step, internal influences
2 Database’s internal influences
To determine how fast or slow a particular process runs, it is important to make sure that one measures what one wants to measure and nothing else in addition. A typical example would be the OnRecordLoad script trigger that is set up for a layout in which the test runs. Another would be calculations that run on the layout itself, such as show/hide conditions, portals, filters on portals and conditional layouts.
In order to check for possible conditions, one can consider a spectrum of approaches, ranging from ‘start from scratch’, to ‘leave all in place’. Both have their pros and cons, and the middle ground of eliminating ballast is likely where it all happens.
2A Start from scratch
With starting from scratch, one attempts to build a database file which contains only the elements needed to perform the test. Typically, one starts with an empty file, perhaps even devoid of the default themes. Use the ‘Save a copy as XML…’ menu command and inspect it for unnecessary baggage. The upside of this approach is that one eliminates all possible influences by not introducing them in the first place. The downside is that ‘only the elements needed to perform the test’ can be many and complicated, so building such a version can be laborious.
2B Leave all in place
Leaving everything in place does not require any building or adjusting of the existing database. However, one needs to use the script debugger and all other possible means to make sure that one knows what one is measuring when speed testing one particular aspect of the database.
2C Eliminate what one does not want to measure
In between these extremes, is the area where one carefully analyses what elements could be influencing a speed measurement, and then decides which of those elements to include in the test and which to eliminate. This can be a matter of repeated adjusting and testing. In fact, this may be part of finding out which elements hamper performance ( the topic of a future post ). One can think of deleting ( groups of ) elements from a layout, switching off script triggers and calculations in calculation fields, removing relationships, de-activating ( blocks of ) script lines, and much, much more.
Here is a catch though. One CAN NOT DO such actions on the production server and database because it will change it’s functioning dramatically. Instead one must switch to a copy on a separate test server, something which one should do in any case for testing and developing. However, this takes away an important factor that influences speed: the users and their daily actions.
3 Under the hood. FileMaker’s internal workings
Lastly, be aware that FileMaker’s internal workings, often referred to as ‘under the hood’, may influence measurements. There are at least the following to take into account.
3A Lazy loading and lazy evaluation
Firstly, FileMaker employs lazy loading. If it does not need to show certain elements on a layout, it will not and will not evaluate any of the calculations needed for those elements. FileMaker also uses lazy evaluation. If in a calculation, a part does not need to be calculated, FM won’t. See this post by Richard from Dyce & Co. Ltd. from 2014 that provides anecdotal evidence.
Lazy evaluation for speed testing means that FileMaker may be faster than one would otherwise expect.
3B Record caching
In form layouts, FileMaker will not only load the record that it is showing, but also 25 records more. It does this to enable more smooth record browsing. However it means that FM may be busy loading 25 times more records than one would expect, which makes it unexpectedly slower. The actual delay depends on the record sizes, the server specification and load, and network latency. It may be noticeable. Or not.
Both FileMaker’s lazy behavior and record caching are two of those old commonly known facts about FM’s internal workings for which I have been unable to find the original source. However, I did find this Claris Community post from 2014, which also offers additional facts about FM under the hood.
3C Data viewer
Do not forget to check FileMaker’s data viewer before running a test. If the data viewer is not showing, it does not or can not influence the speed measurement. However, one may need the data viewer while the test is running.
The catch is that it may show calculations that potentially require substantial processing time to evaluate. Make sure to delete them or deactivate them by redefining them so that lazy processing renders them relatively harmless to speed measurement.
Having dealt with external and internal influences, it is time to address test strategy.
4 Test strategy
My test strategy consists of two elements: A) start simple before increasing complexity. B) inspect the details before averaging. They will be briefly described in the following sub sections.
4A Start simple, then increase complexity
The chapter title says it all. My basic approach would be to start simple and then increase complexity. This means a couple of things
- Start testing on a stand alone client, rather than a server. After all, servers are much more complex machines.
- Start testing using ‘Start from scratch’, exploring speed of basic FM elements, such as a script step or native function.
- Start testing with the simplest means possible in order to keep check on how the measuring impacts the measurement. See my earlier post.
4B Average many measurements but inspect the individual measurements
Speed tests should be repeated many times because of the various unknown internal and external influences that one can not control. The idea is that their effects aggregate to an average delay.
However, during some tests where FM repeated certain script step many times, I also captured the measurement of each repetition, i.e. each individual measurement. This turned out to be very insightful. In fact, it revealed effects that I might not have discovered if I had only looked at averaged measurements as I had done before.
One can have a discussion about what to do first: first take averages or first look at the individual measurements. Since I am currently taking my first steps in speed measurement, I am inclined to inspect individual measurements first before averaging. However, at some point the individual measurements may not provide surprises anymore. Then taking averages would be the starting point.
When averages are the starting point, then there can be at least two reasons for diving into individual measurements:
1) New unexplained results occur in the averages.
2) Hubris. Averages may hide interesting behavior which may be introduced through more complex database schema, scripts, layouts or calculations. So, even when one feels confident that one knows FM’s behavior, random inspection of individual measurements might reveal surprises.
4C Compare different solutions
Usually, one is not interested in the speed of the functioning of a given database or function thereof, but in speeding it up. This means that one usually is interested in comparing the relative speed of different ways to achieve the same functionality.
Admittedly, when it comes to developing a faster way to achieve the same functionality, it does help to know that/if certain functions, script steps, constructs or design patterns are faster than others. This may be an incentive to catalogue the speeds of functions and script steps. However, I would not be surprised that at least some may depend on their context within the database or their parameters. For example, calculating Sqrt ( 4 ) may be much faster than Sqrt ( 10000 ), and setting an indexed field used in many relationships may take a lot longer than doing the same for a not indexed field.
Keep in mind that if one compares different alternatives to achieve the same functionality, they should be tested under the same circumstances. If external and database internal influences can not be eliminated, one should at least check that they play constant and consistent roles during the tests.
5 Concluding
This post has set out my approach to FileMaker speed testing. It discussed external influences, database internal influences, FileMaker’s internal workings, and my test strategy. In future posts, I will report on my speed tests that use this approach.
Rubber Boots Data 