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

    Execute SQL is EXTREMELY slow - hangs and crashes

    dinoapolito

      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