Is it better to store calculated values or recalculate them on request? [duplicate]

Question

Possible Duplicate:
Storing vs calculating aggregate values

At an old job, we had this database, it wasn't normalized rigth, some tables wheren't used anymore, it was patch after patch. In conclusion, it was a mess.

Once, while I was trying to fix it I saw that some fields where calculated on the fly and this made the db very slow performing simple select querys. Since then, I don't calculate any field anymore, I just store the data as is.

Is this recomendend? when is better to store data than calculate it?

Thanks guys.

Answer 1

It depends.

Storing derived values can speed up processing of later read requests as the values do not need to be computed again.

There is one key problem with denormalising your data in this way though: you have to ensure that it is not possible for the derived value to become out-of-date undetected. If your data is not in a sufficiently normal form inconsistencies can creep in and cause trouble. This means that everywhere that updates the data the value is derived from must also refresh the derived value or somehow mark it as needing to be updated before being relied upon. Updating the derived values via triggers may mitigate having to add this logic to many places in your code, but be aware that triggers have their own set of potential gotchas (unexpected locking issues, code in the business logic layer not expecting values that it doesn't explicitly reference to update, triggers can be disabled, and so on). Another solution is to discourage direct access to the tables at all and have all updates via an API based on stored procedures - those procedures can know to recompute the calculated values as needed and obviously can't be disabled.

Answer 2

Generally, it's faster to store the data rather than calculate on the fly. If you are storing tens of thousands of variables, however, this may not hold true.