reynaldi81 wrote:
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? currently i stored the customer balance in a ARBALANCE field and update it each time the customer have transaction because i think that using sum will reduce performance if i have huge numbers of records. does anyone have any suggestions?
Ann W. Harrison answers:
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.