AnsweredAssumed Answered

Execute SQL is EXTREMELY slow - hangs and crashes

Question asked by dinoapolito on May 18, 2014

Title

Execute SQL is EXTREMELY slow - hangs and crashes

Post

     I have some tables where I have added a calculation field that runs an execute sql function to other tables. Like this

ExecuteSQL ( 

      
"select ProductCode, copies from ordercomponents
      
where  orderitemid=?
"
 ; " 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.
      
     Hope someone can help
      
     Dino

Outcomes