Execute SQL (script step)
Post your query here (calculation and/or script)
Sent from my miPhone
Oh! Ok: ExecuteSQL (function)
select o.article_nr, o.qty
from ordersline o
inner join customers c
on o.customerid = c.customerid
I'm running it and it's taking a lot!
...Also please specify the data source for the ExecuteSQL Statement - especially if it's an external data source - also useful to know the total number of records in the tables being referenced - not just the number of records being found....
The ds is external and is based on a remote connection on a db put on a server.
on orderlines there are almost 460k records
and on customers 6000.
In my (humble) experience, querying an external data source of that size can indeed be quite slow (depending on a number of factors, both within the filemaker environment and the external data source environment).
Could you post the ExecuteSQL statement you're using?
Is the remote data source SQL / MySQL? If so, do you need to query against all 640K records? Could ask the DB admin to create a 'view' of the subset of records you need to query against, and then use that as a source?
Also, just to clarify, are you using the ExecuteSQL calculation, or the Execute SQL script step (note the space)...?
3 of 3 people found this helpful
There is no context with the ExecuteSQL (or eSQL as I call it!) function. However, I'm assuming you are performing the evaluation based on the current record (even if every record!)
So, rather than JOIN, I would do this (in Customers):
ExecuteSQL( " SELECT article_nr, qty
WHERE customerid = ? "
; "" ; ""
; Customer::customerID )
That way you are passing the current record customerID and not JOINing in the query.
This is the SAME as the relationship (on the graph)
Customer::customerID = ordersline::customerID
and showing a portal of the relationship with the two fields: article_nr, qty. By default the relationship (JOIN) is an 'outer join', meaning that blank rows can be in the portal if there are no related ordersline.
And since you are not returning any customerid, you don't really need an INNER JOIN in the query.
May I ask why you are using the eSQL query for this data instead of the portal relationship? are you "gathering the data" for a report or export in a way that the article_nr and qty can be used (as a block of TEXT result, remember from eSQL)?
I would never have this as a calculation as it needs to recalc every time the result field is shown. Rather, I would have a TEXT field and script setting it with the eSQL result when needed.
The advice above would help speed the eSQL on large datasets. To recap:
1. remove the JOIN, add the parameter
2. script with Set Field, instead of calculate
2 of 2 people found this helpful
To add to Beverly's excellent advice, be sure the record is in a committed state when you fire the query. When you do this, the query is processed on the server and only the result is returned. OTOH, if you have any open records, FileMaker will send all records down from the server to the client and process the query there. Over a remote connection, that's going to be really slow with 460K records.
If external SQL, can you have a 'view' that returns this as you may need it? Let the SQL server do the work!
With FileMaker's current architecture, ExecuteSQL is not going to be particularly fast. FileMaker's system translates the SQL into equivalent instructions and then executes them to produce the result.
Since this is a very simple query, using a non SQL method, and thus eliminating the "translation" step may be worth testing on your set up to see if it has better performance.
- if native FM functionality is possible, use it rather than ExecuteSQL().
- If the source is external (SQL), try to get a view (pre-set query) to return the result - let the SQL server do the work!
- if ExecuteSQL() is used for a good reason, there are ways to try and optimize.
6 of 6 people found this helpful
With FileMaker's current architecture, ExecuteSQL is not going to be particularly fast. F
I want to be a little more nuanced than this. ExeucteSQL() can be blazingly fast even on really big tables.
See my test file from Devcon a few years ago:
A SELECT query on a 1.5 million record table hosted in the cloud:
less than a 3rd of a second for the first run, down to a tenth of a second for subsequent runs. I would call that fast, for a context-free way of querying. Obviously it is even faster on the LAN.
The things that will slow ExecuteSQL() down are:
- open records by the client's session in the target able (that's the point of the demo, try it - it's jaw-dropping)
- complex queries with multiple JOINs and SQL functions (very often that complexity is not needed in the SQL call and can be handled differently)
1 of 1 people found this helpful
Sure, just add an index!
In MySQL with a recent test, I reduced the query time from 2 seconds to 0.09 seconds with a simple b-tree index. The same basic query in FMP took almost 8 seconds!
Thanks for that Wim, haven't seen that file before - very enlightening! I presume that the performance penalty of having an open record in the target table when the table source is an external data source would be even more pronounced, which is certainly what I've seen whilst connected to MySQL sources over ODBC / ESS.