3 Replies Latest reply on May 23, 2014 10:29 AM by TSGal

    Execute SQL function EXTREMELY slow - hangs and crashes

    dinoapolito

      Summary

      Execute SQL function EXTREMELY slow - hangs and crashes

      Product

      FileMaker Pro

      Version

      12 Advanced

      Operating system version

      Windows 8.1

      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

      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.

      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

      Dino

      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

      Expected result

      For each orderid in the orders table I expect a result from the ordercomponent table

      Actual result

      It hangs on large databases and seems to be sending a stupid query to the server

        • 1. Re: Execute SQL function EXTREMELY slow - hangs and crashes
          TSGal

               Dino Apolito:

               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.

               TSGal
               FileMaker, Inc.

          • 2. Re: Execute SQL function EXTREMELY slow - hangs and crashes
            dinoapolito

                 Hi,

                 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.

                  

            • 3. Re: Execute SQL function EXTREMELY slow - hangs and crashes
              TSGal

                   Dino Apolito:

                   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.

                   TSGal
                   FileMaker, Inc.