2 Replies Latest reply on Nov 14, 2014 11:36 AM by TSGal

    WHERE {field} IN is very slow in executeSQL regardless of the set to be filtered

    JonJ

      Summary

      WHERE {field} IN is very slow in executeSQL regardless of the set to be filtered

      Product

      FileMaker Pro

      Version

      FMPA13.0v03

      Operating system version

      Yosemite

      Description of the issue

      Running an SQL search
      executeSQL (  "SELECT a_ID FROM Table WHERE fk_ID=? AND a_ID IN " & $All ; ""; "" ; localtable::field )

      where $All is a list of some 239 IDs.
      localtable::field is a foreign key field, which will in practice return four or five records.
      Both fields are simple indexed text fields.

      This is enough to lock up filemaker for nearly a minute each time executeSQL is run. It displays a 'find in process. Processing query' dialogue box during the wait..

      However, if I reduce the SQL statement to
      "SELECT a_ID FROM Table WHERE fk_ID=?", then use filtervalues( ) taking the result of the executeSQL (five or so records) and $All is as arguments, and the result is almost instantaneous (as you'd expect).

      This search shouldn't tax filemaker at all, as fk_ID=? returns at most six records, and so the IN statement should only have to check if these records are on the provided list (the equivalent of FIlemaker's native filtervalues function).

      It doesn't matter if $All is calculated dynamically (e.g. using summary list, or a relationship) or is hard coded.

      There's something wrong with FIlemaker's SQL engine if this search is hard work!

      Steps to reproduce the problem

      A table with ~4000 records. Use the data viewer to run an executeSQL search as defined above.

      Expected result

      results found without a delay.

      Actual result

      Serious delay before search is completed. It appears to searching an unindexed field!

      Exact text of any error message(s) that appear

      'find in process. Processing query' dialogue box

      Configuration information

      I've found FM13 to be rather wobbly on Yosemite!

      Workaround

      use filtervalues in combination with executeSQL. This is only a partial workaround, as it's nowhere near as flexible as using a single SQL statement (for example, returning more fields than just the ID field).