optimization question

[Howard]

I have a table with a large amount of rows > 100 000 with
ID|NAME|HITScolumns
ID is the primary key
NAME is nvarchar
HITS is int

I run this query, it takes less than a second
select top 40 * from table 1 order by id desc

but when i run this it take more than 10 seconds
select top 40 * from table 1 order by hits desc


hits column keeps track of time the page has been loaded

what can i do to make the second query run just as fast as the first one?

 

[Andrew J. Kelly]

Do you have an index on HIts? If not then you might want to add one.

 

[Howard]

what type of index would you recommend? I ran the Database Engine Tuning
Advisor it didn't give me anything

 

[retlaw]

add a nonclustered index in desc order



something like this:

CREATE NONCLUSTERED INDEX IX_MyTable ON dbo.MyTable (hits DESC)

 

[Andrew J. Kelly]

You really only have two choices here. A clustered or a Nonclustered index.
I assume you already have a clustered index on ID. So try adding a
non-clustered index on Hits and see if it helps.