10 Replies Latest reply on Dec 6, 2013 12:47 PM by jbrown

    Using "Perform Script on Server" to Improve Performance

    TimDietrich

      I've been experimenting with FileMaker 13's new "Perform Script on Server" (PSOS) script step to see how well it can be used to offload data-intensive tasks to the server. For example, to offload a SQL query (via the ExecuteSQL function) or a bulk update (using Replace Field Contents). The results that I'm getting are amazing.

       

      When offloading SQL queries (with ExecuteSQL), the results are returned on average 20% to 50% more quickly.

       

      Offloading bulk updates (with Replace Field Contents) is so much faster that its hard to believe. In one case, an update of a field across 33,000 records took 3 minutes and 30 seconds in FileMaker Pro, but took only 14 seconds when the update was pushed to FileMaker Server. And that same bulk update took less than a second with PSOS's "wait for completion" option unchecked.

       

      If you're interested in the details, here they are: http://www.timdietrich.me/blog/filemaker-13-perform-script-on-server-insanity/

       

      -- Tim

        • 1. Re: Using "Perform Script on Server" to Improve Performance
          mark_scott

          Thanks, Tim.  This is a real "sleeper" feature — the kind that doesn't make for impressive advertising copy, splashy product web-page pictures, or top the inevitable "new feature" bullet lists, but quickly changes the way we do things.  And thanks for your testing, which is a great service.

           

          I notice that Sort Records is shown as compatible with Server in the "Edit Script" dialog.  I wonder if you know whether it's possible to do a Find and Sort on server, and then have server send the sorted records to client.  (I'm not set up to test at this point.)  It would still take the same amount of time for client to receive the entire (now sorted) found set, of course, as it would for an unsorted found set.  But, it would arguably be less objectionable to the user to experience pauses while trying to scroll too quickly through a found set in transit (already sorted), than stare at a beach ball when trying to do a first sort (on client) on a found set that is still in transit.

           

          Mark

          • 3. Re: Using "Perform Script on Server" to Improve Performance

            Mark, one of the first things I tested was the possibility that the server could perform the find and sort for a client and return the results. Does not seem to be possible. It appears to act as though the server had FMP running as another user or "robot," but I hope I'm wrong and there are some things that cam be passed to the requestor.

            • 4. Re: Using "Perform Script on Server" to Improve Performance
              wimdecorte

              ScottKoontz wrote:

               

              It appears to act as though the server had FMP running as another user or "robot," but I hope I'm wrong and there are some things that cam be passed to the requestor.

               

              You are not wrong, that is exactly what it does: it starts a brand new session on the server that acts like a new user logging in.  In that sense it will also run any on-open scripts etc.

              The PSoS has no link to your session,  your found set anything like that.

               

              Also, a small word of caution on the performance:  the server specs clearly play a role here.  A low-end server that is now close to being resources starved may be pushed too hard by offloading a bunch of client activity to it through PSoS.

               

              Furthermore: there is config setting on FMS that dicatates how many concurrent PSoS sessions are allowed.  The default is 25.  Don't be caught out by this.

              • 5. Re: Using "Perform Script on Server" to Improve Performance
                TimDietrich

                Wim --

                 

                Thanks for bringing this up.

                 

                I agree, you certainly do need to be careful with PSoS - especially when using it to move data intensive tasks to the server.

                 

                For example, suppose that you've found a large found set of records and you want to update them (using "Replace Field Contents") using PSoS. It's tempting to just call a script with PSoS, where the script simply runs the Replace Field Contents step. But doing so could be disastrous. It's very likely that you'd end up updating all records in the table - not the found set that you were looking at in Pro when the PSoS job was run.

                 

                In other words, the thing to remember about PSoS is that, as you said, doing so initiates an entirely new session. Unlike opening a new window, the PSoS does not inherit things like the layout that you were on, the found set you were working with, the sort order, any variables or global fields that had been set, etc. I think the only thing the PSoS session inherits is the account name and privilege set of the user that kicked off the PSoS task. (Another thing to keep in mind is that the new session created by PSoS acts very much like opening a file via Pro does. So the file's script triggers - OnFirstWindowOpen, OnLastWindowClose, etc - will fire.)

                 

                Regardless, there are some very interesting possibilites that PSoS presents us with. I think we're only beginning to scratch the surface of what we can do with it to improve performance of certain tasks.

                 

                -- Tim

                • 6. Re: Using "Perform Script on Server" to Improve Performance
                  TimDietrich

                  Mark --

                   

                  I agree - "Perform Script on Server" is one of several "sleeper" features in FileMaker 13.

                   

                  Others include:

                   

                  • the "Get ( ModifiedFields )" function
                  • the "Base64Decode" and "Base64Encode" functions
                  • the updated "Insert from URL" script step using HTTP Post capabilites
                  • and the new "List of" summary field type

                   

                  I think that we'll see those used in very interesting ways in the coming weeks.

                   

                  -- Tim

                  • 7. Re: Using "Perform Script on Server" to Improve Performance
                    mark_scott

                    Thanks, Tim, Scott, and Wim, for the additional information about how PSoS works, its potential, and its limitations!  I see a white paper coming, from someone who understands this stuff far better than me. 

                     

                    Tim - I also agree with your list of other "sleeper" features.  Re "List Of," I've done a bit of testing, comparing its speed vs. Hyperlist (a brilliant technique on its own, of course!), and found "List Of," coupled with GetSummary ( ), to be 3–6 times faster on a test set of 200K records.  I described my fairly rudimentary tests in a bit more detail over in this thread on FM Forums.

                     

                    Mark

                    • 8. Re: Using "Perform Script on Server" to Improve Performance
                      TimDietrich

                      Mark --

                       

                      You're welcome!

                       

                      Also, a quick follow-up to what I posted last night:

                       

                      The improved performance that I was seeing when offloading SQL queries and bulk updates to the server using PSoS also applies to bulk inserts and deletes.

                       

                      In one of my tests this morning, a bulk insert of 10,000 records via FileMaker Pro took 2 minutes and 28 seconds. Using PSoS (and waiting for the script to complete), the same task took only 13 seconds.

                       

                      The results for a bulk delete (also of 10,000 records) were similar. It took around 2:30 in Pro, but less that 13 seconds via PSoS.

                       

                      -- Tim

                      • 9. Re: Using "Perform Script on Server" to Improve Performance
                        LSNOVER

                        You CAN pass a parameter to a server function.

                         

                        And we now have a Summary field type that will allow you to get all the unique field values for a field in the found set.  This can be used to easy get the Record IDs and pass them to the Server script to initialize the same context.   You could also store them in a field in a record that once committed would be accessible to both the server and the client.

                         

                        It is a FABULOUS feature. 

                        • 10. Re: Using "Perform Script on Server" to Improve Performance
                          jbrown

                          I love this function. It allows principals to not rely on me to get the server to perform a process-heavy script. I'm going to explore this a lot more.