- EXECUTESQL
What are the performance implications of using ExecuteSQ? When should you use this and how should you use it properly?
From all I've read, ExecuteSQL is nothing more than a wrapper on standard FileMaker functions, not a SQL engine per se. Also, only SELECT is supported.
In usage over the years, GROUP BY becomes incredibly slow to the point of non-usability with relatively small record sets. SELECT can also be very slow unless you're doing an EXACT search (where this = that).
You also cannot do an ExecuteSQL unless you reference an object in the Relationship Graph.
In MySQL, for example, you could do something like "select CURRENT_DATE". That capability is missing from ExecuteSQL.
For INSERT, UPDATE, and DELETE you either need a plug-in, JDBC, or something else (?).
I tend to call it FQL (FileMaker Query Language) to distinguish it from the real thing.
FQL seems to be useful as a means of having a script āwalk a tableā and pick up data from records that meet its test?
Bruce Robertson originally, for me, talked about walking the table when he was explaining virtual lists.
A typical use is where someone might have 12 relationships to enable monthly reporting then FQL can be used to create the same reports with zero relationships. Thus use can improve performance by just removing relationships.
dsBenchmark uses FQL in the Performance Dashboard under the statistics buttons that report on performance of different speeds.
As this is open source if you havenāt tried it yourself this may be helpful.
The atypical feature is that field names are not tokenised so a field name path used in a FQL expression will break the expression if it is changed. This can be avoided if you take the trouble to use the field name path internal ID in place of the literal name - or so I am told - I havenāt done this myself.
The other oddity is that if the expression is broken you donāt get much in the way of helpful error messages.
In dsBenchmark FQL worked well and I have not been aware of any slowness, so I used it in the 3WinsApp I build 18 months ago, where I needed to get data for each day, week, month and year and in this case it is lightning fast. But this is a personal App designed to run under FMGo only on someoneās iPhone, it is not designed to be shared.
So I havenāt observed the reported slownesses.
What would be really useful is if folk could describe the circumstances of their use cases of FQL and report the performance so that we can work out what are the favourable and unfavourable environmental factors.
Someone could also summarise the current accepted wisdom / advice on these matters which we can maybe test.
It is I suggest a feature of human nature that people tend to be more active is reporting the failure of something for which they are not responsible, other than deciding to use it, then they are in reporting the success that they hoped for?
Cheers, Nick
1 Like
SQL - Structured Query Language is a misnomer also. SQL is not a "language" and it's not really that "structured" either, yet everybody calls it that. LOL.
A very interesting book I read a long while back describes how the term "SQL" came to be. In fact, according to that discussion, "SQL" doesn't stand for anything and was derived from another word.
To your request above, try to do a GROUP BY in FileMaker with just 50,000 records. On a current iMac with 256 GB RAM, such a ExecuteSQL hangs FileMaker. In MySQL or SQL Server, this query completes in about 0.1 sec.
I know of no workarounds.
Playing ExecuteSQL on the safe side, I am using it for retrieving field content without changing context. It helps making parameter lists for script and function calls narrower.
Yes it think that usage - without changing context - to walk another table and retrieve data and bring it back to the current context is a good example of something that FQL enabled but which is difficult / canāt be done any other way in vanilla FileMaker?
@AndyHibbs posted this on Community a few years back. Don't know if it is still applicable. Adding it so that past work isn't lost because it is elsewhere...
Hi Cecile
Weāre under pressure here at the moment, so it is frustrating to not be able to join in with @nicklightbodyās posts over the last few days.
Since creating the post youāve linked to, weāve moved completely away from any form of join in ExecuteSQL due performance problems.
We have 3 rules for using ExecuteSQL
- The current record must always be in a closed (committed) state before running any SQL.
-
- Due to our scripted approach to validation, we have a āCommit Recordā script that sets a global variable to bypass any OnRecordCommit triggered validation script to allow the calculation process to run uninterrupted.
-
No JOINs to be used. If we need data from multiple tables in addition to those possible by using the function arguments, we use nested or multiple SQL SELECT statements and the IN operator, which has been proved to be vastly quicker than using JOINs.
-
Never use ExecuteSQL in a calculation field (no surprise there as we donāt tend to use calculation fields, but it is a worse recipe for slow systems than standard unstored calculations).
Abiding by the above rules, we find ExecuteSQL very, very useful.
Sorry, in haste and wish I had more time.
Kind regards
Andy
7 Likes
Thanks @AndyHibbs - your expert input very much appreciated.
Cheers, Nick
1 Like
I got some significant acceleration with joins if the JOIN predicate is replicated redundantly in the WHERE clause. Express the same match in both places. (Didnāt verified this in FM19 yet).
2 Likes
To clarify, it's not the current record that needs to be closed, it is any records in the target table that needs to not be open. When FileMaker attempts to return the results from the server, Server doesn't know what records you have open, so it sends ALL of them, to allow the FileMaker client to figure out what values to return.
Agree your other statements. Careful use, if at all, of JOINS and GROUP BY. They are performance killers. And only use ExecuteSQL when you can control when it gets called... as your description stated, NEVER as a calculation field.
For me, I primarily use it for retrieving user preferences, or lists where the match is very simple ( example, the list of UUIDs from the Invoice Line Item tables, for a specific invoice ), or for things like that where I can't use Execute FileMaker Data API script step.
4 Likes