3 ms·
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
by Ididntdothis 7y ago
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
- luismedel 7y agoI'm not a db-engine expert, but if I'd have to write this functionality myself, I'd perform the computation only when any of the involved columns change, not on every read. EDIT: sorry, I read your comment on the opposite way. I see we are saying the exact same thing :)
- redact207 7y agoWouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
- filleduchaos 7y agoAnd if another consumer of the DB doesn't do the calculation or, even worse, does the wrong calculation?
- baq 7y agowhat if you have 50 apps talking to the same DB?
- yourad_io 7y agoSo long as everyone, everywhere, always remembers to calculate it in the same way for all inserts and updates. This seems a lot more robust to me.
- GordonS 7y agoYou can use abstractions that push such code out of the developers concern, into the "code infrastructure" layer. But there are also plenty times where I'd rather compute at the database side; there are no silver bullets.
- magicalhippo 7y agoAnd then someone at support comes along and updates the data via SQL and forgets to update the derivative field because he got woken up at 2am for an emergency. Using a computed column ensures the data is consistent.
- benjohnson 7y agoYes, but increasingly for us we tend to have several "apps" accessing our data: The mobile, desktop and API 'apps' are all different code bases for us.
- FroshKiller 7y agoIf not a performance benefit, at least minuscule energy savings.