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

Question

Hey Everyone,

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

Answer 1

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

Answer 2

I think this could be a serious problem. Reads are 100% scalable, but writes require locking, so you're imposing a locking overhead implicitly on every read and thus serialising them. Do you have any idea what the rate of reads would be that you have to maintain? Have you looked at what auditing options are available as part of the RDBMS?

Answer 3

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

Answer 4

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.

Answer 5

I'd consider doing one of the following:

• Use a buffer within the application and batch write a set of views every time buffers gets full
• Using a local logging file and aggregate its results in a batch load once in a while
• Use an event system (Pub/Sub) to fire events from your application which will later be dealt with by another application (subscriber) asynchronously. Take a look at the following project