Ever since the ExecuteSQL() function was introduced in FileMaker Pro 12, there’s been much debate over its use and usefulness and its speed.
What Does FileMaker’s ExecuteSQL() Do?
First off, if you need to get up to speed on what ExecuteSQL() does and does not do, the best reference is this blog post by Kevin Frank and Beverly Voth. Don’t let the age of the blog post give you pause; all of it is still very relevant if you are not so familiar with all the ins and outs of the function (and even serves as a refresher if you are familiar with the function).
Restrictions to Keep in Mind
The big obvious restriction is that ExecuteSQL() does SELECTS only, in other words: you use it to collect data, you cannot use it to create or update existing records. If you need the ability to manipulate data, there are many FileMaker plugins that provide that capability.
Then there are subtle differences that can catch people out:
- The fact that SQL queries are case sensitive. Looking for “wim” will not return records where the value is “Wim”.
- SQL treats empty different than null. (Learn more here.)
- ExecuteSQL() supports a particular subset of the SQL syntax; it does not always support SQL syntax that you may be used to from other environments.
- There are reserved SQL keywords that can clash with your field and table names.
FileMaker ExecuteSQL() Performance
For now, I want to focus on performance. While participating in the online FileMaker forum, it struck me that ExecuteSQL() seems to be getting a bad reputation, and I do not think it deserves it. I certainly wouldn’t want to see people avoid it. So here is the good, the bad and the ugly:
- The Good: ExecuteSQL() is blazingly fast.
- The Bad: But not always.
- The Ugly: When it is slow it can be extremely slow.
Using ExecuteSQL() effectively all comes down to understanding the circumstances around what makes it fast and what makes it slow.
In our experience, there are two broad areas where it will slow down:
- Complex queries that include JOINS, GROUP BY or SQL functions
- Running queries (even very simple ones) against a table where the user has an open record in
That second behavior where ExecuteSQL() will slow down when the user has an open record in the target table is discussed and demoed in my 2014 Devcon presentation.
The fix can be as simple as making sure that all records are committed before running the ExecuteSQL() query. However, you may also need to carefully consider using the function in areas where you do not always full control over the state of the record — places such as calculation fields, hide conditions, conditional formats and tooltips.
You can often mitigate the first behavior with (semi) complex queries by not cramming too much into one request. Very often you can achieve faster results by breaking up a semi-complex query into different parts, sometimes even by mixing and matching ExecuteSQL() with other FileMaker features.
References for Success
This thread includes links to two success stories:
- “From 6 hours to 6 minutes with ExecuteSQL()”
- “Looping script that went down from 3h 15m to 1m 45sec“
It also provides pointers to possible performance optimizations:
- Using sub-selects with the IN clause (Greg Lane of Skeleton Key)
- Explicitly repeating the JOIN predicates in the WHERE clause (by FileKraft)
Here’s another good discussion about avoiding slow JOINs with sub-queries, by Taylor Sharpe.
One potential roadblock in using the IN clause to speed things up is that it does not allow dynamic parameters like elsewhere in the ExecuteSQL() calls. A good approach to that is available here.
You can also find an approach in that 2014 DevCon demo linked earlier in this blog post.
In Encouragement of Using FileMaker ExecuteSQL()
FileMaker ExecuteSQL() is an incredibly useful tool in our tool belt, and I would strongly encourage FileMaker developers to use it where it fits. It does some very unique things extremely well provided you stay within its performance envelope. It is not a very wide envelope, but do not let that discourage you or shunt it.
The Good is really good; the Bad and the Ugly can mostly be avoided.
There are many good ideas that have been logged to make ExecuteSQL() better in the FileMaker Product Ideas space. You can search there for “executesql”. I recommend finding the ideas you like and voting them up. It’s the best way to provide input into the direction of this useful feature.
Need help with FileMaker ExecuteSQL()?
If you have additional questions about ExecuteSQL() or need help enhancing a FileMaker solution, our team may be able to help. Contact us today to learn more about our work and see if we’re a good fit for your project.
Great article Wim… this is helpful and timely. Thanks!
Very good info. One is missing, the first query time tax. If you’re query involves a somewhat big table (not so big 20K record my suffice to experience it), you’ll notice that if you relaunch the same query a second time it will be much faster than the first time it was launch by a 10x factor. This is especially annoying as in 90% of the case, you only need to launch the query once. Here’s an idea that will put this to an end (as well as detailed explanations) and let us enjoy 10x faster first queries. Please vote for it :
https://community.filemaker.com/ideas/2281
Putting some perspective around it. The screenshot below is from my 2014 Devcon session where I talked at length on ExecuteSQL() performance. As you can see the second query is in fact 10x faster than the first. But it is kinda irrelevant. That’s on 1.5 million records. The first time it runs it is just over 1/10th of a second, that’s fast enough that I wouldn’t really care that the second run is 10x as fast.
The ‘first time penalty’ does get bigger depending on the complexity of the query, which is what I pointed out in the article: try and find the sweet spot and see if your requirements allow you to stay within it.
Hi Wim.
I’ve just taken over a 2012 development, birth of SQL queries, done by another developer. It turns out to be extremely slow, because in my opinion he had to test with less much of records. I’d like to avoid disturbing the architecture too much and refer to the articles you mention. However, the links are broken, could you recreate them?
Hi Vincent,
We’ve updated the links we could find but that’s unfortunately not all of them. It seems like some of the content was lost with the Claris Community update a while ago.
Best regards,
Wim
I’ve been an SQL developer for decades before undertaking a major Filemaker project (now based on Filemaker Cloud). The database is very calculation-intensive, and I relied heavily on ExecuteSQL to perform most of my lookups. After finding a Claris article regarding performance optimization, I was surprised to discover a key performance consideration that could spike network traffic and performance overhead for any Executesql (field, webviewer, custom function) calculation, or script step that is performed locally and not PSOS.
“evaluating the ExecuteSQL function on a client transfers all records in affected tables to the client.” Clearly for large or growing tables, this could quickly paralyze a database’s performance!
As a quick test and fix, I abstracted all of my client-side references to executesql to a PSOS step, and immediately saw a 5-10x speed improvement on my slowest scripts – and our database is still fairly small! I am still a huge fan of executesql, (the many steps required for a layout change/find/iterate/return seem very inelegant to me), but this is an important programming consideration I haven’t seen mentioned anywhere outside of the Claris documentation.
Claris article link: https://support.claris.com/s/answerview?anum=000035171&language=en_US#Limit%20the%20use%20of%20the%20ExecuteSQL%20function%20with%20large%20data%20sets
What you probably ran into is the behavior I first demoed at Devcon 2014 and which you can read up here (plus a demo file): https://www.soliantconsulting.com/blog/executesql-named-buckets/
When the user – in their session, other user’s sessions don’t affect this – has an open record in the target table, that forces FMS to send the whole table down to the client to perform the eSQL() call. The penalty can be severe if the table is long (many records) or wide (many fields, lots of data in fields).
Thank you very much all contributors ,
Am from Uganda and have taken over a project which is calculation intensive and there are many errors in this project reports.
I opted for the executesql function to help me rebuild better reports from the available data ; Please share with me
how you can update the portal using executesql.
Thanks!
That is a question that that probably needs a better medium than these comments to dive into. You have a few different options and the best one depends on your existing setup and what it would take to change it. ExecuteSQL() gives you results in text format, you can use those as the basis of a Virtual List-technique portal, or perhaps you just retrieve the IDs of the records you want to show in the portal and set those in a global text field in the parent table. Or perhaps a portal is not the best construct here.
Feel free to post your question and background info on community.claris.com and tag me (handle = wimdecorte).