The Perform Script on Server (PSoS) script step is an extraordinarily powerful tool for speeding up processing in FileMaker, but it poses a challenge to go along with it’s power: Since the processes run on the server, the results are not always available to the client. Imagine a situation where the server is finding a set of data and doing some type of update on the found records. Now imagine the end user needing to recreate that found set on the client. The user will need a way to identify the records modified by the server, gather that info, and recreate the find on the client.
Imaginative developers have developed several ways to retrieve sets of records from the PSoS script step, but, until now, there hasn’t been an deep analysis of which way works fastest under varying conditions.
Enter Mislav Kos, of Soliant Computing. Kos spent a considerable amount of time testing different methods under different conditions and with very large data sets, and the results are worth studying:
As I alluded to already, the List of Find method is quite slow. The GTRR method is fast, with number IDs performing considerably faster than text. The Snapshot Find method performs the fastest when the found set is configured according to the ‘best case’, requiring just a single find request. But when the found set is set up according to the ‘worst case’, the performance is comparable to the List of Find method. (It’s a bit faster, because getting the list of internal record IDs from the snapshot is faster, but it’s still brutally slow.)
The answer is a bit unsatisfying, because for the Snapshot Find method, the data is shown for the ‘best case’ and the ‘worst case’, and not for the ‘typical case’. But the typical case would be difficult to reliably reconstruct in a test environment, so I had to resort to a best/worst-case type of analysis….
There are some other interesting points to consider from the post:
- When the script parameter for Perform Script on Server exceeds 1 million characters, Error 513 is generated
- Using UUID Text fields for the primary key results in a much smaller file than using standard Number type serial number fields
- Primary keys that are of the Text type process much slower than Number type fields
- There is a limit to the number of records that can be accessed using the Go To Related Record (GTRR) function
- One commenter thought of another way to collect the values and return them…I’ll bet others find more ways, too
Finally, and incredibly, KOS built a robust demo downloadable file that allows users to do their own testing under different conditions and with different parameters. Thanks, Mislav!