optimization question

  • Thread starter Thread starter Howard
  • Start date Start date
H

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?
 
what type of index would you recommend? I ran the Database Engine Tuning
Advisor it didn't give me anything
 
add a nonclustered index in desc order



something like this:

CREATE NONCLUSTERED INDEX IX_MyTable ON dbo.MyTable (hits DESC)
 
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.
 
Back
Top