Posted by Russ Rose on 02/27/07 01:48

Fastest returning query would be on a separate table that maintains the
count to the granularity you would require in your output (week, day, or
hour). A query that requires more than hourly granularity can still be done
on the source table.

For each new week|day|hour add a row for each page to be tracked. This can
be done dynamically during monitoring but it is better to do it ahead of
time.

Then put an insert trigger on your million row table that will increment the
count on the proper row of the table.

An alternative would be a temp table that contains a subset copy of the
source data for the time range most likely to queried. For example a table
that contains the last 31 days of data, the 32nd day of data
deleted/archived every night.


"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.
>
> 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
>

[Back to original message]