Using cursors and Query Optimization

I am currently tracking sessions/hits on a few of my sites. One of the things I want to know is what are the most popular search terms that people use to find my sites. While writing a stored procedure to get my list of popular search terms I decided to see if a single query would be faster to count the number of searches for a particular term or if using a cursor would be faster. I've heard that cursors are "bad" but I use them often so I wanted to put it to the test. Here is my query:



declare @Date1 varchar(32), @Date2 varchar(32)

select @Date1 = '1/1/2000'

select @Date2 = '1/1/2009'



declare @d1 datetime, @d2 datetime



select @d1 = convert(datetime, @Date1 + ' 12:00:00 AM')

select @d2 = convert(datetime, @Date2 + ' 11:59:59 PM')



create table #temp (SearchQuery varchar(512), Hits int)





insert into #temp (SearchQuery, Hits)

select distinct Hit.SearchQuery, (select count(*) from Hit as Hit2 where Hit2.SearchQuery like Hit.SearchQuery) as Hits from Hit where SearchQuery NOT LIKE '' AND HitDate Between @d1 AND @d2



/*

DECLARE @SearchQuery varchar(512), @Hits int

DECLARE myCursor CURSOR FOR

select distinct SearchQuery from Hit where SearchQuery NOT LIKE '' and HitDate Between @d1 AND @d2

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SearchQuery

WHILE @@FETCH_STATUS = 0 BEGIN

    select @Hits=COUNT(*) FROM Hit where SearchQuery=@SearchQuery

    insert into #temp (SearchQuery, Hits) values (@SearchQuery, @Hits)

    FETCH NEXT FROM myCursor INTO @SearchQuery

END

CLOSE myCursor

DEALLOCATE myCursor

*/



select * from #temp order by Hits desc



drop table #temp





At first you see that I am running the ORANGE query which has a subquery doing the counting. It took 59~60 seconds to run this on all the data I have.



Next up, I commented out the orange query and ran the GREEN query. It took 47 seconds to run! And people say that cursors are bad? BS! The last thing I did was switch the order of my WHERE clause:



SearchQuery NOT LIKE '' and HitDate Between @d1 AND @d2

to

HitDate Between @d1 AND @d2 and SearchQuery NOT LIKE ''



Which brought the run time down to 40 seconds! I also tried this on the orange query but it still took 59 seconds to run.



Conclusion

Cursors are not bad! You just have to know that in some cases they can work slower or faster and you should test each method to see what is best. Also remember that the order of your WHERE clause can speed your query up as well. In my case searching a date field was much faster than searching text.



What other optimization tips do you have?