Subscribe to this blog
Briefcase
Print
Ordinarily, column order of a SQL statement does not matter.
Select a,b,c
from table
Select c,b,a
from table
However, sometimes it can make a difference.
select SalesOrderID,
CustomerID,
OrderDate,
ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc,
ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc
from sales.SalesOrderHeader
order by CustomerID,OrderDate
option(maxdop 1)
If you look at the execution plan, you will see similar to this
That is three sorts. One for RownAsc, one for RownDesc and the final one for the ‘Order by’ clause. Sorting is an expensive operation and one that should be avoided if possible. So with this in mind, it may come as some surprise that the optimizer does not re-order operations to group them together when the incoming data is in a similar (if not exactly the same) sorted sequence.
A simple change to swap the RownAsc and RownDesc columns to produce this statement :
select SalesOrderID,
CustomerID,
OrderDate,
ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc ,
ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc
from Sales.SalesOrderHeader
order by CustomerID,OrderDate
option(maxdop 1)
Will result a different and more efficient query plan with one less sort.
The optimizer, although unable to automatically re-order operations, HAS taken advantage of the data ordering if it is as required. This is well worth taking advantage of if you have different sorting requirements in one statement. Try grouping the functions that require the same order together and save yourself a few extra sorts.
Posted by Steve Jones on 7 June 2011
Interesting, did not know this. A good little tip to keep in mind.
Posted by Dolson on 8 June 2011
good to know!
Posted by srikant maurya on 8 June 2011
I will keep in mind from next time while wtriting T-sql statment.
Thanks
Srikant
Posted by forestGreen on 8 June 2011
I did not know that it is not a well know fact about window functions. I found out this in 2008 when I was solving exercises at sql-ex.ru
Just a note. For better plan we should avoid not obligatory sorts:
select SalesOrderID,
CustomerID,
OrderDate,
RownAsc,
CustomerCount - RownAsc + 1 as RownDesc
from (
select *
, ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc
, COUNT(*) over (Partition By CustomerId) as CustomerCount
from Sales.SalesOrderHeader
) x
order by CustomerID,OrderDate
option(maxdop 1)
Posted by Nakul Vachhrajani on 8 June 2011
Thank-you! Learnt something new today!
Posted by richard.langlois on 8 June 2011
This tip is now part of my bag of good practices.
Posted by Fabiano Neves Amorim on 8 June 2011
Very Nice, good to know. Tks for the post.
Posted by Pei Zhu on 8 June 2011
Bear with me. I am not quite getting it. Why could it affect the plan? Because the clustered index was created with desc order?
Posted by Dave Ballantyne on 8 June 2011
Thanks all, just glad to 'pay it forward'
@Pei,
The point is that there are 3 points in the plan where an order is required. The optimizer is unable to swap its operations around to provide group together operation that require the same sort. So by swapping the RownAsc and RownDesc the optimizer has seen that the data is already in the required order for the order by clause and not resorted.
Dave
Posted by Pei Zhu on 9 June 2011
Dave,
Thank you for taking time to explaining it. I did not pay attention to the statement "Try grouping the functions that require the same order together". Now I got it. Thanks again.
Pei