Index Scan Behaviour When Using ROW_NUMBER() in a View

Question

Post reply

Index Scan Behaviour When Using ROW_NUMBER() in a View

schwizla

SSCrazy

Points: 2668
More actions
May 20, 2010 at 12:37 pm

#224807

Heres the scenario...
I have a large table with a PK Clustered index on and INT (ID) column
I create a View on this table which returns the columns from the table and also a derived rank column by using the ROW_NUMBER() OVER PARTITION ordered by the INT(ID) column
When I query the viewed and enter a fixed value for the ID column (example 45) it uses the PK and does an Index Seek which is very fast. However if I put this value in a temp table or normal table and then join this table to the view, the optimizer does an Index Scan. It does not seem to be clever enough to retrieve the value of 45 and the use this value to do a seek
It there a way I can force it to do a seek because for a table with millions of rows the query takes a very very long time 🙁 Is this normal behaviour and if so why?
You can reproduce this below.
-- table of data grouped by smallid, with a bigid
CREATE TABLE FOO (
smallid int,
bigid int,
data char(4),
primary key (smallid, bigid)
)
GO
-- populate it with some data
DECLARE @i int
SET @i = 1
WHILE (@i < 1000000) BEGIN
INSERT FOO VALUES (@i, @i%100, 'DATA')
SET @i = @i + 1
END
-- view involves a partitioned row number over the bigid
CREATE VIEW BAR AS
SELECT smallid, bigid, ROW_NUMBER() OVER (PARTITION BY smallid ORDER BY bigid) as _rank
FROM FOO
GO
-- just in case...
update statistics FOO
-- this does a clustered index seek (sweet)
select * from BAR where smallid = 45
-- this doesn't.....
create table #tmp (id int primary key)
insert #tmp values (45)
-- clustered index SCAN!!!!! WHY!?
select * from #tmp inner join BAR on #tmp.id = BAR.smallid
-- or try
select * from BAR inner join #tmp on BAR.smallid = #tmp.id

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

I agree. This seems strange. This could possibly be regarded as an optimizer bug.

Even this code gives an index scan:

DECLARE @v-2 INT=45

select * from BAR where smallid = @v-2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Interesting, I get nothing but Clustered Index scans for all three select statements.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

CirquedeSQLeil (5/20/2010)
Interesting, I get nothing but Clustered Index scans for all three select statements.

me too get clus index scan for all 3 statements

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Stefan_G SSCertifiable Points: 6609 More actions · Answer 4

I tested this on SQL 2008. I got exactly the same behavior as the OP.

Maybe there is a difference here between 2005 and 2008 ?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

This is a cross post from

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e433ad80-c1d7-4fa6-bdf4-c6e471ceeb91

Clear Sky SQL
My Blog[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Stefan_G (5/24/2010)
I tested this on SQL 2008. I got exactly the same behavior as the OP.
Maybe there is a difference here between 2005 and 2008 ?

I tested on 2008 and 2005 - same results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

schwizla SSCrazy Points: 2668 More actions · Answer 7

As Dave has said, a resolution has indeed been provided for this on the MSDN link in his post. The question\problem is one and the same.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Just read the msdn entries - and it is the same.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events