Re: View vs Constantly Updated Table

On Dec 15, 11:25 am, r(dot)(dot)(dot)(at)iol(dot)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(dot)(dot)(dot)(at)iol(dot)ie
> Galway Cathedral Recitals:http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)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.