100x Faster – Flight Testing FileMaker Perform Script on Server – Episode I

Read the follow up to this blog post at What Are Your Imports Waiting For? FileMaker Perform Script on Server – Part II

Introduction

Since the release of FileMaker 13 we have been exploring a new feature called “Perform Script on Server” (PSoS) and trust me there are many cool things you can do with it. I’m going to describe how PSoS works, how it’s best implemented, and how our tests show PSoS can drastically improve database performance, more than 100x in some situations. However, there are also a number of considerations before you run wild and convert scripts to execute via PSoS. So, I’ll cover these as well.

Before I get started I’d like to give a big Thank You to the FileMaker engineering team for this new capability. I have been rethinking solutions in light of this new capability and the results have been astounding. In this first of two blog posts I’ll talk about these results, as well as what you need to watch out for when using PSoS and how to work with these limitations. In a follow up blog post I’ll go into more detail and share some other exciting ways in which you can use PSoS to improve the FileMaker user experience altogether.

Getting Started

First, you’ll need FileMaker 13 for both server and clients. In FileMaker 13 we now have a new script step called “Perform Script on Server” (or “PSoS” for short). You can specify a script to run using this method, and there is an additional option called “Wait for completion”.

If this option is checked then you’ll have to wait till the server completes the execution of your script and all of its sub scripts before the parent script continues. However, if you uncheck this option then the script is told to run on server and you are free to get back to doing whatever you want to do while the script does the work on the server.

I’ll talk more about this later and give some great examples of how you could use it.

If you have users that connect to your database over a WAN and across the internet then performance is always going to be foremost in your mind. And this new capability executes scripts so much more quickly on FileMaker Server.

You may already be thinking: Why not always use PSoS instead of “Perform Script”?

Well, not so fast! First off, not all the FileMaker steps are PSoS compatible. Take a look:

You can use the Show Compatibility pop-up menu at the bottom left of the Script editor window to see which steps are compatible. We can see that the Perform Script on Server step itself is not Server compatible. Although that sounds confusing when you first hear it, all it really means is:

The Perform Script on Server can only be fired off by a client (FileMaker Pro/Advanced, Go or Web Direct).

Once you have asked that script to run on server, any other sub-script can only be Perform Script and not Perform Script on Server. Well, that puts the kibosh on simply converting every single Perform Script to PSoS. So what good is PSoS, and how exactly does it work, you ask? PSoS is still incredibly useful, but you have to be careful when choosing to use it.

You should also take care in how many times you might end up calling scripts to run on server. Otherwise you can easily overload your server/CPU and trash performance for the rest of your users. More on this later.

Calling PSoS

There are only 3 places where you can directly call “Perform Script on Server” and you should only be using 1 of them…that is from inside a script using the Perform Script on Server script step.

You can directly call a script to run on server from any layout object as a button, custom menu item, or script, but that might not be a good idea. The reason is that at some point if you open the file locally and execute those scripts you will get an error:

Error # 3: Command is unavailable (for example, wrong operating system, wrong mode, etc.)

So, a best practice might be to always check to see if the current host application is FileMaker Server 13. It may be a little more work to have your button or custom menu item first call a parent script, but it’s good to have that safety net. Then the sole function of the parent script is to pass the same parameters over to the child script that will be the one that will actually do the work. But before you call the child script you’ll want to check if you are running on FM Server 13.

To keep your code clean, the child script could be the same exact script, except that one way it is called as a Perform Script step and the other way it is called as a Perform Script on Server step. Then inside your script you’ll need to carefully consider whether you need to fill globals, give it the correct context, etc.

Here is a diagram of what that architecture might look like.

What we like to do is write a custom function that will check the host application version and we call that function IsHosted – this will only return a value if it is hosted and additionally make sure we are in FileMaker 13:

Let ( [
   host = Get ( HostApplicationVersion ) ;

   hostVersion = GetAsNumber ( host )
   ] ;
   Position ( host ; "Server" ; 1 ; 1 ) and hostVersion ? 13 and hostVersion < 14

)

With buttons, custom menu items, or script triggers, we don’t call the PSoS script step directly. Instead, we call a parent script with “Perform Script.” Then inside that script, we add some logic to check if it is hosted.

If [ IsHosted ]
   Perform Script on Server ( Script )
else

   Perform Script ( Script )
End If

Using this technique you can essentially get the ability to trigger Perform Script on Server safely and effectively from anywhere you can call a script. That even includes Script Triggers!

Other Considerations

When working with Perform Script on Server, I like to remind myself in the same way you would if you might remember important things for a trip.

• Passport
• Tickets
• Credit Card

Or, in in the case of Perform Script on Server there are 4 things to review or consider:

• Security
• Context
• Globals
• File Options ( all the Window script triggers )

Its important to understand that if you are logged in with restricted access and you call PSoS, your account privileges are maintained, so all the same restrictions apply to the script. Depending on the task to be completed you might even want to have the script run with full access. If your security relies on any globals then you need to find a way that those globals will have values in them when your script is performed.

This is what FileMaker is all about. Let’s say you want to delete a record and yet you want that action to happen on Server. You would need to be on the correct layout and the correct record. Simply handing off the operation to happen on server isn’t going to be enough. So, you’ll want to make sure you are on the correct layout with the correct record found before you delete it.

The same goes for found sets. This is easy if the user performs a find to locate the records on which they want to perform some action. This is a little bit harder if it is a found set that the user aggregated. With a little creativity you can also transfer found sets over to your Server-side script… maybe even by using the new “List of Summary” function.

If values have already been inserted into global fields before your file was hosted then you should be fine. But if you insert values into global fields when your file opens as part of the OnFirstWindowOpen script, and you reference any of those globals in your script that you asked to run on the server, they won’t be there unless you load them just before you run your script.

For example I would load the access a user has into a global field in some resource table and then my security was checking that field if the user had the rights to see it. I spent a while wondering why it wasn’t working till I took a break and it just dawned on me that security was checking those global fields and so it was denying access because there was no access to be had.

What I mean here really are 4 script triggers: OnFirstWindowOpen, OnWindowOpen, OnWindowClose, and OnLastWindowClose. Each one of these will get triggered, so you want to add some checking into those scripts to see if they are being called on Server, and if so exit them. If you have scripts that, for example, need to load some global values which might end up being referenced by your server-side script then you’ll want to make sure that those values are loaded.

My advice would be to only load them in each script as needed, so if you end up relying on PSoS you don’t encumber the PSoS script with additional overhead.

Finally, in the FileMaker Server Admin Console under the Database Server section there is a setting that allows you to control the number of maximum simultaneous script sessions that you can have running at the same time.

My advice here is to know your solution(s), and tailor this setting accordingly. If you know that most of the scripts being performed on Server will execute in less than a second then you can set the number pretty high. But setting that number too high might allow more script sessions than the CPU can handle and then the user experience will be compromised for everyone.

Experimenting will help you to find the right number for your solution. Know your solution(s) and what you are going to be asking the server to do. BTW – the maximum number you can set that value to is 500. If you do happen to hit up against that limit the error you’ll get is error number 812: “The host’s capacity was exceeded, try again later.”

It seems like there are lots of things to consider with PSoS, so you ask, “is this really worth all the effort and changes required?” ABSOLUTELY!

Rethinking and Refactoring

I am right in the middle of refactoring Perform Script on Server in a couple of solutions. Both of these solutions have users who are thousands of miles away from FileMaker Server. Thus far we have given a few users access to test out this new approach and they are amazed at how much of an improvement it is. Also, I took this opportunity to rethink the solution and find ways to improve performance in other areas, all of which is contributing to a better solution.

But before I decided to tackle these 2 solutions, I put together some tests of PSoS. I asked users in different geographic locations to connect to a FileMaker Server over the WAN and across the internet. I put together a number of very simple tests to demonstrate performance gains using PSoS:

The results shown below are for FileMaker Pro, from a WiFi connection to Internet at our office in Oakland, connecting to the FileMaker Server in Palo Alto, 29.1 miles away.

For this test, I used a Mac mini co-located at macminicloud.com. It has incredible bandwidth there, sitting on a fiber optic backbone just across the street from a Google datacenter, so access speed is amazing. I highly recommend co-locating a server with macminicloud.

Total time to run all the tests was 729 seconds. This was a typical FileMaker database, using only Perform Script for scripts. Compare this with the Perform Script on Server results, testing both wait and don’t wait options which both clock in at about 7 seconds.

If you move the FileMaker client to the East coast, some 2,492 miles from the server, we are looking at 1,398 seconds or 23 minutes using traditional Perform Script … and PSoS still clocks in at 7 seconds for both options.

Now it’s nearly 200x faster!

Finally, when you connect from as far away as Switzerland ( almost 6,000 miles )…. Those poor souls have to wait 46 minutes as opposed to (once again!) 7 seconds for Perform Script on Server.

Almost 400x faster!

We have more tests that we are working on and will publish more details in a follow up blog post. If you have suggestions for additional tests, please send them our way.

Final Considerations

Improving performance is definitely a motivating factor for many of us that have to deal with user connections from different geographic locations. As much as I am excited about Perform Script on Server, I am also being very tempered and cautious when it comes to implementing this everywhere.

Having a good understanding of the solution you are implementing this for is a must. Understanding how long tasks will take as they are performed on the Server, and how many users might trigger those tasks to be executed at the same time is important.

Let’s say you end up converting a set of tasks that previously took a very long time to complete to the Server, and many users take advantage of this, doing work all at the same time. In that case, you may end up making matters worse for everyone if the Server becomes totally busy with PSoS and can’t do much of anything. So, be cautious in how you implement this.

Thank you for reading this and a big Thank You to my testers around the world. Stay tuned for a follow up post with more insights into what else is possible via Perform Script on Server.

– Vince

Vince Menanno is director of FileMaker development at beezwax.