Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries.

Does this ORDER BY affect performance in any way or is it ignored by the profiler as the rows are already in this order?

In my instance I am using a SQL Server 2005 database

share|improve this question
    
SQL Server? version? –  JNK Jan 9 '12 at 15:39
    
@JNK added to question –  Ben Brocka Jan 9 '12 at 15:48
add comment

3 Answers

up vote 4 down vote accepted

It almost certainly will affect performance.

If you just do a query like

Select *
From Table
Order by PrimaryKey

It likely won't affect anything at all.

Bear in mind, though, that this only determines the order of the rows at the leaf level of the clustered index. If you do JOINs, or use other indexes that avoid key lookups, then the ORDER BY will generate additional work.

If every index and every table that you will ever JOIN or reference when querying this table are all ordered by the same key (in the same direction) then it probably won't impact performance. Outside that very limited scenario, you will see a hit.

Only use ORDER BY when the order of the results actually matters. In my experience it's seldom necessary.

share|improve this answer
    
In this case I'm grabbing rows for presentation so order matters and I'm usually only JOINING tables which are all clustered on the same primary/foreign key. Is the ORDER BY additional work even when I join two tables clustered in the same exact manner? –  Ben Brocka Jan 9 '12 at 15:58
    
@BenBrocka - it might be. There's really no general rule for this. If the intermediate result sets (i.e. once the tables are joined) needs to be reordered, that's work as well. It's unlikely that there won't be any sorting involved at all. –  JNK Jan 9 '12 at 16:03
1  
@BenBrocka - If you already have a useful index in the order requested already then SQL Server will probably be more likely to consider order preserving operators (e.g. avoiding hash joins and aggregates) unless statistics lead it to believe it will be cheaper not to. –  Martin Smith Jan 9 '12 at 16:42
    
+1 for "Only use ORDER BY when the order of the results actually matters" –  BryceAtNetwork23 Jan 9 '12 at 16:43
add comment

For a complex query, SQL Server may decide that it needs to sort portions of data in your query, and may even require multiple sort operators based on which indexes are available and the complexity of your query.

These sorts can definitely impact the performance of your query especially if you are working with large tables, and in some cases you'll find that adding an Order By statement will significantly change the execution plan SQL Server generates for your query.

If you're interested in seeing the impact of Sorting the queries you're tuning, run the Actual Plan with SQL Sentry Plan Explorer (free) or using SSMS with "Include Actual Execution Plan" turned on and look at the Ordered Indicator by hovering over the individual data steps in the plan. In Plan Explorer you can also look at the Ordered column in the Plan Tree or Top Operations view.

For something as simple as:

Select * 
From Table 
Order by PrimaryKey 

You should find that while the Order By doesn't seem to change the query plan of your query, that the Ordered indicator in the execution plan does change from False to True when you add the Order By.

You'll may have heard that "the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause"; One reason for this is a feature of the Enterprise Edition of SQL Server that allows SQL Server to piggyback one table scan on top of another, sometimes called a "Merry-go-round" scan and also called "Advanced Scanning".

For further research, I highly recommend both of the following books which are currently available as free ebooks from RedGate:

Hope this helps!

share|improve this answer
add comment

Look at the query plan for both queries, you will see that the "order by" clause will result in a sort of the data that is returned by the remainder of the query (which should be in memory, but could be paged if memory insufficient). The time that sort takes is related to the amount of data (it has to walk it at least once) and how well ordered the data already is (it may be correctly ordered if you are sorting on an indexed column, or data joined on a indexed column)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.