Understanding and tuning FileMaker Server Performance: PART Three
By Nick Lightbody BA(Hons) FRSA
May 29th 2015
Deskspace Systems Limited, West Sussex, United Kingdom
This document has been prepared with the assistance of Wim Decorte of Soliant Consulting Richard Carlton of RCC Alan Stirling of AST and FileMaker Inc. and I thank those folk for their valuable and in some cases time consuming assistance.
So we start first with some solution design DON'TS followed by the DOES, this is because with a product like FileMaker the most important thing is to avoid doing things that FileMaker doesn’t like, things that for example ask FileMaker to undertake lots of unnecessary work and hence which adversely affect performance and thus the user experience.
● Don't use an unindexed calculated field as a foreign key in a relationship or as a sort key.
● Don't use summary fields in what may become a very long list of records, only use them in shorter data sets, use instead a more efficient and controllable method of obtaining the totals and subtotals that you require.
● Don't index fields unnecessarily.
● Don't use very large table occurrence groups in your relationship graph.
● Don't refer to field values in expressions or scripts other than to set a variable which is subsequently used instead of the original field.
● Don't add features just because "I can".
● Don't permit any normal user to access very large numbers of records.
● Don't break up scripts into many small scripts unnecessarily since a single long script runs faster, and is far easier to follow, than the same script broken into many short scripts called in sequence. However, the key word here is unnecessarily. You should, of course, always use sub scripts to ensure that you never do exactly the same thing in more than one place, but you shouldn't break scripts up just thinking that this will run faster since it will actually run slightly slower. Thanks to John Renfrew for asking me to clarify my recommmendation on this one.
User interface / experience
● Don't permit any normal user to sort very large numbers of records.
● Don't use layouts containing local styles.
● Don’t display any layouts in the user interface which are based on table occurences holding many records, especially on a mobile device over WAN
● Don't start building a solution without planning it first.
Then some DOES...
● Do try to keep things as simple as possible, consider what you've written and then try to remove whatever you can to reduce it to its essential elements. Your users will thank you for simplicity and speed not for may features they will never use.
● Do use an app like OmniGraffle to plan your solution or the part that you are working on and then construct it to this plan.
● Do use OmniGraffle, FMDiff3 and BaseElements to analyse and understand what a legacy solution is doing, this will likely enable you to undertake extensive simplification and improvement.
● Here are a couple of examples of how I use Omnigraffle when designing see fig.1 and fig.2.
● Do use global variables for all of the language layer in the user interface.
● Do always use a let statement to set the values to be used in local variables when writing an expression.
● Do always set the values to be used in local variables when writing a script.
● Do design and use a consistent table occurrence and field naming policy to enable you to use set field by name to write multipurpose scripts.
● Do remember that long scripts are easy to follow and run faster than many short scripts.
● Do always document expressions and scripts. extensively as you write them to help yourself and others understand what you are trying to do at the time, the ability to add comments very easily in the new FileMaker 14 Script Workspace makes this much easier.
● Do when writing a new expression or script start by writing a series of comments describing what you want to happen and use that as the structure around which you build the operative parts of the expression or script.
● Do find out about best practice in utilising perform script on server since it will make many operations such as creating and deleting records very much faster, particularly on a WAN/mobile deployment.
● Do always import or export data from a layout based on an isolated table occurrence.
● Do schedule intensive operations, perhaps updating or querying large numbers of records, to be run by the server during the part of the day when the system will likely have fewest users.
FileMaker Server will be performant when an efficient solution is correctly deployed. If the solution is badly designed or implemented and it does not perform well then this is your responsibility, it does not result from a problem with FileMaker Server.