FileMaker Transaction Scripts Example
As we discussed in our previous blog post, FileMaker has released a new feature set involving transactions. The new scripts allow us to send the changes we make to a record to the server in aggregate. So, we can batch all our changes together in one transaction and send that to the server.
If you read our blog regularly, you might remember we created a test file to demonstrate a technique on how to create multiple records at one time. We can also use the new transaction scripts to accomplish this task. For example, instead of creating 10,000 records in a loop one at a time, thereby producing 10,000 individual transactions, we should be able to submit those 10,000 new records as a single transaction.
CREATE MULTIPLE RECORDS VIA TRANSACTION SCRIPTS
By way of review, we discussed two different methods for creating records. We can run a script to create the records one at a time in a loop. Or, we can write a script that imports the records from a file. Either way, we can add an Open Transaction script step and the Commit step to each script. Note that adding the transaction state doesn’t require much change to the existing scripting. All we had to do was pay attention to where to open the transaction state and where to commit it.
Here’s what they look like with the adjustments:
The last time we ran these scripts, the loop took 24 minutes to create 10,000 records, and the import took 13 seconds.
Running them presently, with the new script steps, significantly improves performance:
The loop script process time decreased from 24 minutes to 2 seconds. And the import script processed in less than a second. Incredible!
It was such a remarkable improvement that we wanted to see what would happen if we increased the record count. So, we changed the script to make 100,000 records, and something crazy happened:
The loop script took 6 minutes this time, which seemed wrong. Why would 10,000 records take two seconds, but ten times that number take six minutes? We did some testing and realized there is a time threshold – not quite sure what it is – when the screen refreshes and displays the created records. The time lag may have had something to do with FileMaker having to display the records as it was generating them. We changed the layout from table view to form view and deleted all of the fields on the layout, essentially creating a blank layout. The populated layout may have contributed to why the script ran so slowly, even without the Open Transaction script step.
Having made that change, we reran the script:
Much better. Here’s what both script times look like together, side by side, with the new script steps implemented:
The import methodology still works better than the loop script, but the difference between the two has gone from minutes to seconds using the Transaction state. That’s a HUGE performance leap.
Not only that, but several times during our testing, we canceled the script while it was running, and the transaction state was still open. FileMaker automatically reverted all of the edits while the transaction was open. We didn’t have to worry about cleaning up orphaned records or undoing whatever the script had done before we canceled. This function is also practical and will lead to cleaner databases with better data integrity and fewer errors in scripting and data management.
So at the end of the day, the new transaction state script steps will improve the speed and stability of our applications. Furthermore, they are easy to add to existing scripts and can be extremely powerful using the right approach.