This sounds like a hosted solution. The first time FMP must query the Server for the data needed to perform the ExecuteSQL() command. The second time it is performed the data is in cache or the temp file.
The number of WHERE clauses does not dramatically affect performance. More can sometimes improve performance.
In addition, if the query gathers or uses data from more than one table you must include a JOIN statement to tell FMP explicitly what relationship to use. The ExecuteSQL() function does not even know about the TOG's let alone use them. I have found that adding the required JOIN can improve performance considerably (at least 2X).
That completely makes sense. I had not remembered the caching of data.
I'll try the join, although there's no real join in this. The ExecuteSQL statement does this:
WHERE IncHm_Boolean = ?
AND Teacher = ?
AND Class = ?";"";""; 1;$Teacher;$Class)
Would this benefit from a join? Would that make it faster?
This query doesn't use more than one table so a JOIN is not needed.
That's what i thought. Is there a way to speed up the cache of the data? People are very happy that this has been sped up, this process, but that first one they're still reporting that first query is slow.
Could I do a pre-query to force the cache before they go to enter this info?