Is this an efficient query?

Results 1 to 2 of 2

Thread: Is this an efficient query?

  1. #1
    Join Date
    Dec 1969

    Default Is this an efficient query?

    I have a SQL Server 7.0 table of bands. Everytime a specific band page is viewed on the corresponding website, the hits field for that band&#039;s particular record gets incremented.<BR><BR>At some point I&#039;d like someone to be able to view a band&#039;s particular ranking, based on those hits, sorta like how Amazon ranks a book by sales (ie. 47,089).<BR><BR>I quickly wrote a query that would do this. It works, but I&#039;m not sure if it&#039;s the most efficient way. Here&#039;s the SQL statement:<BR><BR>DECLARE @total int<BR>DECLARE @greater int<BR>--get total number of records<BR>SELECT @total = COUNT(*) FROM bandTable<BR>--find number of bands with more hits than particular band queried<BR>SELECT @greater = COUNT(*) FROM bandTable WHERE hits &#062; (SELECT hits FROM bandTable WHERE name = &#039;slayer&#039;)<BR>--add one to result above to obtain rank<BR>SELECT @greater + 1

  2. #2
    Join Date
    Dec 1969

    Default Looks pretty good... me. About the only thing I could see that would improve it would be to keep the count of all bands in an Application variable. After all, you will *know* when you add another band to the DB, so at the same time you bump the count in the App variable. You init the App variable by doing the COUNT(*) in the Application_onStart even in Global.asa.<BR><BR>But is that worth it? Not unless this is a pretty damned busy site!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts