I believe the problem is that you are jumping through two hoops by using ExecuteSQL with ESS, with FMP as the middle-man. ExecuteSQL talks to FMP, which then asks the SQL table.
From the "Missing Guide", it appears that using External SQL Sources (ESS) table occurrences is "legal"
but not sure if "much, much slower than switching to the Item layout and searching for the dispense ID" is a fair comparison. Does the "Find" on the layout also span two tables ?
Check your query with another tool, to make sure it works, e.g. that indexes are properly used, and make sure the data types are comparable, e.g. is $someValue an Integer, or Text ?
"legal" does not necessarily equate with "wisdom"
for the word from FMI on the subject, see the FM12 ODBC/JDBC Guide (pages 6, 7, 10 & 11)
Stephen's right. You are asking FMP to return ALL records from SQL table(s) and then perform a calculation on them (albeit in SQL-speak).
If you have control of the SQL server and can write a "view" that narrows down your number of records, then you will have boosted the performance regardless of how you ask for the data in those records (find, eSQL, etc.)
As Beverly stated being ABLE to do something doesn't make it practical.
Unfortunately, Filemaker does not hand the SQL you are executing back to the SQL database, so the data has to be fully read into Filemakers Data Cache before the ExecuteSQL command can be executed against the data. In addition, the data that IS brought into Filemaker from SQL is not indexed in any way, so you get a double zap.
Unless the table is very small, avoiding using ExecuteSQL against an ESS Table at all costs.
Hopefully this will be improved in future releases. In the mean time, send Filemaker a nice note and ask them to improve this.
Filemaker is also VERY slow at sorting ESS data, even in the scope of Portals with single tables and predefined sort fields. It would be great if they could pass the "sort" command back to the ESS database. Would improve things dramatically in many instances.