FM 12's ExecuteSQL with "Group By" option = horribly slow
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. :)
"SELECT Text, Count (Text)
GROUP BY Text"
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:
The 22 seconds seems like very poor performance for this function. Is anyone else running into this? Am I doing something wrong?