User Stats: "interative calculation" or bulk calculation + caching

Question

I have a project that calculates a number of 'statistics' about a users performance and then shows it to them. All these statistics ultimately come from a large table of 'interactions' that record the users interaction with the site. At the moment, all these statistics are calculated by looking at this data. We make extensive use of persistent caching to keep this things going fast.

We're considering moving to an 'iterative design' where statistic values are stored in the db, and upon logging every interaction, we update the values depending on what that interactions contribution to each score is, so we're essentially iteratively updating the values. (right now we just dirty the cache).

I see some trouble with the iterative design because it means we have these redundant, potentially out of sync information stored in our database, it makes adding new statistics difficult, and means more work on every interaction log. The benefits though are that it simplifies statistic lookups to single db hit!

Something in this iterative design raises alarms bells for me, but I can't deny the potential time saving benefits. Should I obey this gut feeling, or go ahead and do it?

Answer 1

When I'm doing database design, I try to avoid storing redundant data whenever possible. (This is, after all, the object of database normalization). Calculated columns and views are OK - these are managed and updated automatically by SQL server. Personally, I would lean towards other avenues before using the DB for caching (Is the SQL query really the part that needs performance improvement? Can I simplify things in the application by using a SQL view? etc.)

When you say manipulating the data, what operation are you performing that's so expensive? Do you mean insert/update/deletes? If your usage of the statistical data is write-intensive, you might consider removing indexes to speed up data changes.

Answer 2

Would triggers help, in that you can then do the calculations whenever new data comes in, which leads to not having stale data.

This would only be helpful if the read is much higher than the write though. If I am doing 2 writes for each read then this would be a bad design.

Some more details about what you are doing would be helpful

Answer 3

Calculating on an insert-basis is certainly the way to go, IMHO.

To cover the problems of, say, not being able to generate new stats immediately (because you don't have the calculated data), you can either:

• Run a bulk report on the new stat offline

or

• Calculate it on the fly and merge with the cache

Depending on your caching model, the stats may be out of sync, or they may not be. If it's with triggers, it happens immediately (on insert into tblFoo update tblFooStats); but you can retrieve this as required.

I would think the only real risk is as-mentioned: not being able to add new stats/calculations immediately. If you cover this, life should be quite nice.