ApplicationsBlogFileMaker

When FM Scripts Are Faster Than PHP for Custom Web Publishing

By October 2, 2009 2 Comments

Often it’s best not to use FileMaker scripts to perform complex operations within a Custom Web Publishing application, if only to keep all your code logic in one place, but there ARE times where using FileMaker scripts can provide a huge speed improvement over equivalent logic in PHP.  I recently worked on a project where we wanted to update roughly 500 records following a form submit.  The problem was it was taking over 30 seconds due to the massive number of commits. Every round trip to the server was costing us time, so we needed to drastically reduce the number of calls we made.  While we might have been able to use a portal (that’s another post, I guess), in this situation we decided to use a script to offload most of the work to FileMaker using a single server connection from PHP.

First, on the PHP side I made the information headed for those 500 records into a single long string with delimiters separating each row and field value. For this project I used ^|^ for a field delimiter and *|* for a row delimiter.  You can use whatever you want as delimiters but you should do your best to make it sufficiently obscure that a user won’t ever be entering data with one of your delimiters in it!

Then I sent that long string over to FileMaker, specifically to a FileMaker script that I wrote to be able to dismantle my long string and turn it into record updates.  The PHP call to do this did a Find Any, which returns one randomly-selected row, because it doesn’t matter what record context our script runs with and because that random find runs faster than a real find — and called the script with my long string as the parameter.

Inside of the FileMaker script we used

Set Variable [$array, Value:Substitute (Get (ScriptParameter) ) ; "*|*"; "¶")]

to break apart the string into return-separated values, each value representing a record we want to create.   Then later, in a loop, we used

Set Variable [$row; Value:Substitute (GetValue($array ; $counter) ; "^|^"; "¶")]

to break apart the field values within each row, so we could work through them and set them into the proper fields on the proper record.  When the script was done running, those 500 FileMaker records had been updated with the new field values sent across in the long, delimited string parameter.

Using this method we went from 2 PHP server requests for every record we wanted to update — one to find it, another to write changes to it — down to just one server request for all of them, no matter how many record updates we needed to do.  This method might not be faster for updating 2 rows, but for the hundreds of records that we were updating each time, we saw a speed improvement of almost 10x, bringing our form submit time down to around 3 seconds.   So if you find yourself with a web page submission that needs to update a lot of records and it’s running slow, consider shifting the burden of updating those records out of PHP and onto FileMaker scripts to speed things up.

Brian Engert

Brian Engert

Brian is a DevOps Engineer for Soliant in Chicago. Brian specializes in PHP development and system administration and has been doing so for over 5 years. In Brian’s free time he likes to work on open source software and play online multiplayer games.

2 Comments

  • Avatar Caspar Harmer says:

    Nice one man – I’ll be using this next time I want to update a lot of records.
    Casp

  • Avatar Pieter Claerhout says:

    The same applies to deleting more than one record with the FileMaker PHP API. If you use the PHP api, it will execute a request for each record you want to delete. Moving this to a script cuts it down to one single request to delete any number of records.

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant