Posted by Greg D. Moore \(Strider\) on 02/26/07 16:26

"C10B" <tswalton@gmail.com> wrote in message
news:1172502298.291068.60420@s48g2000cws.googlegroups.com...
>
>> If you want more help, I suggest you give us a complete DDL of your
>> database, some real example data and perhaps we can do better.
>
>
> The requirement is simply this...
>
> 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...
>

Yes, we understand the requirements. That doesn't eliminate the value of a
DDL and sample data.




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

Define slow?

(my former employer did queries similar to this in subsecond times.)


> 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.
>
> Any other tips?

Yes, post a full DDL.


>
> Thanks
>


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

[Back to original message]