1 2 Previous Next 22 Replies Latest reply on Jun 10, 2016 5:27 AM by FrankvanderMost

    FQL LEFT OUTER JOIN gets 471 times slower if there's more than 1 criteria in the JOIN

    VincentL

      Summary

      FQL LEFT OUTER JOIN gets 471 times slower if there's more than 1 criteria in the JOIN

      Product

      FileMaker Pro

      Version

      13.0v3

      Operating system version

      Mavericks 10.9.4

      Description of the issue

      Using ExecuteSQL against filemaker database, having more than 1 criteria in a LEFT OUTER JOIN absolutely kills performance (471 times slower) no matter how simple is it.

      This takes 0,07s to perform

      SELECT A.product_id FROM products A
      LEFT OUTER JOIN competitors_prices B
      ON A.product_id=B.product_id


      This takes 33s to perform

      SELECT A.product_id FROM products A
      LEFT OUTER JOIN competitors_prices B
      ON A.product_id=B.product_id AND 1=1

      This takes 37s

      SELECT A.product_id FROM products A
      LEFT OUTER JOIN competitors_prices B
      ON A.product_id=B.product_id AND B.matching=1 AND B.flux_id=1

      Steps to reproduce the problem

      Run the above queries

      all details here

      you may download my example file here

      https://www.dropbox.com/s/kt8ykt8783se7qy/QueryTesting.zip

      Expected result

      The LEFT OUTER JOIN  with more than 1 criteria, considering those added criteria are fully indexed or very simple, shouldn't be slower by such orders of magnitude.

      Moreover Filemaker 13 SQL documentation doesn't warn about that performance loss.

      Actual result

      471 times slower.
      yes slower can be accepted but not that much, especially as all added criteria are so simple.

      Configuration information

      FMPa 13v3 (also trie 12 same thing)
      Mac OS X 10.9.4

      Workaround

      use only ONE criteria in LEFT OUTER JOIN, and move the criteria in the WHERE close like Tim (https://fmdev.filemaker.com/message/152179#152179) suggested.

      However this complicates a lot the query, even more so if you need several cascading JOIN.
      This produce a not really evadable code, and it's very difficult to create the query.

        1 2 Previous Next