> 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.
