5 Replies Latest reply on Jul 11, 2013 4:30 AM by gdurniak

    ExecuteSQL - amazing, and slow

      Nothing radially new to report, but I'm so enamored with what can be done with ExecutSQL, and yet I have very few uses where it remains implemented simply because of speed - especially on FMS with many records. Front-end stuff seems to be the best realistic use.

       

      I was in need of 12 columns in a report, one for each month in the past. Simple (once I got past syntax errors) and worked well locally and with less than a million records.

       

      ExecuteSQL ( "SELECT SUM(

      CASE ? WHEN 'Standard' THEN Standard_CM_TY WHEN '9-Liter' THEN liter9_CM_TY WHEN 'Dollar' THEN Dollar_CM_TY WHEN 'Bottles' THEN Bottles_CM_TY END

      )

      from DATA

      WHERE PRODUCT_KEY = ? and STORE_KEY = ? and Premise = ? and Yr_Mth = ?"

      ; "" ; "" ;

      $$units ; PRODUCT_KEY ; STORE_KEY ; Premise ; Yr_Mth - 11 ) ;

       

      Put the above on the server (60 million records) and it is so slow that I cannot ask the client to run reports. Even overnight can be out of the question.

        • 1. Re: ExecuteSQL - amazing, and slow
          TimDietrich

          Scott --

           

          I feel your pain. I've run into similar weirdness with ExecuteSQL, where it seems like FileMaker should be able to execute the query that I've thrown at it with ease, and yet it just churns on it. In those cases, what I've been doing is to try figure out what, exactly, FM is struggling with.

           

          In your case, have you tried running the query without the CASE statement to see if that's the problem? For example, maybe hard-wire for one of the unit types to see if it helps at all?

           

          ExecuteSQL ( "SELECT SUM(liter9_CM_TY) from DATA WHERE PRODUCT_KEY = ? and STORE_KEY = ? and Premise = ? and Yr_Mth = ?"; "" ; "" ; PRODUCT_KEY ; STORE_KEY ; Premise ; Yr_Mth - 11 ) ;

           

          If FM can process that one easily, then you might able to rework your code a little to support the CASE in another way. For example, you could either branch your code so that you have multiple ExecuteSQL calls (one for each unit type) or use a dynamically generated ExecuteSQL statement (along with the Evaluate function).

           

          Also - and you've probably already done this - check to see that the fields that you're using in the WHERE clause (PRODUCT_KEY, STORE_KEY, Premise, and Yr_Mth) all have indexes on them.

           

          Hope this helps!

           

          -- Tim

          • 2. Re: ExecuteSQL - amazing, and slow

            Thanks very much for the advice Tim. I considered letting FMP handle the CASE statement, but since one simple report with my code above would have taken several hours (long scrolls to get one number), I'm reverting to a tried and tested FMP method that does not involve additional table occurrences or relationships.

             

            I'll simply find 12x the number of rows and add the sparse table to form the columns. Find time increase is negligible, sort time is x12, but sums are pretty fast (much MUCH faster than ExecuteSQL). Because I'm not using sums of related values (hops of any length are always a major concern in a huge table) the speed is probably two magnitudes faster. Cleaner relationship graph to boot.

             

            It's great that remote find requests in 60M rows on FMS is really fast. I've taken a product that had been zipped and placed on an FTP server for decades to a FMS hosted version, and the clients love it. FileMaker 12 more than anything made this possible.

            • 3. Re: ExecuteSQL - amazing, and slow
              BruceRobertson

              Scott can you explain that a little more? 12X the number of rows. I assume that doesn't mean 720M rows? But then what does it mean?

               

              And - your not adding to the graph; but you are adding a "sparse table"?

              • 4. Re: ExecuteSQL - amazing, and slow

                We fInd 12x the number of rows. We don't find the single month in question for each product (as we'd like to) but we find all 12 months and summarize, hence we find 12x the number of rows and 12x the sort time.

                 

                Adding a sparse table: I guess "adding" was ambiguous here -- we are adding the column values of a sparse table, not adding the table.

                 

                We would be on average adding 11 NULLs for every one number for a total, hence a sparse table (i.e., sparse matrix with many zero or NULL values).

                 

                Not the best way to do things from a strict relational DB standpoint, but one that FileMaker handles quite nicely. We gladly take the 12x hit in sort speed to see a 100x speed increase in adding lots of numbers. I'm not sure of the real speed increase, but when a report goes from unusable to less than a minute (most under 15 seconds) then we gladly adjust for FileMaker's strengths.

                • 5. Re: ExecuteSQL - amazing, and slow
                  gdurniak

                  It sounds like you gave up on ExecuteSQL, and just summarize a layout now instead

                   

                  you are correct that if you require "sums of related values", it will be horribly slow

                   

                  it would be interesting to remove the "SUM" from your query, to check that it "finds" the proper number of records. A SUM over millions can also be a killer

                   

                  greg

                   

                  PS

                  For one solution with multiple SUMS, I ended up exporting the FileMaker records to a "real" SQL database, then queried that for the results. This was over 1000 x faster