Execute SQL function EXTREMELY slow - hangs and crashes
Operating system version
Description of the issue
I have some tables where I have added a calculation field that runs an execute sql function to other tables. Like this
"select ProductCode, copies from ordercomponents
; " x" ; ", ";OrderItemID )
This all works brilliantly on my test database which only has a few thousand records but when I use it on a customer database it falls down in a screaming heap. The FROM table in the customer database (ordercomponents) has over 1 million records in it.
As soon as I load a layout that has a value for this field Filemaker hangs, and is non responsive. I have waited 20 minutes before killing the process.
Is there anything I can do or is this beyond Filemaker?
The tables themselves are shadow tables from an external database. The equivalent query elsewhere directly on the external database is very fast.
While Filemaker was hung I ran MySQL Workbench and executed a SHOW PROCESSLIST to see what was happening. It is bizarre. It was sending the followoing query
"Select ordercomponentid from orders where ordercomponentid>1" and it was doing this for every orcercomponentID eg
"Select ordercomponentid from orders where ordercomponentid>2"
"Select ordercomponentid from orders where ordercomponentid>3"
Why would Filemaker be asking MySQL to do this?. There are 1.5 million records in that table
Hope someone can help
Steps to reproduce the problem
A calculation field on a shadow table (orders) is using an execute sql function to query a related shadow table (ordercompnents).
The two tables are related v1a an orderid
The orders table has 500,000 records
The ordercomponents table has 1.5 million
For each orderid in the orders table I expect a result from the ordercomponent table
It hangs on large databases and seems to be sending a stupid query to the server