Home Dashboard Directory Help
Search

Unnecessary Sort by Fabiano Neves Amorim


Status: 

Closed
 as By Design Help for as By Design


15
0
Sign in
to vote
Type: Bug
ID: 679342
Opened: 7/13/2011 2:08:32 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

If query optimizer re-arrange the order of execution calculation of the windows, the second sort is becomes unnecessary because it is already sorted by Nome_Dep.

Query Optimizer is creating a plan that is first executing the AVG and then execution the RANK, this leads in a unnecessary double sort by Nome_Dep.
Details
Sign in to post a comment.
Posted by Hugo Kornelis on 4/23/2012 at 12:50 AM
And here is another repro that shows this bug still occurs on SQL Server 2012:

USE AdventureWorks2012;
go
-- Three sorts
SELECT TerritoryID,
         Name,
         SalesYTD,
         SalesLastYear,
         RANK() OVER (ORDER BY SalesYTD DESC) AS Rank1,
         RANK() OVER (ORDER BY SalesLastYear DESC) AS Rank2
FROM     Sales.SalesTerritory
ORDER BY SalesYTD DESC;

-- Two sorts
SELECT TerritoryID,
         Name,
         SalesYTD,
         SalesLastYear,
         RANK() OVER (ORDER BY SalesLastYear DESC) AS Rank2,
         RANK() OVER (ORDER BY SalesYTD DESC) AS Rank1
FROM     Sales.SalesTerritory
ORDER BY SalesYTD DESC;
go


I can't agree with the comment by Andrew Richardson. As far as I know, there is no official documentation anywhere that I can use to predict the order of sorts in the execution plan if a query uses multiple OVER clauses. This not only means that Andrew encourages me to design by trial and error, it also means that I would have to rely on undocumented behaviour - which, as we all know, is subjec to change without notice. So even if I do rewrite my queries to minimize the number of sort steps, I might get all those superfluous sort steps back tomorrow.

PS: Please don't take the above comment as a request to document the current behavious, as that would also force the optimizer team to maintain it in the future for backwards compatibility. Please do take it as a request to reopen this bug, acknowledge that this is a deficiency in the current optimizer behaviour, and put it on your long list of suggestions for future query optimizer improvements.

I get that some things that sound simple can be hard to build in the query optimizer. But I also think that a LOT of people are currently suffering suboptimal performance because of this deficiency without even knowing it. Fixing this would benefit a lot of people.
Posted by Fabiano Neves Amorim on 7/14/2011 at 1:26 PM
My collegue at SolidQ just point me out to a BlogPost talking about a similar problem... And this one was not "fixed" on Denali...

use AdventureWorks2008R2
-- Three sorts
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)
GO
-- Two sorts
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)

More details here http://sqlblogcasts.com/blogs/sqlandthelike/archive/2011/06/06/column-order-can-matter.aspx

I've to agree with Paul White... QO has much more intelligence to consider different ordering than developers. I know you had changed a lot of things related to windowing on Denali, looks like now it's the best time to make it right.

Thanks
Posted by Paul White NZ on 7/14/2011 at 12:32 PM
Hi Andrew,

" It would be fairly complicated for us to take a list of different OVER clauses and try to find the reshuffling that minimize the number of sorts. Given that it is fairly easy for the person writing the query to do this tuning themselves, we don't intend to invest our time into making this better. So I'm afraid I'm going to have to fall back on the principle of "If writing your query a particular way makes it run faster, then write your query that way."

This is a shame. The optimizer is in a much better position to consider 'interesting orders' than the query-writer in most cases. Most SQL people do not even know this limitation exists, much less how to work around it. Moreover, if I write my query in a particular way to optimize sorts today, what guarantee do I have that won't break with the next build of SQL Server?

Unnecessary sorts are a prime cause of poor-performing queries; I think it would be a big win if QO could be enhanced to consider this new type of interesting order problem.
Posted by Fabiano Neves Amorim on 7/14/2011 at 10:26 AM
I just tested on CTP3 of Denali and yes, it shows the good plan :-) with just one Sort.

Anyway, I'm a pessimist, but the good news here, was good enough for me.

Thanks for your time.
Posted by Microsoft on 7/14/2011 at 10:16 AM
Thank you for your feedback. I have a couple of comments.

First the bad news: It would be fairly complicated for us to take a list of different OVER clauses and try to find the reshuffling that minimize the number of sorts. Given that it is fairly easy for the person writing the query to do this tuning themselves, we don't intend to invest our time into making this better. So I'm afraid I'm going to have to fall back on the principle of "If writing your query a particular way makes it run faster, then write your query that way."

Now the good news: In the next release we are extending the OVER clause to allow genuine windows. In the process of enabling the new functionality, we made major changes to the existing code. As a result, the repro you provide now uses only one sort, regardless of the order of the SELECT list. We still don't guarantee, however, that we will always avoid redundant sorts.

Thank you again.

Andrew Richardson
Developer, SQL Server Query Optimizer.
Posted by Fabiano Neves Amorim on 7/14/2011 at 10:06 AM
What, closed with the status "by project"? I didn't understood? It's that right ?

Thanks
Fabiano Amorim
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Plans, Bug Unnecessary Sort.zip 7/13/2011 6 KB
Bug Unnecessary Sort.sql 7/13/2011 1 KB