Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2003-06-24 : 21:58:58
|
| I am working on a website where users have profiles, the same sort of deal when you click on the users name on this site and look at the about page.What I want to do is record profile views, basically a hit counter. I was planning on creating a table as suchViewID(identity)ViewDate(SmallDateTime)ViewerID(int)On each profile view I want to insert a row. If it is a registered member I will insert their userID. This way the users can view their daily views, and there total views, as well as the possibility to see who is viewing them.The problem is, with 400,000 views per day this is sounding like its going to be a huge table. I could accumlate 12 million rows in just a month. I am not totally sure this feature is worth the performance hit. Each time a user looks at a profile it will have to count the number of rows in the table. I am coding this with asp.net and will object cache the data so it is not hit as hard, but this is still major overhead.Does anyone have any ideas / suggestions on how to go into this? Any experience in a similar situation? Speed is my ultimate concern as well as I dont want to have gigs of storage for this table. Thanks alot for any help!Mike123 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-24 : 22:08:48
|
| Mike,Obviously this is not critical data so perhaps you could just keep the aggregate.ViewerID(int) ViewDate(SmallDateTime) ViewCount(int) DEFAULT(0)You Key would be (ViewerID, ViewDate).Possible Rule...IF (ViewerID, ViewDate) NOT EXISTS Then INSERTELSE Update ViewCount+1Obviously less I/O intesive and should still give you all the functilonality...DavidM"SQL-3 is an abomination.." |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2003-06-25 : 03:30:01
|
| byrmol, thanks for the inputthis is a field that is going to be updated lots, so I am guessing it is still best to keep it in a separate table. Do you agree? I could put it in the main table that stores all the profile information but I think that would be slower since an update statement effectively deletes and inserts the row (as I understand it so far) and it would be faster to just update 3 columns than the 20 or so I have in my main table.this method doesnt allow for me to keep track of who is viewing profiles, but I guess that is not the end of the world considering the tradeoff ... however I don't really see a way to do this without the table size getting out of control either.thanks for the advice, any other comments gratefully welcomedcheersmike123 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-25 : 07:27:10
|
quote:
I could put it in the main table that stores all the profile information but I think that would be slower since an update statement effectively deletes and inserts the row (as I understand it so far) and it would be faster to just update 3 columns than the 20 or so I have in my main table.
That's really only true if you update a varying length column with a dramatically different-lengthed value, say from a null to a 100 character string. If you're updating a int column that is declare not null, it will update it in place, and no page splits will occur. FWIW the Snitz forum code keeps the aggregrate posting value in the members table, and it doesn't seem to suffer any problems. |
|
|
|
|
|
|
|
|
logging table