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.