Posted by Greg D. Moore \(Strider\) on 02/25/07 21:59

"C10B" <tswalton@gmail.com> wrote in message
news:1172439270.641426.83160@a75g2000cwd.googlegroups.com...
> hi,
>
> I have a table with several million rows.
> Each row is simply the date and time a certain page was viewed.

First question, do you have an index on the table.

That should help.

>
> eg
> page1 1-1-00
> page2 2-1-00
> page1 16-1-00
> page1 17-1-00
> page2 19-1-00
>
> I now need to find the most popular pages so I do this...
>
> SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
> GROUP BY place ORDER BY COUNT(place) DESC
>
> ...which gives me the top 10 most viewed pages
>
> big problem - this is getting slower and slower as the table grows and
> grows.
>
> what should I do?
>
> is there an alternative?
>
> I think I need all the rows (rather than a simple incrementing
> counter) because I might want to say "what was the most popular page
> on a certain day or within a certain period"
>
> tia
>
> Tim
>



--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

[Back to original message]