AnsweredAssumed Answered

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

Question asked by disabled_JustinClose on Jun 18, 2012
Latest reply on Sep 12, 2015 by Vincent_L

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

Outcomes