FileMaker ExecuteSQL: MAX Function Weirdness

Discussion created by TimDietrich on Oct 25, 2013
Latest reply on Jan 22, 2018 by bigtom

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