Thank you for your post.
In your steps to reproduce, you mention that Execute SQL is used to query a related shadow table, but the Execute SQL appears to be from a primary table. Can you clarify?
If ordercomponentid is an unstored calculation field, this will slow things down. Overall, make sure ordercomponentid is indexed on the customer system to improve performance.
As far as the multiple SELECT statements, are you running this through a Looping script? That way, multiple SELECT statements would be queueing up waiting for other SQL queries to complete.
Using a custom function instead of embedding SQL statement directly in a calculation can also improve performance. If you need field dependency that triggers recalculation, you can add them, but there is a performance bitt, especially with large data sets (1.5 million records).
Any other information you can provide may be helpful.
All my tables are shadow tables. Consider two such tables Orders and OrderComponents.
In Filemaker I have added a field to the Orders shadow table. This is a calculation field - the only option when adding fields in Filemaker to shadow tables.
That calculation field is using the ExecuteSQL function as described above. Basically it is looking up the ordercomponents table based on orderitemID. The two tables are related by this field.
You asked about the ordercomponentid. It is a field on the external database. It is indexed on the external database. The orderitemID is also indexed. I do not have storage options for fields in shadow tables nor for calculation fields in shadow tables. These are greyed out.
Regarding the multiple selects. That is not me doing it. I do not have any looping script. It is what the ExecuteSQL function is doing on the server as soon as I hit a layout that has the calculation field with the ExecuteSQL function. It seems to query the orcercompenents table for every ordercomponentID (in batches of 25) then it selects the result with the orderitemIDs as evealuated by the expression.
Thank you for the clarification. I needed to ensure the proper fields were indexed.
For the calculation field that performs the ExecuteSQL function. Is this a stored or unstored calculation? If unstored, FileMaker will execute every time a record is accessed. On top of that, if the layout is set to view by list or table, each displayed record needs to be calculated.