Optimizing the select query to return the first 100 records (adding an ORDER BY query slows the query)

Question

I execute the following query: -

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

It takes at least 30 seconds to complete. The table contains no more than 300,000 entries.

but when executing the following query:

  select top 32 * from TweetEntity 

It takes less than a second to complete. I do not know how to get my first request completed in less than one second. What should I check. Can someone point me in the right direction.

Answer 1

When you enter ORDER BY , Sql should evaluate the 32 highest rows based on this order, not the 32 rows in the table. It is apparant that there is no suitable index for Sql to evaluate the query.

So, TL;DR you need to add an index on TweetEntity(FavoriteCount desc, LastModifiedDateTime desc) to improve performance, for example:

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

If your query is the most important / general query in the table, you can also consider changing this clustered index.

Edit, re Can I create as many non-clustered indexes as I like in the table?

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

However, there is a catch - every index added:

• consumes more disk space
• and makes it slower during writes to the table (i.e., inserts new / update / delete existing rows), since indexes must also be supported
• If you have many similar indexes, Sql will also have to choose between the indexes when creating the query plan.

Welcome to the black art of indexing - there is no simple formula - every time you think about adding a new index, you will need the advantages of each new index over the existing ones. In many cases, you can customize an existing index, rather than adding new indexes each time.

Answer 2

You need to create an index for the FavoriteCount and LastModifiedDateTime fields to optimize this particular query. You may need the following pages:

https://msdn.microsoft.com/en-us/library/ff650692.aspx

https://technet.microsoft.com/en-us/library/cc917626.aspx#ECAA