Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

marked as duplicate by Gaurav, Larry Coleman, Am1rr3zA, BenV, BrunoLM Jan 5 '11 at 18:06

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
search didn't show that question, feel free to close. Thanks @Gaurav –  eiefai Jan 5 '11 at 16:57

2 Answers 2

up vote 1 down vote accepted

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 denomalising 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, and so on).

share|improve this answer

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.