Re: View vs Constantly Updated Table

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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------