execute SQL searching in uncommitted state.
Operating system version
Description of the issue
I've been using the executeSQL script step to perform a simple SELECT. If the records are not committed beforehand, the SQL engine appears to have no access to any indices, making every search the equivalent of a search on an unindexed field. This can be very slow, compared to the equivalent Filemaker 'find' or a list(field) across a relationship, which return results even if the records have not been committed.
This really comes to light when trying to use an executeSQL statement to replace field contents with a calculated result.
It doesn't matter what field within the record you have modified -- i.e. even if the uncommitted data is in a field that isn't used in any relationships, and isn't being searched on in the SELECT statement.
Steps to reproduce the problem
Perform an SQL SELECT statement on a table with a few hundred or thousand records, when the you have not committed changes.
Indexes (or at least cached versions of the indexes) should be available for the SQL search.
Search is extremely slow -- many minutes to perform a relatively simple SELECT statement, whereas if you commit records first it takes a fraction of a second.
I think this is the same on the PC (at least using a PC client), but I haven't tried using a PC server as a host.
make sure records are committed whenever possible, otherwise using more traditional filemaker methods.