Claris FileMaker is an excellent tool for getting stuff done. It gives us lots of space and versatility, like a minivan that can take our kids to soccer practice and stow a ton of groceries from the local supermarket. Our FileMaker custom apps are just as multi-faceted. But when we inevitably store a lot of our data in those custom apps to leverage its powerful ability to solve all sorts of problems, the system can get kind of pokey. When FileMaker becomes our tool of choice for every challenge that we confront, chances are our system will start to show the strain of all this virtual responsibility. Let's discuss some strategies we can use to solve those pesky FileMaker performance issues. None of our suggestions will transform your daily driver into a race car; however, it may feel more like one.
FileMaker Search Speed
Let's first talk about what FileMaker does pretty well, which is finding stuff. If we're diligent about indexing the fields that we typically use for searches, we'll be rewarded with industry-leading speed. FileMaker enhances speed with the recent addition of the page-level locking feature. This process allows the server to spend less time returning the requested information when other users make the same requests, as long as the app is hosted on FileMaker Server 18 or above. However, there's another edge to this indexing sword. Be sure to only index the fields users search on regularly because the index adds weight to the file. So, where it speeds up searches, it can slow down things like record creation. This slowdown occurs because FileMaker updates both the data in the field itself and the index attached to the field. So, it's doing double duty.
FileMaker Record Speed
We can't ignore one of the things FileMaker does rather slowly, record creation/modification. Now we have to say that record creation/modification does require a lot more work than finding stuff, so it's not necessarily surprising that it takes longer. When we create or modify a record, the server needs to:
- Verify user access rights
- Lock the record so that other users aren't able to edit it
- Make space in the file for the record
- Update indices and resolve any applicable auto-enter calculations
- Notify the hosted file's active users that a new record has been created and/or changes have been made
How do you get through this performance bottleneck as quickly as you can? The key lies in keeping the host from knowing about the new records or modifications until the absolute last moment. We may have to apply some creative maneuvering and a few trusty FileMaker scripts. For example, if we need to create a bunch of records simultaneously, we can try to utilize the Import Record script step since it's usually faster than a scripted looping New Record Request. The Import Record function batches its communication with the host while giving us the option to globally or selectively turn off Auto-Enter Calculations from firing. We can even combine the two record creation methods by creating records with a loop in a local file and then import them into the hosted solution. Incidentally, we can easily maintain and deploy local files using a container field. Simply export it when we need it and update it by replacing it.
Imports that are linked to local files offer possible solutions to our record creation scalability issues. But what about changing existing records, especially when there are a lot of them? Before we talk about the possible solutions to this problem, let's chat briefly about why updating records can be as laborious as creating new records. The FileMaker database engine retrieves, locks and stores data at the record level. It doesn't send data to and from the host, field by field, but instead record by record. This means that when we're merely modifying one field on a record, much of the effort associated with creating a record from scratch still comes into play. We could try to streamline the process as we described above with the new record creation technique by modifying records in a local file then import them using the matching option. However, most real-world workflows often make this an awkward method. Importing records using match fields is handled differently internally by FileMaker, which often negates some of the batching benefits.
We can try to use the Replace Field Contents command or a looping Set Field command. In many circumstances, Replace Field Contents proves to be a lighter-weight process than a looping Set Field, which requires more steps to take place in the background. A Freeze Window script step used before each technique can help speed things up while using either of these commands. One thing to keep in mind is that if we need to modify a lot of records (10,000's) or multiple fields in each record, we will find that a looping Set Field provides a certain level of control. In the case of editing numerous fields within a record, it's generally fast, depending on the number of fields and the required degree of modification.
Up To Speed
Hopefully, one or more of these approaches will be just the thing to speed up a slow workflow. Next, we'll tackle the performance struggles that can accompany remote access to our FileMaker based apps via the Internet. We'll also deal with unstored calculation functions and unexpected hits to the performance that affects our screens and reports.
This article is also published on FileMakerProGurus.com.