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.
Thanks in advance!
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.
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
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.