Posted by Erland Sommarskog on 02/26/07 22:23

C10B (tswalton@gmail.com) writes:
> I have a lot of pages and I want to record when each is visited.
> I then need to be able to answer questions like this...
>
> "Which was the most popular page in January 07?"
> "Which are the top 10 most viewed pages between 2 and 3 in the
> afternoon"
>
> with unlimited segmenting of the data really.
>
> So I thought I would record an entry in a table everytime a page is
> viewed, along with the date and time.
> Using some simple queries using "count(*)", a where clause and a group
> by clause I was answering all the questions.
> Trouble is the speed. Several million records (and growing rapidly)
> and count(*) gets slow.

You did not say anything about wanting to get the counts for a certain
day, so I interpreted your question that you wanted total counts of
the entire table. Whence my suggestion of an indexed view.

If you are not able to relate clearly what you want to do, then the
answers you get will be equally imprecise.

> Just while I was writing this I had an idea - I could move a finished
> month's data to another table, so each month has a table of data. It
> might still be a million records though, but at least it wont get
> worse and worse over time. This would make some results harder to get
> like "most viewed page of all time" but my monthy, hourly, weekly
> figures would be quicker.

There is no need for that. If you always want to run these count
queries for a certain period, make sure that there is a clustered
index on the datetime column in your table.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]