CODEWITHSUNDEEP
sql-server
Jason Andrews
Jason Andrews

Reputation: 59

Added an Index to a field and it's still running slow

We have 10M records in a table in a SQL Server 2012 database and we want to retrieve the top 2000 records based on a condition.

Here's the SQL statement:

SELECT TOP 2000 * 
FROM Users 
WHERE LastName = 'Stokes' 
ORDER BY LastName

I have added a non clustered index to the column LastName and it takes 9secs to retrieve 2000 records. I tried creating an indexed view with an index, created on the same column, but to no avail, it takes about the same time. Is there anything else I can do, to improve on the performance?

Upvotes: 0

Views: 45

Answers (1)

James Z
James Z

Reputation: 12317

Using select * will cause key lookups for all the rows that match your criteria (=for each value of the clustered key, the database has to travel through the clustered index into the leaf level to find the rest of the values).

You can see that in the actual plan, and you can also check that the index you created is actually being used (=index seek for that index). If keylookup is what is the reason for the slowness, the select will go fast if you run just select LastName from ....

If there is actually just few columns you need from the table (or there's not that many columns in the table) you can add those columns in as included columns in your index and that should speed it up. Always specify what fields you need in the select instead of just using select *.

Upvotes: 2

Related Questions