Index Scan Behaviour When Using ROW_NUMBER() in a View

  • 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

  • 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

  • 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

  • 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;-)

  • 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 ?

  • 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

  • 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.

  • 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

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

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