SQL Server Performance Forum – Threads Archive

Select top – performance issues

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

---

Try updating the statistics on this table and check the query I/O stats… MohammedU.
Moderator
SQL-Server-Performance.com

---

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

---

Is there any text or binary column in the table?

---

]]>