Storing calculation results in database, or doing them on the fly?

[galvin]

This might be a loaded question, but if you have an application where a lot of calculations are done (simple calculations, but a lot of them), is it better to store calculation results in MySQL tables, or is it better to do the calculations on the fly each time a page loads?

 

For example, if you have an NCAA Bracket Pool Application that displays TOTAL POINTS for every participant in the pool (and TOTAL POINTS changes for each participant pretty much daily), is it better to run through the calculations for each user every time the "Standings" page is loaded, or should the calculation results be inputted into a MySQL table each time TOTAL POINTS change (i.e. each time a round of the tourney ends).

 

With the latter, the Standings page would just pull the TOTAL POINTS from a database for each user and easily display the standings (i.e. faster than if they calculations were being done on the fly for every participant as the page loaded), but of course updating the TOTAL POINTS in the mysql table every time the TOTAL POINTS change (which is a lot) would be lots of work.

 

Hope this question makes sense 

[ignace]

I am unfamiliar with NCAA but you would store all points a player has won during a round, at the end of the game you would sum all sub totals to a total for ranking.

 

round #1:

player_id, points

3, 5

..

 

round #2:

player_id, points

3, 2

...

 

SELECT sum(points) AS total_points FROM table GROUP BY player_id. You could store the total points in a separate rankings table so the calculation only has to be done once (the points in a rankings table should never change).

[fenway]

What you're talking about is basically data warehousing -- and yes, you can probably create summary tables and then just handle subsets of changing data.  Also, caching makes most of this go away.

[galvin]

Fenway,  I am going to read up on data warehousing since I hadn't heard the term until you said it   If you know any good resources to learn more about that (or on caching, since I'm unfamiliar with that as well  :-\), please share.

 

Just to make sure, it sounds like you're saying the better bet is to store the calculation results in a "summary" table (rather than do the calcs on the fly every time), right?

[fenway]

I'm suggesting you pre-compute the past, and then just combine the result sets.