This is a quick tip about how PSOS can vastly improve performance of the ExecuteSQL script step.  Recently we received feedback on one of our solutions that uses Execute SQL to build a virtual list used in a picker window. The feedback had to do with the slow performance of loading the picker window. The user complained of the window sometimes taking up to 30 minutes to load. Obviously this design was not ideal!

The Solution

The solution I came up with was quick and easy to implement. Since we already have a wrapper script called “ExecuteSQL” that performs the SQL call, it was easy to modify that script so that it either uses ‘Perform Script’ or ‘Perform Script on Server’.

The “ExecuteSQL” generic script simply takes a parameter of the SQL statement and returns the results (or an error). It’s simple, but very useful, to have the ExecuteSQL command isolated into it’s own script. This means I could easily modify my script to run the SQL portion on the server by passing it my SQL statement and specifying the checkbox to “wait for script to complete” in the PSOS options so that the server script runs synchronously with the client script. When the server is done running the script, it returns my results which I then pass to my virtual list.

With this simple change made, performance was improved from 30 minutes to less than 5 seconds. It’s amazing how much faster executing SQL commands can be on the server than on the client. There are many scenarios where using PSOS can significantly improve performance and help take some of the load off the client. This technique is quick and easy to implement with impressive results.

What to Watch Out For

It’s important to make note of the risks of using PSOS and to use “Set Error Capture” to properly check for errors when running PSOS. I’ve referenced some of the risks in this related blog post on appending PDFs.

First, of course, you must be using FileMaker Server and your file must be hosted. If you are trying to use PSOS on a local file you will get error code 3.

Be sure to verify all script steps you are using in a server side script are server compatible. You can easily do this by using the compatibility drop down to see a list of server compatible script steps

Occasionally we have seen instances where the FileMaker Scripting Engine (FMSE) will crash. When this happens, you will see ‘scripts in process’ in the Server Log. You will also get an error when using PSOS. You can restart the FMSE from the command line, or by restarting the server.

The last issue has to do with the maximum capacity of the host. There is a setting in the FMS Admin tool which allows you to specify the maximum number of simultaneous script sessions. Each time the PSOS script is run, it begins a new session. If you set “Wait For Completion” to OFF  in the PSOS script step, this leaves you open to creating too many simultaneous connections. If you need to wait to return a result or wish to capture an error turn it ON.

~Kate Waldhauser