2 Replies Latest reply on Sep 12, 2015 5:16 PM by Vincent_L

    FM 12's ExecuteSQL with "Group By" option = horribly slow

      Title

      FM 12's ExecuteSQL with "Group By" option = horribly slow

      Post

      I am running into nasty performance problems when trying to run an ExecuteSQL statement that includes the "Group By" clause.  We are trying to be good and do more development in new FM 12 features.  :)


      [CODE]ExecuteSQL (
      "SELECT Text, Count (Text)
      FROM Test
      GROUP BY Text"
      ;"";""
      )[/CODE]


      I have a very simple table structure as I was just doing some SQL testing.  See attached image cap.


      The short description is that we are trying to count the occurrence of words.  The table has some 4100+ records, each one (the "Text" field) simply a word from a document.  The SQL statement above takes 22 - 25 seconds!!  (Depends on the machine that runs it.)  So I have been trying to test whether different techniques will help (indexing, this numeric field thing, etc).  I got a little better results from a numeric field (19 instead of 22) but nothing from an index (which makes sense, but had to try it).

      Here's a sample of my records showing the 'Text' and 'NumericID' fields:
      [CODE]
      availability 96
      availability 96
      available 97
      average 98
      awake 99
      aware 100
      aware 100
      away 101
      back 102
      back 102
      bag 103
      [/CODE]


      The 22 seconds seems like very poor performance for this function.  Is anyone else running into this?  Am I doing something wrong?


      Thanks,
      J

      Screen_shot_2012-06-15_at_20.35.16.png