1 of 1 people found this helpful
My understanding is that for more elaborate queries, FileMaker will download all the relevant data for the client to parse rather than executing the query on the server. I could be wrong. My experience has been that getting the best performance from ExecuteSQL usually requires some experimentation. In one example with an outer join, I got a 1000x performance improvement by splitting the join into separate queries, and doing the join with a custom function on the results instead.
I notice that your JOIN clauses don't specify match fields in the "right" tables. Are you sure that's the right way to get the result you want? Can you show us the rest of your query? That might give us more clues to what could be slowing you down.
The ExecuteSQL function in FileMaker is not "true" SQL. FMI built a wrapper that basically parses the SQL and then performs FileMaker'ish functions (like find, sort, etc).
FileMaker is very record centric, meaning that when you ask for one field in a table, all the fields come across the wire to the client. ExecuteSQL does not get you around this aspect of FileMaker.
I suspect SQL is slow because you are moving a lot of data across the wire to the client where it is then stored in the temp file. The next time you call the function it is able to read from the temp file and appears to be much faster.
This is assuming that you were talking about the ExecuteSQL function from within FileMaker.
If this is the case then are the 3rd party SQL plugins doing the same or are they more "real" SQL?
Is this why you can get more functionality out of the 3rd party versions?
Would true SQL actually be faster/better or even possible?.... or is FMI SQL just a context-less workaround
masquerding as SQL?
Missing table names has no remarkable effect on results of that query?, however I have add them.
Here is other, very similar query:
SELECT Issues.ID FROM Issues LEFT JOIN Participants ON Issues.ID=Participants."Record ID" LEFT JOIN RelationCalc ON Issues.ID=RelationCalc.IdB WHERE Issues."Global State"='Active' AND Participants.Name='TomasD' AND Participants.Role='Worker' AND RelationCalc.IdA=2636 AND RelationCalc.TypeA='Milestones' AND RelationCalc.TypeB='Issues'
Participants and Milestones can be connected to one Issue and I am finding all issues with specific milestone and participant. Participants table is shared across all database files (each Person, Company, Issue,.... can have participants).
The custom function is interesting, do you mean to perform select for each join separately and then combine it?
I didn't know that. In process monitor I see Filemaker downloading large chunks of data. I am using custom SQL plugin, but ExecuteSQL did the same thing.
Some facts about FQL, I have found:
We see slowdowns in the initial query even on a simple query - we've got 250k records in a customer table and want to do a portal filter based on several fields. Standard portal filtering is too slow but sql works great after the first request. We implemented an ExecuteSQL function in the startup of their solution so FM caches or does whatever it does. Having more RAM on the workstation (8GB vs 4GB) seems to improve first query so it's not just writing to disk. We tried PSOS as well but it seems to flush it's cache so queries were faster on the workstation.
"SELECT \"_pk_Customer_ID\" FROM \"Customers\" WHERE LOWER (\"First\") = 'micky' and LOWER (\"Last\") = 'mouse'"