0 Replies Latest reply on Aug 20, 2012 4:49 AM by JonJ

    execute SQL searching in uncommitted state.

    JonJ

      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.