SQL Server Performance

Index Scan Behaviour When Using ROW_NUMBER() in a View

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by siz, May 20, 2010.

  1. siz New Member

    Here's 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.Thanks in advance

    -- 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
  2. FrankKalis Moderator

    Hi, is this question still open?
  3. Madhivanan Moderator

    In order to have a SEEK, you need to use indexed column in the WHERE clause rather than joining it with other table
  4. satya Moderator

    Welcome to the forums.
    What is the service pack level on SQL 2008 instance?Also what kind of problem you are having by running index scan,
    it is very clear that there is a very big difference between the query costs when the index is created on the table. In fact, there is a very simple explanation of this behavior in the white paper Improving Performance with SQL Server 2008 Indexed Views.

    The reason for this behavior is that the query optimizer works when it checks for the index on view and finds that the execution plan of the query is similar to that of the view.

Share This Page