4 Replies Latest reply on May 25, 2016 6:07 AM by beverly

    Idea that improvement for ExecuteSQL


      I tried a below SQL for table has 1000000 records.

      And the found records(by WHERE part) are only 100 records.

      SELECT category, \"date\", SUM(amount)

      FROM test_table

      WHERE \"date\" >= '2016/05/01' AND \"date\" <= '2016/05/31'

      GROUP BY category, \"date\"

      ORDER BY \"date\"

      Before I tried that, I have had thought that sorting works for only constrained records.

      But I seemed ExecuteSQL method sorts whole records.

      Because I saw progress dialog that counting down remained sort records from about 1000000 to 0.


      So, using ExecuteSQL with ORDER BY or GROUP BY for tables have a large amount records is extremely slow. It's terrible.


      I think that ExecuteSQL method should executes "ORDER BY" part or "GROUP BY" part for constrained records by "WHERE" part.


      I modified the code, because it has some errors. Thank you for pointing out, beverly.

        • 1. Re: Idea that improvement for ExecuteSQL

          Do you  have time comparisons between this method and native FM find & sort? a large data set is going to have speed issue, regardless.

          A work around may be to skip the ORDER BY, create a virtual list table and then Sort that with the summary.

          Also, if this is the exact query, there are a number of errors that would prohibit it even working at all.

          1. 'date' is a reserved word and must be escaped

          2. GROUP BY must have all fields that are not aggregates.

          GROUP BY category, \"date\"

          But I doubt you want that, so remove date from the SELECT list.


          Can you post the exact query or does that simply not matter because you have a large data set?


          • 2. Re: Idea that improvement for ExecuteSQL

            Thank you for your reply and pointing out.


            Of course, I tried native FM find & sort and I know sorting a large data set has speed issue.

            But I didn't mean it to sound like that.


            I actually want that sorted records from only found set.

            So, if I get it with native FM find & sort, I will constrain by "Perform find" and sort for the found set.

            It works very fast.


            But when I do same thing by ExecuteSQL, it seeme that it sorts for whole records in spite of constraining by WHERE.


            I think that ExecuteSQL function should sort after constrain.


            I'm afraid my expression may be hard to read, because I'm not so good at English.

            Do you understand?

            • 3. Re: Idea that improvement for ExecuteSQL

              I agree with you. I just wondered if you did a time comparison.

              I also would like to see ExecuteSQL() improved.

              There may already be Ideas posted to do that. https://community.filemaker.com/community/discussions/product-ideas

              If so, please vote on them!


              1 of 1 people found this helpful
              • 4. Re: Idea that improvement for ExecuteSQL

                reminder: ExecuteSQL() does not work on Found Sets.