Home / Blog / FileMaker / ExecuteSQL() – the Good, the Bad and the Ugly
05Mar 2018

ExecuteSQL() – the Good, the Bad and the Ugly

About the Author

Wim Decorte Wim Decorte

Wim is a Senior Technical Architect at Soliant. He is a FileMaker 7, 8, 9, 10, 11, 12, 13, 14, 15 and 16 Certified FileMaker Developer and the author of numerous Tech Briefs and articles on FileMaker Server. Wim is one of the very few multiple FileMaker Excellence Award winners and was most recently awarded the FileMaker Community Leader of the Year award at the 2015 FileMaker Developer Conference. He is also a frequent speaker at the FileMaker Developer Conference and at FileMaker Developer groups throughout the world. In addition to being a renowned expert on FileMaker Server, Wim also specializes in integrating FileMaker with other applications and systems. His pet project is the open source fmDotNet connector class that he created.

Comments (3)

Kevin Frank - March 13, 2018

Great article Wim… this is helpful and timely. Thanks!

Vincent - March 13, 2018

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 :

    Wim Decorte
    Wim Decorte - March 14, 2018

    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.

Leave a Reply