Optimising Filemaker ESS SQL Queries

Anyone who has worked with Filemaker at length has at some point run into performance issues with queries and batch operations taking longer than we would like them to.
One of the challenges for anyone with experience working directly with SQL queries or database object models (such as using the Django web framework) is that it’s actually surprisingly difficult to understand exactly why certain queries are slow when working directly with Filemaker.
However, as soon as you start adding external data sources to your solutions, things get even more complicated, and at times the performance impact can be significant. There are two main symptoms you should watch out for when working with external data sources (in this example we’ll be looking specifically at integrating a MySQL database into a Filemaker solution):

Operations in Filemaker which you’d expect to be fast begin to take an extremely long time.
Database query logs on the MySQL side start getting hammered by thousands upon thousands of queries.

However, the challenge is that when developing solutions at an early stage, point 1 above may not manifest itself immediately, because there just isn’t enough data in the database to cause it to slow down.
The best way of ensuring you’re future-proofing your solution is to closely monitor the database query logs. These can usually be easily enabled, whether you’re running your own MySQL server, or using a cloud solution such as Amazon Web Services (AWS). By doing this you’ll also quickly learn what types of queries and operations are likely to be problematic.
Before you start analysing your queries, it’s incredibly helpful to gain an understanding of what’s happening behind the scenes. The article referenced below [1] provides an incredibly useful overview of how Filemaker interacts with external data sources, and will save you hundreds of hours of experimentation and struggling.

Understand Filemaker’s SQL Cached Data
If you’ve worked with the refresh window script step before, you may have noticed the options to ‘clear SQL cached results’ as an option and wondered to yourself what it actually does. The answer is relatively complex. Filemaker creates a local cache of external data which it attempts to keep in sync. You may have noticed that if you make a change in the SQL data it doesn’t immediately show in Filemaker unless you clear the cache – this is why.
Filemaker first requests ID’s, then the actual records
If you read the aforementioned article, you’ll notice that Filemaker first makes a request to the external data source for the respective ID’s and then performs individual queries using those ID’s to retrieve the records themselves. This is important to keep in mind, particularly as you go about monitoring the SQL logs.

Let’s look at a few examples of what we can expect. If we create a layout for a single table (call this table B) and include all the fields in this table, and run a search on the unique ID (in this case searching for ID 1881), the query will look as follows:
[code language=”sql”]SELECT id,[…] FROM database.table_b WHERE id IN (1881)[/code]
This is a nice clean query, as we’d expect. Now let’s add in a parent (call this table A) which is related to table B by ID. We’ll include all the fields for Table A in the above layout for Table B and we will again run the same search in Filemaker. The resulting query will look like the following (if you’re trying this for yourself, don’t forget to clear your cache every time):
[code language=”sql”]
SELECT id, […] FROM database.table_b WHERE id IN (1881)
SELECT id FROM database.table_a WHERE (id IN (38))
SELECT id, […] FROM sdcmysql.campaigns_campaign WHERE id IN (38)
[/code]
This is also a surprisingly clean query. We’re finding the relevant record from Table B, which contains the foreign key (FK) to Table A, and then searching for that ID in Table A.
Now suppose we wanted to find all records in Table B which were related to a record in Table A with a field (called ‘Name’) containing the string ‘test’. If we were writing raw SQL, this would be a simple join. However, in Filemaker, if you run the find in the same layout, this is what you’ll see:
[code language=”sql”]
27063 Query SELECT id FROM database.table_a WHERE name LIKE ‘%test%’
27063 Query SELECT id,[…] FROM database.table_a WHERE id IN (492,46,49,55,70,72,74,94,95,99,114,119,122,123,131,132,133,140,141,143,144,145,149,150,151,152,153,155,167,168,190,228,229,230,231,232,234,235,236,237,238,239,240,241,242,243,244,245,271,273,277,337,345,349,350,378,403,406,407,408,409,410,411,412,414,415,424,425,428,429,442,450,465,467,472,473,474,475,476,479,480,481,482,483,484,485,486,487,490,491)
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (492))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (46))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (49))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (55))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (70))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (72))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (74))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (94))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (95))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (99))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (114))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (119))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (122))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (123))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (131))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (132))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (133))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (140))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (141))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (143))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (144))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (145))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (149))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (150))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (151))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (152))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (153))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (155))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (167))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (168))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (190))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (228))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (229))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (230))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (231))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (232))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (234))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (235))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (236))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (237))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (238))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (239))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (240))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (241))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (242))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (243))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (244))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (245))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (271))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (273))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (277))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (337))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (345))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (349))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (350))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (378))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (403))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (406))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (407))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (408))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (409))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (410))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (411))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (412))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (414))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (415))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (424))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (425))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (428))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (429))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (442))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (450))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (465))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (467))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (472))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (473))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (474))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (475))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (476))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (479))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (480))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (481))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (482))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (483))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (485))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (486))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (487))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (490))
27063 Query SELECT id FROM database.table_b WHERE (campaign_id IN (491))
[/code]
Let’s examine what happened. Even though we were in a layout for Table B Filemaker automatically ran the first query against Table A to retrieve all the ID’s of matching records. It then, however, runs an independent query against Table B for *each* ID returned in the first query. It’s easy to see that if you’re querying a database with millions of records, this becomes an incredibly expensive process resource wise. Furthermore, if you were querying on multiple tables which are all joined together, this is multiplied many times.
Even though the sample table we’re running these against (in my case) only have a few thousand records, running this query already froze Filemaker for about 5 seconds. It’s worth noting that this is a very simple relationship between 2 tables A and B. If we had further tables between these, the problem would be exacerbated.
So how do we get around this?
After attempting different ways of achieving this, from some clever use of the ‘Go to related records’ script step, through to using portals to retrieve related records, we quickly established that nothing really solved this performance bottleneck.
The solution in our case was to delegate the complexity of running the joins to MySQL by exploiting it’s views functionality. By creating designated views that already returned joined data sets, we can then simply run queries directly against one table. This ensures that queries such as the example above are returned almost instantly, with minimal performance implications on both the server side and Filemaker.
Limitations and other considerations
Here are a few additional considerations and limitations to keep in mind:

Unlike Filemaker, there are no indexes automatically set in MySQL (except for the ID field by default). This means that as your database size increases, it’s important to manually created indexes in MySQL for each field you’re likely to search on.
If you need to run a similar join as above but where one of the tables you’re searching on is a Filemaker native table, then you have a problem on your hands. So far I haven’t been able to find a simple solution to this. What we recommend is filtering down your foundset as much as possible before performing any query or join on the Filemaker table. This tends to make it workable, though it still leaves a lot to be desired.

By using the approach outlined above, we’ve managed to reduce complex queries (particularly those running server-side in scheduled scripts) from taking over 60 seconds, to almost instant, involving joins across many tables.
If you have any specific questions about how to optimise queries involving external data sources in Filemaker, just drop me a line or add a comment below! Good luck!
References:
[1] http://www.filemaker–solutions.be/IMG/pdf/techbrief_ess_en.pdf