What is the best approach to calculate a formula which changes value each day?

Question

I use the following columns stored in a SQL table called tb_player:

Date of Birth (Date), Times Played (Integer), Versions (Integer)

to calculate a "playvalue" (integer) in the following formula:

playvalue = (Today - Date of Birth) * Times Played * Versions

I display upto 100 of these records with the associataed playvalue on a webpage at any time.

My question is, what is the most efficient way of calculating this playvalue given it will change only once a day, due to the (today-date of birth) changing? The other values (times played & versions) remain the same.

Is there a better way than calculating this on the fly each time for the 100 records? If so, is it more efficient to do the calculation in a stored proc or in VB.NET/C#?

Answer 1

In a property/method on the object, in C#/VB.NET (your .NET code).

The time to execute a simple property like this is nothing compared to the time to call out-of-process to a database (to fetch the rows in the first place), or the transport time of a web-page; you'll never notice it if just using it for UI display. Plus it is on your easily-scaled-out hardware (the app server), and doesn't involve a huge update daily, and is only executed for rows that are actually displayed, and only if you actually query this property/method.

Answer 2

Are you finding that this is actually causing a performance problem? I don't imagine it would be very bad, since the calculation is pretty straightforward math.

However, if you are actually concerned about it, my approach would be to basically set up a "playvalue cache" column in the tb_player table. This column will store the calculated "playvalue" for each player for the current day. Set up a cronjob or scheduled task to run at midnight every day and update this column with the new day's value.

Then everything else can simply select this column instead of doing the calculation, and you only have to do the calculation once a day.