5 Replies Latest reply on Feb 6, 2017 5:19 AM by rkappel

    ExecuteSQL

    rkappel

      I am struggling with why it takes to run an ExecuteSQL step the first time it is run during a session.

      I have a portal that will return a list of available inventory based on a set of parameters.  The user enters a search term and a script combines the users input with some other settings to perform a SQL query on one table which returns the keys to a series of records in a second table that populates the portal.

      The text of the SQL statement is this:

       

      SELECT KF_INVENTORY FROM IV_INVENTORY2 AS IV

                 WHERE UPPER ( IV.Stock_Status ) LIKE '%STOCK%'

       

                 AND ( UPPER ( IV.Item_No ) LIKE '%HEAT%'
                            OR  UPPER ( IV.Pattern_Name ) LIKE '%HEAT%' )

       

                 AND  UPPER ( IV.Warehouse ) IN  ( '465' , '455' )

       

                 AND  UPPER ( IV.Company ) IN  ( 'FFW' , 'MCS' , 'CCA' , 'CSS' , 'CTI' , 'INV' , 'CCW' )

       

      When this query runs the first time it takes just over a minute to return a value.  Subsequent executions, with different or the same user input, take about 4 seconds.

       

      The table that the query runs on has 11 fields and 60,000 records.  

       

      Does anyone know if there is something I can do to speed up the initial execution?

       

      Thank you.

        • 1. Re: ExecuteSQL
          siplus

          put the fields in the order that mostly reduce the found set first.

           

          I'll give you an example. Let's say you have a medical practice with 2 doctors and you're looking for the invoices of doctor A, current year.

           

          In the first 1.5 years they use your software,

           

          SELECT invoiceAmount FROM invoices WHERE DoctorID = ? AND InvoiceYear = ? (1; 2016)  will be faster than

          SELECT invoiceAmount FROM invoices WHERE InvoiceYear = ? AND DoctorID = ? (1; 2016)

           

          After 15 years,

           

          SELECT invoiceAmount FROM invoices WHERE InvoiceYear = ? AND DoctorID = ? (1; 2016)  will beat the shot out of

          SELECT invoiceAmount FROM invoices WHERE DoctorID = ? AND InvoiceYear = ? (1; 2016)

           

          so place the arguments in the order that mostly restrains the found set after every argument used.

           

          In order to further reduce the time, place a useless SQL in the opening script, where the user is used to wait some time before using the solution - users are willing to wait during loading but not when using.

           

           

          ---

           

           

          Try to avoid using LIKE - if you use this kind of query very often, create stored calc fields that use patternCount and return 1 or 0 and search on them with field = 1.

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL
            wimdecorte

            Very likely the delay is because of the LIKE function, but just in case: also make sure the user running the query does not have any open record in their session for the target table.  That will force FMS to send over all the data, something you can easily check by using the FMS stats log.  60,000 records is enough data that you will notice the effect.

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL
              user19752

              In addition, using UPPER(field) need calculation on all records, make the query can't use index.

              • 4. Re: ExecuteSQL
                fmpdude

                I have similar issues with LIKE and FMP's ExecuteSQL (and good performance).

                 

                In one database with 500,000 names (I have posted the data and the query used previously), it took FMP between 4-6 seconds each time to do a simple query with LIKE.

                 

                Like this: Select * from names where name LIKE '%at%'

                 

                Using MySQL with a b-tree index I control, on the other hand, this query takes about 0.2 seconds.

                 

                If you can stick to FMP's internal "Find"s which are extremely fast, that would be best. Hopefully, the SQL engine will improve over time. I now avoid FMP SQL mostly for anything beyond the most basic SQL queries (no LIKE queries).

                 

                HOPE THIS HELPS.

                • 5. Re: ExecuteSQL
                  rkappel

                  Thank you to all who have replied and given advice on this problem.  This was a great help to me troubleshootiungg the issue because it led me in the right direction.  I discovered that the biggest problem was that one of the fields that was included in my query was a calculated field based on a related table.  When I changed that to a text field the query ran in less tha a second every time.