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 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.
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. Get the demo file here.
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 ExecuteSQL()
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.
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.