AnsweredAssumed Answered

execute SQL searching in uncommitted state.

Question asked by JonJ on Aug 20, 2012

Summary

execute SQL searching in uncommitted state.

Product

FileMaker Pro

Version

12.0.02

Operating system version

Mac

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.

Expected result

Indexes (or at least cached versions of the indexes) should be available for the SQL search.

Actual result

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.

Configuration information

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.

Workaround

make sure records are committed whenever possible, otherwise using more traditional filemaker methods.

Outcomes