Optimizing FileMaker Performance for Working from Home
When most of the world is forced to work from home, many things you took for granted can disappear over night. Speed of the internet is one of them, performance of your business critical apps follows very closely. The good news is that most of the custom FileMaker apps out there do not use the full power of the platform and they can be made much faster. So let me share a few tips on how you can optimize your FileMaker apps for working from home over the slower-than-usually internet connection.
Here is a special exclusive excerpt from my FileMaker Konferenz 2018 session, specifically focused on optimizing for slow network.
In the video I am talking about the tools you can leverage for this specific optimization task, and one example from my team’s recent practice, optimizing one of our company’s apps.
When you want to minimize the impact of slow network on a particular operation, then you have to find out wonder how it uses the network and why.
To find out how, two tools actually really stand out by their ease of use. Both are designed to run on macOS.
The first one is NetworkMonitor, a simple FileMaker solution created by Jeff Benjamin in 2018 and published in the FileMaker Community (now Claris Community) under the name of FileMaker Inc. (now Claris International Inc.).
This file serves as a simple user interface to start and stop capturing all communication on the port 5003 used by FileMaker Pro to communicate with FileMaker Server. So if you start the monitor, then perform your test operation, and then stop the monitor, you get an accurate information about how much data got transferred between the client and the server.
For some reason, unknown at the time of writing this article, the file disappeared from the Claris Community and only the article explaining its use remained. It can, however, be found elsewhere, if you search for its name.
The second tool is Network Link Conditioner, an app from Apple, available as part of the Additional Tools for Xgode.
There are basically three parameters of a TCP/IP network connection affecting your app’s performance the most - bandwidth, latency and packet loss. Network Link Conditioner lets you modify these three parameters, thus simulating the network conditions you are trying to optimize your app for.
The third tool I found very useful, especially in combination with the Network Link Conditioner, is built into FileMaker Server since version 15, released in May 2016, and it’s called Top Call Statistics. It is disabled by default, but when you enable it, it will log the top 25 most expensive calls currently being processed by FileMaker Server at the interval you set. The default interval is 30 seconds but it can be set to as low as 1 second.
To really understand in detail how FileMaker Pro uses the network connection to FileMaker Server for a particular action, I found a neat trick combining the above mentioned tools. The Top Call Stats collect only the top 25 most expensive calls at every interval. But if you set the interval to be 1 second, and slow the network down enough by Network Link Conditioner to make FileMaker Pro unable to send more than 25 requests per second to the server, you will get every single request logged in the Top Call Stats log.
Not a long time ago I needed to optimize my company’s own app because one of its features was performing really horribly when working from home. One routine task that was usually taking me just a few minutes in the office, often took more than an hour to complete when I was working from home.
So our team used FM Bench, Network Link Conditioner and Top Call Stats to identify the bottleneck of that particular feature. They discovered there was a relationship with sorting enabled, and this sort operation was being requested from the serve several times for a single update.
The impact was not even noticeable on the local network but when working on slow network with lower bandwidth and significantly higher latency, every unnecessary request to the server counted.
Our developers checked the app in detail and discovered that the sorting was actually only needed for presenting the data in the portal. Just removing the sort from the relationship definition and sorting only the portal helped us to get rid of almost half of all the client-server exchanges.
Further optimizations included removing some conditional formatting, combining multiple data changes into a single commit, and replacing some unstored calculations with stored fields to avoid the same records being re-fetched from the server too often.
In the end, we managed to cut one elementary operation originally taking over 12 seconds, when working from home, down to about 5 seconds, very close to the 3 and half seconds time it was taking on the local network. The same operation now takes less than a second when working in the office.
To be able to optimize your app you need to understand how it uses the network and why. For this it helps to understand what role each of the three network condition parameters plays in the game.
Bandwidth matters the more the higher amount of data you need to transfer. So when bandwidth is your primary constraint, you will look for the Network Bytes In and Network Bytes Out values in the Top Call Stats. You can prevent too much data to be transferred by properly using named styles in your layouts, minimizing number of fields in a single table, and minimizing the number of records in the found set you are working with.
Latency has the bigger impact the higher number of (even simple) requests your FileMaker Pro client sends to the FileMaker Server for processing. Typical local gigabit wired network latency is under 1 ms. Latency of a VPN connection from home to office usually ranges from 10 ms to 100 ms. That means certain things can get up to 100 times slower when working from home. Every commit, every find request, every refresh, every context switch, every relationship evaluation generated at least one unique request sent to the sever. Even working with global fields can trigger client-server communication. Limit the number of requests, and you’ll get your app less sensitive to the connection latency.
Packet loss indicates instability of the connection. If too many packets are lost on the way from FileMake Pro to FileMaker Server or back, you get disconnected and have to re-connect. Then FileMaker Pro has to re-connect, which takes the longer the larger your solution is and the more table occurrences you have connected together in your relationship graph. If your network connection is so unstable that this happens, typically when using mobile network from a moving vehicle, then you should probably consider using a local copy of your app on the device, and synchronize data with the server only when the connection is stable.
FileMaker apps can be significantly affected by working from home with a slower network connection to the office. The fact that slower network can slow your app down 100 times, however, also means that you can speed it up 100 times by leveraging the knowledge of how and why your app uses the network. By using the right tools you can quickly spot what unnecessary network communication is happening, identify the code that’s triggering it, and optimize your app dramatically. Don’t hesitate to try it out, and don’t be afraid to contact 24U if you need help. We have gone through this many times and can do it for you as well.