'Group by' is painfully slow

[litenin]

Hi,

We have a IDS 7.31 on an 8*731Mhz CPU Alpha. Everything runs quickly until a group by is added to the query. I know you'd expect the query to be slower but, it's about 50-100 times slower than other databases e.g SQL Server and MySQL with the same data and query running on Dual Pentium Pro PCs. I suspect it's something to do with the Temp DBspace but I'm not really sure how to diagnose this further.

Thanks

Dave

 

[mberni]

to get for information about your problem i would try the following:

write a script:

set explain on;
<your select>

and execute it.

after doing this you will find a &quot;sqexplain.out&quot; file in your current directory, from which you can get info how your server executes your statement, i.e. information about which indices are used, etc.

Maybe you will notice some poor execution path &quot;full table scan, etc.&quot; which could lead you to a missing index on one of the &quot;grouped by&quot; columns. another point could be, that the optimizer choses a somewhat misfitting index-path to execute the statement, so it would help to point the optimizer to the right index via an optimizer-directive.

once i had a performance problem when i moved a database from a 7.31 instance to a 9.x instance. a certain statement performed very poor on the 9.x instance, but it was exactly the same database. the problem was, that the 9.x optimizer interpreted the statistics in a different manner and chose a very bad strategy to fetch the data, i.e. full table scan. the 7.31 optimizer did fine. i had to add a directive to make 9.x use a certain index.

another point could be the amount of data which is returned by the statement in relation to your sortspace, but i would check the above mentioned proc. first.

hope this helps