Impact of Order By on performance (Production Env.)

harsha_james
harsha_james used Ask the Experts™
on
Hi All,
I am working on Data Migration Queries for migrating one version of product to another version, While writing data migration scripts I need to write scripts to fetch Before Data i.e. select required data before running Data Migration script and  After data i.e. select required data after running Data Migration script. (to verify before and after result in out file)

In before and after script I am using simple select statements without order by clause.
My query is ,
Can I use order by clause  for every select script as customer is going to run them on production environment, What will be impact on performance because of Order by Clause.
Will order by clause affect run time or memory utilization.
Many select statements return record count > 100000.
DB2 Version 9.1 on AIX

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
theGhost_k8Database Consultant

Commented:
Ofcourse order by will cost some. You may check the impact using EXPLAIN.
and thats why there exists pages like: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
and
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
if you have an index with a key that match your order by clause,
it might have no impact because db2 can use the index - depends on which columns you have in the select list

if you don't have an index, a sort will have to be performed and this will slow your queries down

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial