1 2 3 Previous Next 33 Replies Latest reply on Feb 21, 2016 7:02 AM by disabled_morkus

    Why is ExecuteSQL So Slow (what's the alternative)?

      I have a simple query on a table with about 500,000 rows.

       

      SELECT max(id) from <table_name>

       

      In MySQL, this query takes 2 seconds.

       

      In FileMaker ExecuteSQL- in the data viewer within FileMaker, it takes, depending on the attempt, anywhere between 45 seconds to  a minute!

       

      The "id" field is indexed in FileMaker so I don't know how to further optimize this SQL.

       

      Other ExecuteSQL queries I've tried that use this 500,000-row table also seem to take forever (in FileMaker). I gave up on one sub-query after 5 minutes.

       

      -----

       

      So, trying to do things more in a "FileMaker way", for acceptable performance, is there a way to do a "Find" where you can specify any of the FileMaker functions, like min, max, etc? I know you can't do RegEx, but how about the built-in FM functions? I don't want/need a "calculated field" since this is an ad-hoc query.

       

      Not sure how to do this "Find" quickly. 45 seconds to a minute isn't going to cut it.

       

      This might be a case where a plug-in could really help, but I'm probably missing something basic here about how to best use FileMaker to find the kinds of stuff I need.

       

      TIA

       

      - m

        1 2 3 Previous Next