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.
• Query: SELECT City, COUNT(*) FROM Leads GROUP BY City
• Run on Client: 35 seconds
• Run on Server: 28 seconds