1 2 Previous Next 19 Replies Latest reply on Oct 30, 2013 3:24 PM by psijmons

    FileMaker ExecuteSQL: MAX Function Weirdness

    TimDietrich

      I'm a big fan of ExecuteSQL, and find myself using it frequently. However, every once in awhile I find that it behaves in odd and unexpected ways...

       

      I'm currently working on a custom accounting system, which makes extensive use of ExecuteSQL. At one point, I needed to quickly get the maximum value of a field in one of the tables - and it is one of those cases where no relationship to the table was available. So using ExecuteSQL made sense.

       

      The table contains 32,689 records. The field that I need the maximum value from is setup as a number and it is indexed. The database that I'm working on is currently local (not hosted).

       

      Here is the original ExecuteSQL call:

       

      ExecuteSQL ( "SELECT MAX ( Period_Posted ) FROM GL_Transactions"; "|"; ¶ )

       

      I assumed that FileMaker would make use of the index on the field and quickly return the maximum value. However, much to my surprise, it took FileMaker approximately 8 seconds to return the value. So it appears that FileMaker did a table scan to get the value, instead of using the index.

       

      I then started experimenting to see if I could come up with another way to quickly get the maximum value. I decided to see how long it would take FileMaker to get the distinct values in the field, like this:

       

      ExecuteSQL ( "SELECT DISTINCT ( Period_Posted ) FROM GL_Transactions"; "|"; ¶ )

       

      This time, FileMaker returned the values in under a second. So it appears that it is using the index on the field to resolve that query. Interesting!

       

      Next, I decided to use the DISTINCT query to get the maximum value. It required adding a SORT BY clause to the query, and wrapping the ExecuteSQL call with a GetValue call. Here's what that looks like:

       

      GetValue ( ExecuteSQL ( "SELECT DISTINCT ( Period_Posted ) FROM GL_Transactions ORDER BY Period_Posted DESC"; "|"; ¶ ); 1 )

       

      That calculation returns the maximum value in just about one second. Problem solved!

       

      My take-aways from this:

       

      • Using ExecuteSQL to get a maximum value from a column is terribly inefficient, even when the column has an index on it.

      • Using ExecuteSQL to get distinct values from a column is efficient, and apparently does make use of an index when it is available.

       

      -- Tim

        • 1. Re: FileMaker ExecuteSQL: MAX Function Weirdness
          DavidJondreau

          Did you quit FileMaker between each test? Have you tried the DISTINCT method on a newly opened file?

           

          FileMaker may have cached the results from your first Max() test skewing your results.

          • 2. Re: FileMaker ExecuteSQL: MAX Function Weirdness
            taylorsharpe

            Yes, I have found that the Max function is slow in ExecuteSQL and appears not to make use of the index.  I've found I can get faster results with a cartesian join and calcluation of the max value. 

            • 3. Re: FileMaker ExecuteSQL: MAX Function Weirdness
              TimDietrich

              David --

               

              "Did you quit FileMaker between each test? Have you tried the DISTINCT method on a newly opened file?"

               

              Yes, I did - and the result was the same. It really looks like FileMaker does a table scan to resolve the MAX function, but uses an index to resolve a DISTINCT request. This seems very odd to me, because there are plenty of cases where DISTINCT cannot be resolved with an index...

               

              -- Tim

              • 4. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                TimDietrich

                Taylor --

                 

                "I've found I can get faster results with a cartesian join and calcluation of the max value."

                 

                I'm seeing this, too. The good old Maximum summary field type, and Max calculation function, seem to be the most efficient ways to get a max value.

                 

                The problem with this approach, of course, is that you end up with yet another Table Occurence on the relationship graph.

                 

                -- Tim

                • 5. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                  FileKraft

                  Tim, great observation using DISTINCT and GROUP BY to fetch the max! there is another dependency effecting the result if not accessing the data with full access:

                   

                  i did some testing and get:

                   

                  logged in with FULL ACCESS:

                  MAX: 1091 msec

                  DISTINCT GROUPED:  105 msec

                   

                  logged in with RESTRICTED ACCESS - simple predicate via TOG one table away

                  MAX: 6055 msec

                  DISTINCT & GROUPED: 5021 msec

                   

                   

                  -uli

                  • 6. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                    TimDietrich

                    Uli --

                     

                    Wow! It seems as though FileMaker is applying the access restrictions first, and then performing the SELECT against the resulting (filtered) table.

                     

                    I don't know that I would ever have considered the impact that the account being used has on ExecuteSQL's performance. Thanks for pointing that out!

                     

                    -- Tim

                    • 7. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                      FileKraft

                      Tim - your improvements still kick in - just my overhead of 5 sec by access privilege enabled - Amdahl's Law ..

                       

                      thanx again!

                       

                      (i wish security wouldn't have such a penalty - the price is steap regarding just checking 30 records of a table one relationship away)

                      • 8. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                        mardikennedy

                        Apologies in advance, I know I should be doing my own testing but I'll be very interested to get feedback from our ExecuteSQL users...

                         

                        So, what you're saying is that the ExecuteSQL performance takes a big hit if RLA is enabled?  As in, totally different experience than for the Full Access developer?  Yes?  Or am I misunderstanding?

                         

                        And would this only apply when extras are involved like DISTINCT etc, or would it apply in any SELECT statement?  (I'm guessing that it's when there are extras, as otherwise the experience would probably be better known?)

                         

                        All the best,

                        Mardi

                        • 9. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                          taylorsharpe

                          I just had to give this a try. 

                           

                          I ran an unstored calculation a bunch of times.  I compared calculations for finding the Max date in a field:

                           

                               Max ( Add_Date )

                           

                          and

                           

                               Let ( [

                               F2 = "SELECT Distinct ( Add_Date ) FROM Survey_Paper" ;

                               F3 = ExecuteSQL ( F2 ; Char ( 9 ) ; ¶ ) ;

                               F4 = ValueCount ( F3 ) ;

                               F5 = GetValue ( F3 ; F4 )

                           

                               ] ;

                               F5

                               )

                           

                          Obviously the first one is the normal method we use to calculate a max date and is simplist.  I ran a test comparing these two calculations on a database I have.  I ran it over and over to make sure there wasn't much variance.  I used the Base Elements Plugin to measure the speed down to the miliseconds. 

                           

                          RESULTS: 

                               2.515 seconds using the FileMaker Max function

                               0.575 seconds using the SQL with DISTINCT function

                           

                          I then decided I wanted to know what the speed was if I ran the ExecuteSQL with a Max function in it like:

                           

                               Let ( [

                           

                               F1 = Survey_Paper::Add_Date ;

                               F2 = "SELECT Max ( Add_Date ) FROM Survey_Paper" ;

                               F3 = ExecuteSQL ( F2 ; Char ( 9 ) ; ¶ )

                           

                               ] ;

                               F3

                               )

                           

                          The results were around 2.46 to 2.47 seconds. 

                           

                          CONCLUSION:  Be wary of the Max and Min functions in FileMaker, even if using ExcuteSQL.  You will get much faster results using the DISTINCT function.

                           

                          RECOMMENDATION:  Use the example ExcuteSQL with DISTINCT call to obtain a max or min date.  I assume the results will be the same for numbers and text fields, but haven't tested them yet.  

                           

                          PS:  I also tried the GROUP BY option instead of DISTINCT and it took about 2 minutes.  So avoid GROUP BY even more than the Max and Min functions unless you absolutely need it!

                          2 of 2 people found this helpful
                          • 10. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                            FileKraft

                            Mardi, security settings are evaluated all the time as specified so the overhead is tremendous as you can see in my measures. i wish they would be just maintained when you log on and if records are altered or added or deleted checked or unchecked of the set where you have access to. i am not FM so can't tell just assume how it is implemented.

                            One thing for sure: Nothing is free!

                            • 11. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                              TimDietrich

                              Taylor --

                               

                              This is really good info. Thanks for sharing what you've found!

                               

                              Based on your research, it sure looks like FileMaker isn't using an index to resolve any Max or Min calculations, regardless of whether you're using ExecuteSQL or the Max and Min functions. That's interesting and good to know. And also very surprising - I would think that the Max and Min functions would be optimized to use indexes when they're available, especially when you consider how long we've had those functions.

                               

                              The results of your GROUP BY test don't surprise me. I think that FileMaker needs to scan the table in order to resolve that query, so it can't use an index in that case. The poor performance of that test makes sense.

                               

                              At this point, I think what is most interesting about what we've found is that ExecuteSQL with a DISTINCT query is extremely efficient. It appears to be using an index when it can. So if you're looking to get better performance when calculating maximum and minimum values, the "ExecuteSQL with DISTINCT / GetValue" method that I described earlier is something to consider.

                               

                              -- Tim

                              1 of 1 people found this helpful
                              • 12. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                                FileKraft

                                grouping the result of any SQL statement might happen without explicit iterations if ORDER BY and GROUP BY are using the same fields - but the grouping has also some stuff to do so additional costs added to the bill ..

                                • 13. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                                  psijmons

                                  Taylor,

                                  was this done on a test set where records were sorted by date already?

                                  And if so, what would the delay be when the records were in a more random order?

                                  • 14. Re: FileMaker ExecuteSQL: MAX Function Weirdness
                                    taylorsharpe

                                    The dates were in random order and not sorted.  The Date field was indexed and its type was Date.  There were 10397 records. 

                                    1 2 Previous Next