Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

performance of Order by Clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Baby Priya

New User


Joined: 06 Aug 2008
Posts: 3
Location: Bangalore

PostPosted: Thu May 21, 2009 12:14 pm    Post subject: performance of Order by Clause
Reply with quote

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
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 21, 2009 2:08 pm    Post subject:
Reply with quote

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 ...
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Thu May 21, 2009 2:28 pm    Post subject: Reply to: performance of Order by Clause
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 21, 2009 7:51 pm    Post subject:
Reply with quote

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. . .
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu May 21, 2009 11:11 pm    Post subject: Reply to: performance of Order by Clause
Reply with quote

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
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri May 22, 2009 10:36 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri May 22, 2009 2:04 pm    Post subject:
Reply with quote

http://www.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts CONTIG for performance? JPVRoff JCL & VSAM 7 Fri Jun 09, 2017 8:39 am
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us