4 Replies Latest reply on Oct 22, 2013 1:43 PM by LSNOVER

    ExecuteSQL is very slow with ESS data

    deninger

      Maybe this is not news to everyone here, or maybe my setup is very sub-optimal, or maybe I am missing something significant but...

       

      I was very excited to implement ExecuteSQL Function in some of my solutions. My thought was that I could quickly grab data from a table with SQL instead of switching to a Filemaker Layout, scripting a search, and returning the value I was interseted in.

       

      In practice, however, a simple call like:

       

      Let (

      [

      $dispenseID = $someValue; // passed to the step from earlier

      $sql = "SELECT ITEM.NDC_CODE FROM ITEM, DISPENSE

      WHERE DISPENSE.DISPENSED_ITEM_ID = ITEM.ITEM_ID

      AND DISPENSE.DISPENSE_ID = " & $someValue

      $result = ExecuteSQL ($sql ; "" ; "" )

      ];

       

       

      $result

      )

       

       

      is much, much slower than switching to the Item layout and searching for the dispense ID, returning to the original layout and returning the needed value.

       

      Is the problem related to the fact that the data being accessed by the ExecuteSQL is actually an ESS datasource instead of a native FM table?

        • 1. Re: ExecuteSQL is very slow with ESS data

          I believe the problem is that you are jumping through two hoops by using ExecuteSQL with ESS, with FMP as the middle-man. ExecuteSQL talks to FMP, which then asks the SQL table.

          • 2. Re: ExecuteSQL is very slow with ESS data
            gdurniak

            From the "Missing Guide", it appears that using  External SQL Sources (ESS) table occurrences is "legal"

             

            http://www.filemakerhacks.com/?p=6406

             

            but not sure if "much, much slower than switching to the Item layout and searching for the dispense ID" is a fair comparison. Does the "Find" on the layout also span two tables ?

             

            Check your query with another tool, to make sure it works, e.g. that indexes are properly used, and make sure the data types are comparable, e.g. is $someValue an Integer, or Text ?

             

            greg

            • 3. Re: ExecuteSQL is very slow with ESS data
              beverly

              "legal" does not necessarily equate with "wisdom"

              for the word from FMI on the subject, see the FM12 ODBC/JDBC Guide (pages 6, 7, 10 & 11)

               

              Stephen's right. You are asking FMP to return ALL records from SQL table(s) and then perform a calculation on them (albeit in SQL-speak).

               

              If you have control of the SQL server and can write a "view" that narrows down your number of records, then you will have boosted the performance regardless of how you ask for the data in those records (find, eSQL, etc.)

               

              Beverly

              • 4. Re: ExecuteSQL is very slow with ESS data
                LSNOVER

                As Beverly stated being ABLE to do something doesn't make it practical.

                 

                Unfortunately, Filemaker does not hand the SQL you are executing back to the SQL database, so the data has to be fully read into Filemakers Data Cache before the ExecuteSQL command can be executed against the data.  In addition, the data that IS brought into Filemaker from SQL is not indexed in any way, so you get a double zap.

                 

                Unless the table is very small, avoiding using ExecuteSQL against an ESS Table at all costs. 

                 

                Hopefully this will be improved in future releases.  In the mean time, send Filemaker a nice note and ask them to improve this.

                 

                http://www.filemaker.com/company/contact/feature_request.html

                 

                Filemaker is also VERY slow at sorting ESS data, even in the scope of Portals with single tables and predefined sort fields.  It would be great if they could pass the "sort" command back to the ESS database.   Would improve things dramatically in many instances.

                 

                Cheers!

                Lee