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

    Idea that improvement for ExecuteSQL

    naoyuki

      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
          beverly

          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?

          beverly

          • 2. Re: Idea that improvement for ExecuteSQL
            naoyuki

            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
              beverly

              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!

              beverly

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

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

                beverly