Tim Dietrich

Custom Software Developer

Home Services Portfolio Blog About Contact Newsletter

FileMaker 13: Optimizations Using "Perform Script on Server"

One of my favorite new FileMaker 13 features is the "Perform Script on Server" script step (hereafter referred to as "PSOS"). PSOS enables us to send a request to a FileMaker Server so that it will run a specified script on the client's behalf (using the server's resources). Optionally, you can indicate whether or not the client should wait for the result of the script, or just proceed without waiting for the result.

There are a number of potential uses for PSOS. For example, neither FileMaker Go nor WebDirect currently support plugins. However, you can overcome that limitation through the use of PSOS, so that a script is run on the server and it makes use of a plugin that has been installed on the server. Pretty cool stuff!

Another potential use of PSOS (and one that I'm particularly interested in) is to use it to optimize data-intensive processes, especially those where large amounts of data are sent from the server to the client. For example, when updating large numbers of records or performing certain SQL queries using the ExecuteSQL function. The goal is to use PSOS to handle those types of tasks on the server, and therefore avoid any need to move large amounts of data to the client.

I ran a series of tests to see what PSOS was capable of. Here are a few notes about how the test was performed:

  • The database that I used includes 33,000 records, which are sample sales leads.
  • The table includes 48 fields, including the usual suspects (company name, street address, city, state, zip, etc).
  • The City field, which I use in ExecuteSQL tests, is indexed.
  • Also, after each test, I closed FileMaker Pro (to ensure that nothing was being cached - at least not on the client side, anyway).
  • The tests were performed over a LAN using WiFi, with a MacBook Air (Mid 2011 version) as the client and a Mac Mini (Mid 2011 version) acting as the server.

ExecuteSQL Tests

Test 1
Query: SELECT City, COUNT(*) FROM Leads GROUP BY City
Run on Client: 35 seconds
Run on Server: 28 seconds

Test 2
Query: SELECT City, COUNT(*) FROM Leads GROUP BY City FETCH FIRST 25 ROWS ONLY
Run on Client: 41 seconds
Run on Server: 28 seconds
Note: This query is identical to that used in Test 1, except that I added the "FETCH FIRST 25 ROWS ONLY" to see if reducing the size of result woud yield even better performance from the script was run on server. Surprisingly, it seemed to have no real impact.

Test 3
Query: SELECT City FROM Leads ORDER BY City FETCH FIRST 25 ROWS ONLY
Run on Client: 12 seconds
Run on Server: 6 seconds
Note: In this test, the records are sorted by City and only the first 25 rows are returned. No "GROUP BY" clause was specified, so the performance of the query was improved when it was run on both the client and the server.

Test 4
Query: SELECT DISTINCT City FROM Leads ORDER BY City FETCH FIRST 25 ROWS ONLY
Run on Client: 13 seconds
Run on Server: 6 seconds
Note: This test is very similar to the third test, except that only the first 25 distinct values of the City field are returned. The results were very similar.

Bulk Update Tests

In these tests, the script found all 33,000 records in the Leads table, and then used the Replace Field Contents script step to update the value of a text field. Note that two variations of the "run on server" test were performed. In the first, the PSOS script step waited for the remote script to complete. In the second, it sent the PSOS request to the server, but did not wait for the remote script to complete.

Run on Client: 3 minutes and 30 seconds
Run on Server (and waited for the script to finish): 14 seconds
Run On Server (without waiting for the script to finish): < 1 second average

Conclusions

The test results do appear to show that offloading data-intensive tasks to the server, using PSOS, does indeed result in better performance. When offloading SQL queries (ExecuteSQL), the results are returned 20% to 50% more quickly. Offloading bulk updates (Replace Field Contents) results in massive performance bonuses!