SQL Server Performance

Select top - performance issues

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by rlcoach, Mar 11, 2007.

  1. rlcoach New Member

    Hi,

    I am running into issues with "select top" in SQL Server 2005.

    I have a table without around 3.5 million records in and am doing a select top i.e.

    Select top 10 *
    from Products

    And it takes around 17 seconds to come back, which is crazy. I have looked at the execution plan and its because its doing a clustered index scan. Why does it not use a non-clustered index in this situation?

    If I change it to the following:-

    select top 10 *
    from Products
    order by Ref

    explicitly ordering by a non-clustered index then it returns instantaneously.

    The table currently has a clustered index on the primary key ProductID (which is an identity column). Is it because its the primary key? I could make another unique field primary clustered, but it isn't a sequential integer, its a 13 character varchar.

    Any thoughts?

    Andrew

  2. MohammedU New Member

    Try updating the statistics on this table and check the query I/O stats...

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. ranjitjain New Member

    Hi,
    You could even try adding non-existent value filter
    something like:
    Select top 10 * from Products
    WHERE ProductID>0
  4. mmarovic Active Member

    Is there any text or binary column in the table?

Share This Page