0 Replies Latest reply on May 18, 2014 10:53 PM by dinoapolito

    Execute SQL is EXTREMELY slow - hangs and crashes



      Execute SQL is EXTREMELY slow - hangs and crashes


           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