Subject Re: [firebird-support] sum performance
Author Matthias Hanft
reynaldi81 schrieb:
> 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? 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? thanks

Hi, I run a similar application, and I don't have a BALANCE field,
and I even don't use SUM - I just do a SELECT for the rows which
I want to add for the balance, and then I add the amounts myself.

This happens because of historical reasons: There are only positive
amounts in the database, along with a "type" field which denotes the
sign (-1, 0, +1), and a currency field which causes applying a factor
for non-local currencies before addition.

And this goes _really_fast_ - you just have to create and use
appropriate indexes for the SELECT command. (I have more than
a million rows in that database!)

The advantage without a BALANCE field is that it can never become
wrong :-)