sql server (alternate): C10B (tswalton@gmail.com) writes:
[quoted text, click to view]

The DROP at the end is just cleanup. The script was meant to show how
you create an indexed view and how to use it.


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

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

not entirely sure why my browser posted my original message again!
sorry.

thanks guys for your help so far.
yes I have indexes on the columns

erland - I don't quite get what you are doing with creating and
dropping a table and a view?


Tim

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.

Thanks

Tim

[quoted text, click to view]


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

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

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?

Thanks

[quoted text, click to view]


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.



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

C10B (tswalton@gmail.com) writes:
[quoted text, click to view]

No. An indexed view is materialised, and this particular view has one
row per webpage with the count of visitors. An alternative would be have
a separate table with the counts, and update that table through a trigger.
But why roll our own when SQL Server can do the job for us?


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

[quoted text, click to view]

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




[quoted text, click to view]

Define slow?

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


[quoted text, click to view]

Yes, post a full DDL.


[quoted text, click to view]


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

C10B (tswalton@gmail.com) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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