Is there an easy way to scale a 'views' or 'download' counter in a DB row?

I'm working on a little web project that needs to keeps to a NumViews counter for each object/row in a DB table. I'm concerning that all the updates to rows would start really degrade performance both the table grows in object count, and the site would grow in use.

I'm using .NET 3.5 with MSSQL 2K5 if it matters.

Any suggestions?

Thanks in advance!

--joel


Our current guestimations on DB usage come to a sustained ~66 reads a second.

I like the idea of splitting the counters into a separate table. Do you know of any articles or white pages supporting it, or is it one of things everyone just does? :-P

Also, while updating, does the DB lock the entire table or just that particular row? I would assume just a row, but wasn't sure.

Thanks,

--j

Answers


I'd suggest:

  1. Putting the row -> count data in another table (with fixed size columns) so that you're not continually writing to your main table just to maintain counts

  2. Abstracting access to that so that as the application grows you can replace the DB table with some more efficient structure


Consider using a "Views" event table, write-only, that stores each view with a timestamp. No locking problems, no sweat, full audit trail at the most granular level. Little tiny records, ~ 5MM records a day?

Someone will probably want it later, and it's simple to add it now and use it. If someone wants to do analysis/mining on it, this will be useful. At that volume, I can't imagine much else to use it for.


Need Your Help

close Jquery Modal popup Datalist button click

asp.net jquery-ui-dialog datalist itemcommand aspbutton

i am running into a problem that i have a jquery modal popup in my usercontrol that i show on a click of a button and user selects something from datalist and then i returned some value on the parent

Finding text in string, storing it on array and displaying it

javascript arrays for-loop indexof

I am trying to find a strings in a string content and pushing the found content letter by letter in an array and display all the found content in html. But it seems like something wrong with the ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.