Обсуждение: View vs Constantly Updated Table

if i have a "column" that is a calculation, say a bank balance -> sum
of all the debits and credits...is it more efficient to make a view
that executes the underlying calc query doing the math, or to create a
table that has a column called balance that is updated for each
transaction?

so in the end "select balance from view" or "select balance from
table" ?

What are the pros cons ?

Thanks

Ketema J. Harris
www.ketema.net
ketema@ketema.net
ketemaj on iChat

> if i have a "column" that is a calculation, say a bank balance -> sum
> of all the debits and credits...is it more efficient to make a view
> that executes the underlying calc query doing the math, or to create a
> table that has a column called balance that is updated for each
> transaction?
>
> so in the end "select balance from view" or "select balance from table"
> ?
>
> What are the pros cons ?
>

How often are you using the "bank balance" value?  If you're updating it
for every transaction, you would be doing a lot of work computing values
that may rarely if ever get used.  That's an argument for the "view"
route, since the computation only happens when necessary.

The opposite argument, is how long does the computation take, and how
quickly do you need it? The pre-computed value would obviously be much
faster than waiting for it to be computed on the fly.

Other things to keep in mind... you might want to make the balance
calculation a separate function rather than building it into the table,
unless it's used on *every* query.  Also, if you would be taking any
action with the value returned by the balance calculation, remember to
lock any tables necessary to ensure the balance doesn't change between
the time you compute it and the time you act on it.

On 15/12/2008 16:14, Ketema Harris wrote:
> if i have a "column" that is a calculation, say a bank balance -> sum of
> all the debits and credits...is it more efficient to make a view that
> executes the underlying calc query doing the math, or to create a table
> that has a column called balance that is updated for each transaction?
>
> so in the end "select balance from view" or "select balance from table" ?

It would depend on how much calculation is involved in calculating the
balance..... If you had to query tens of millions of rows to get the
balance, I'd imagine you'd do better to have a trigger updating the
balance every time a row is inserted into the account ledger table.

If there's only a small number of rows to be queried, then it's easier
and probably more robust to do the calculation in a view or a function.
I've done this with ledger containing about 500 rows with no noticeable
delay (on my laptop).

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Dec 15, 11:25 am, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 15/12/2008 16:14, Ketema Harris wrote:
>
> > if i have a "column" that is a calculation, say a bank balance -> sum of
> > all the debits and credits...is it more efficient to make a view that
> > executes the underlying calc query doing the math, or to create a table
> > that has a column called balance that is updated for each transaction?
>
> > so in the end "select balance from view" or "select balance from table" ?
>
> It would depend on how much calculation is involved in calculating the
> balance..... If you had to query tens of millions of rows to get the
> balance, I'd imagine you'd do better to have a trigger updating the
> balance every time a row is inserted into the account ledger table.
>
> If there's only a small number of rows to be queried, then it's easier
> and probably more robust to do the calculation in a view or a function.
> I've done this with ledger containing about 500 rows with no noticeable
> delay (on my laptop).
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals:http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

OK.  I will go with the updated table for now, as I am expecting
thousands of records generated per day.

 > How often are you using the "bank balance" value?
I have no data on this as of yet, but it obviously needs to be correct
for when the user looks at it.

 > The opposite argument, is how long does the computation take?
  The computation is simple, however the amount of data that goes into
it grows as there are more and more
   transactions added.  This is why I was thinking of storing the
balance in a table sort of as a cache
   to avoid spinning through all pas transaction to get the current
balance.

 > and how quickly do you need it?
   as fast as possible of course :)

 > Also, if you would be taking any
> action with the value returned by the balance calculation, remember to
> lock any tables necessary to ensure the balance doesn't change between
> the time you compute it and the time you act on it.

I'll keep this in mind.  never done a lot of manual locking before,
but I can see where this is going to be needed.  Its possible to take
row locks and not complete table locks correct? (manual chapter 13.3)
I'm thinking if I use a single table to hold all users cached balances
then I would not want to lock the entire table just to retrieve and
act on one users balance.

thanks for the input

On Dec 15, 2008, at 11:27 AM, Adam Rich wrote:

Truncated...

It is generally better to save the balance. The general rule in
accounting systems design is "what can be printed, should be
explicitly on disk". for an invoice:

value before tax, tax percentage, value after tax, total before tax,
total after tax, etc, should all be saved explicitly.

An account should have a balance. Every operation should have balance
before operation, value, balance after operation. You should never
update an operation.

This way when business rules change all previous documents are stored
in consistent state.

Greetings
Marcin Mańk