Subject Re: [firebird-support] sum performance
Author Ann W. Harrison
reynaldi81 wrote:
> hi, i have this table to record all customer transactions from sales
> to payments. is it better to update customer balance each time
> transaction happens in a field or simply using sum?

It's a trade-off. If you maintain a constant balance, you have a
"hot" record that many transactions need to update. That leads to
conflicts (aka deadlocks in MVCC) which requires redoing the
failed transaction and hurts performance. On the other hand, sum
requires reading all the records to be summed, which also has
performance problems.

There are ways around the multiple update situation ... they've
been discussed here many times before ... but they involve more
complex coding.

If you need to get the sum frequently and the values change rarely,
store it. If values change frequently and you need the sum rarely,
compute it.

Good luck,