performance of Order by Clause

Baby Priya · New User Joined: 06 Aug 2008 Posts: 3 Location: Bangalore

Hi,

I have an SQL query with 'Order By' clause on the indexed columns.I want to understand how does this affect the performance of the query.

Thanks,
Priya

ashimer · Posted: Thu May 21, 2009 2:08 pm

You actually do not require this order by as it is on the indexed cols and if the order is same as that of the index definition ...

enrico-sorichetti · Posted: Thu May 21, 2009 2:28 pm

the order by is an application concern ( data being requested in a certain sequence )
the index is a mix of application requirement ( unique indexes, for example )
and performance concern

so the order by should always be specified
the developer should not worry ( as a general point ) about indexes

seen a few times developers and testers chasing strange results because of indexes added to tables for performance reasons

so ( always as a general point ) I would consider not specifying an order by as an error

dick scherrer · Posted: Thu May 21, 2009 7:51 pm

Hello,

Said slightly differently - if the sequence of the returned rows matters to the application ORDER BY must be specified. Without an ORDER BY, the results are not predictable.

A process that has run as intended for years may suddenly present results "out of sequence" causing lots of grief. . .

Raghu navaikulam · Posted: Thu May 21, 2009 11:11 pm

Hi Baby Priya

If the application requires an ORDER BY, you should provide it no matter whether it is degrading the performance or not. But definitly ORDER BY will reduce the performance even though the column used is an indexed column in the ORDER BY clause. Indexed column help to read the rows from the table quickly. DB2 will not give any guarantee that the rows are placed in the table based on the value of the indexed column.
Since sort (ORDER BY) is the last process of an SQL statement, the indexed column will not have any influence in the performance of the program. It will consider the column as an ordinary column in ORDER BY clause.

Hope this will help you.

Regards
Raghu

ksk · Posted: Fri May 22, 2009 10:36 am

Priya,

We have also faced similar type of situation where due to ORDER BY performance of job was affected severly.

For our requirement, we have removed ORDER BY from the SQL query and added one more step to sort the file on reuquired field using SORT utility.

If you have SORT in your shop, I can sugest you to use sort instead of ORDER BY for performance purpose.

ashimer · Posted: Fri May 22, 2009 2:04 pm

www.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html