Speeding FileMaker Performance with Truncate Table

Managing Data You Use and Throw Out

We develop FileMaker applications because we want a place to store information for the long term. We track events, tasks, contacts, finances—FileMaker can track almost anything. To update information, the application might ingest a spreadsheet.

You usually don’t want spreadsheet rows to feed directly into data tables, since you risk calamity should the columns be mismatched with target fields; you bring data into the application, validate it, and (often) massage it before updating regular data. After you process temporary data, you need to get rid of it.

In the past, Delete All Records represented the only option for removing large numbers of records. After a user performed a function such as the import mentioned above, the system would process the imported data, and then a progress bar would appear, informing the user that a (sometimes large) number of records was being deleted. You don’t want a user sitting through a slow process or worrying about losing information.

Removing the Wait and Concern

FileMaker 15 offers a means of reducing wait times and anxiety for users. Enter the new Most Dangerous FileMaker Command for Developers: Truncate Table. The word, “truncate” means to shorten or cut short; Truncate Table deletes all of the records in a table—the current table or one you select. “Remove Table Data” or “Delete All Records And I Mean It This Time” would be a more accurate name. (The name comes from a SQL command.) The only data not removed from the table when Truncate Table executes are values in global fields, with the exception of global container fields, which lose their values. Truncate Table will not delete child records–you still need Delete All Records for that requirement. Users must have full access privileges to run Truncate Table, which also distinguishes the command from Delete All Records.

We found that, when the Truncate Table script step is called either directly from a script or using Perform Script on Server, the user doesn’t see a progress bar or a message about deletion, and doesn’t have to wait—the records disappear right away.

 

Testing Truncate Table

To test Truncate Table, we created a file with one table in it with 5 fields and 100,000 records. When we ran Truncate Table in the local file, it took less than a second to remove all of the records. We hosted the file using FileMaker Server 15 at Beezwax headquarters in California. I opened the file from Massachusetts, where I am, using FileMaker Pro. I ran the command, and again, the records were gone in less than a second. I asked another Bee in California and one in Switzerland to perform the same test. Here is what we found. Spoiler alert: we had to measure the time in milliseconds.

Truncate Table on a Hosted File

Here are our test results. The times are proportional to each person’s distance from the server, but they might as well be the same in terms of the user experience. Poof, the records are gone.

Time to delete 100,000 records
Type of Network	Time
LAN	.043 seconds
WAN (Massachusetts)	.276 seconds
WAN (Switzerland)	.389 seconds

To introduce some funkiness, we hosted the test file on a FileMaker 14 server, opened it in FileMaker Pro 15, and ran Truncate Table. We received error 3: Command is unavailable. When a file is hosted, then, it must be hosted with FileMaker Server 15 in order for Truncate Table to execute. FileMaker Server and the FileMaker client appear to be in cahoots, working together under the hood to execute the script step.

Trying Perform Script on Server (PSoS)

We were delighted at our findings. But we thought of Perform Script on Server, which so often cuts performance time for scripts. Would Truncate Table prove still faster if we ran it via Perform Script on Server?

Once again, we hosted the file on FileMaker Server 15, at Beezwax headquarters in California. Opening the test file with FileMaker Pro 15, we ran Truncate Table using PSoS. Whether we opened it on a LAN or WAN, it took one second to remove all of the records. Once again, the records vanished. (See the table below for more details.)

We opened the test file from Massachusetts with FileMaker 14 and 15 at the same time. When we triggered the function—Truncate Table using PSoS—from the FileMaker 15 client, it took 18 seconds to remove all of the records. With the file open on both clients in one screen, we watched records disappear in batches.

We tried simpler tests, using only FileMaker 14 or only FileMaker 15. The hardworking Bee in Switzerland performed the same tests. Even with FileMaker 14, the deletion process when performed server-side was relatively fast. With FileMaker 15 client, the function happened in a second.

Truncate Table using Perform Script on Server

Time to delete 100,000 records
Type of Network	FileMaker 14	FileMaker 15
LAN	7 seconds	1 second
WAN (Massachusetts)	21 seconds	1 second
WAN (Switzerland)	24 seconds	1 second

When compared with running Truncate Table from the client, then, PSoS did not enhance performance. It may have even made it slightly slower, though I doubt anyone would notice the difference between a quarter or half second and a whole second. PSoS opens the script server-side, which must require overhead that makes the function take a bit longer.

What makes Truncate Table so fast?

Truncate Table locks the selected table, returning an error if even one record is being edited, then communicates to clients that the records in the table have been deleted. If all of the clients are running FileMaker 15, the server passes down the ID of the table whose records were removed. This is why records vaporized in our first test. If one or more clients are running FileMaker 14, though, FileMaker Server has to delete records the old way, passing down record IDs one by one. To take full advantage of Truncate Table, all users must be running FileMaker 15.

Truncate Table in a Multi-user Environment

Developers should give careful consideration to how to use Truncate Table in a multi-user environment. Take the scenario I described earlier, in which an application ingests a spreadsheet, processes data, and then removes the temporary import data, as an example. If more than one user attempts to run the function at the same time, the function will derail one of the user’s efforts. Say a user imports records into the temporary table, and a second user imports records a few seconds later. The import function completes for the first user, and as a last step, the function performs Truncate Table on the import table, deleting all imported records—including those imported by the second user. Records vaporize before the user’s bewildered eyes. To protect against this happening, the script needs to set a flag field when someone initiates the import function. Before doing anything else, the script should check to see if the flag was set and, if it was, give the user feedback to try the import function again later.

Great Benefits for Users

In reducing wait time and anxiety, Truncate Table makes the user experience significantly better when a system must ingest and process data, whether triggered directly or using Perform Script on Server. The kind of programming that used to cause a noticeable lag now executes in a second. The user might not even notice it. Invisible programming–we like that.

Special thanks for Mark Scott, Stefano Lesandrini, Vincenzo Menanno and others who helped with WAN testing for this blog post.