optimization question

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?Do you have an index on HIts? If not then you might want to add one.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>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
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
> Do you have an index on HIts? If not then you might want to add one.
> --
> Andrew J. Kelly SQL MVP
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>|||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.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:e$EfRUSUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> what type of index would you recommend? I ran the Database Engine Tuning
> Advisor it didn't give me anything
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>