SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

BUG - Use of ranking functions result in an inefficient query plan

In SQL2005 (9.00.4207.00), if you use a ranking function within a view or CTE, then an inefficient query plan can be produced.

First off in Adventure works create this index

Create Index idxLastName on Person.Contact(LastName)

and then consider these similar queries:

select ContactId,
LastName,
row_number() over(partition by LastName order by ContactId)
from [AdventureWorks].[Person].[Contact]
where LastName = 'Smith'
go
create view vwContacts
as
select
ContactId,
LastName,
rown = row_number() over(partition by LastName order by ContactId)
from [AdventureWorks].[Person].[Contact]
go
Select
* from vwContacts
where LastName = 'Smith'
go

You should now see two vastly different query plans


rankingqueryplan1


Notice in “Query 2” that Filter ?


image


Wow.. So it did calculate the entire query and then filter on the results.  At first i thought that i was being unfair, obviously there is potential for filtering on the ranking function, but when doing exactly the same on 2008 I get index seeks on both sides


rankingqueryplan2


CTE’s also suffer from the same problem, although they are easier to rewrite than a view to utilize a seek.

with cteLastNameRow(ContactId,LastName,Row_number)

as(
   
select ContactId,
          
LastName,
          
row_number() over(partition by LastName order by ContactId)
   
from [AdventureWorks].[Person].[Contact]
    )
select *
 
from cteLastNameRow
where LastName = 'Smith'
go

Another index scan.

So , be careful when using ranking functions and as ever,  investigate the query execution plan on EVERY query. I've raised a connect bug report here if you feel that it is something that Microsoft should invest some time in fixing.

Comments

SqlServerKudos said:

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# September 9, 2009 1:46 AM

Tony Rogerson's ramblings on SQL Server said:

This Thursday 26th is the London SQL User Group and also the Edinburgh UG; in London we have Tony Rogerson

# November 24, 2009 12:58 PM