Optimising select query to return the first 100 records (Adding an ORDER BY slows query)

Question

I am executing the following query:-

 select top 32 * from TweetEntity order by FavoriteCount desc, LastModifiedDateTime desc

This takes atleast 30seconds to execute. The table contains atleast 300,000 of records.

but when I execute the following query:

  select top 32 * from TweetEntity 

It takes less than a second to execute. I am clueless as- how to get my first query executed in less than one second. What should I be checking. Could some one point me in the correct direction.

Answer 1

When you introduce the ORDER BY, Sql needs to evaluate the 32 highest rows based on that order, rather than any 32 rows in the table. It is apparant that there is no suitable index for Sql to use to evaluate the query.

So TL;DR you'll need to add an index on TweetEntity(FavoriteCount desc, LastModifiedDateTime desc) to improve performance, e.g.:

CREATE NONCLUSTERED INDEX IX_TweetEntity_Favourite 
ON dbo.TweetEntity(FavoriteCount desc, LastModifiedDateTime desc);

If your query is the most important / common query on the table, you can also consider changing this to be clustered index.

Edit, re Can I create as many non-clustered index as I like on a table?

Arbitrarily, you can create up to 999 non clustered indexes per table.

However, there's a catch - each index added:

• consumes more disk space
• and makes it slower during write operations to the table (i.e. insert new / update / delete existing rows) since indexes must also be maintained
• if you have many similar indexes, Sql will also need to choose between the indexes when creating a query plan.

Welcome to the black art of indexing - there is no simple formula - each time you are thinking of adding a new index, you'll need to closely evaluate the benefit of each new index against existing ones. In many cases, you'll be able to tweak an existing index rather than adding new indexes each time.