I'm trying to decide between updating a banks current balance whenever any type of transaction is made (with a guarantee I'll miss at least one place) or generating a view which simply calculates it from all the transactions eg

SELECT
_ID,
_Name,
(SELECT SUM(_Income) FROM Transactions T WHERE T._fkBanks = Banks._ID) AS _Income,
(SELECT SUM(_Expenditure) FROM Transactions T WHERE T._fkBanks = Banks._ID) AS _Expenditure,
_OpeningBalance +(SELECT SUM(_Income) - SUM(_Expenditure) FROM Transactions T WHERE T._fkBanks = Banks._ID) AS CurrentBalance
FROM Banks

Each bit of the query is optimised but how efficient is the query/view as a whole? Also since I don't use views is it refreshed each time its accessed or is it like a static query?

Roy Lambert

Roy,

<< Each bit of the query is optimised but how efficient is the query/view as a whole? Also since I don't use views is it refreshed each time its accessed or is it like a static query? >>

Views are simply as fast, or as efficient, as the equivalent query.  An updateable view is simply a query that returns a sensitive result set, and a non-updateable view is the same as an insensitive query.

As far as refreshing, EDB will only refresh a non-updateable view when you open it and when you manually refresh it.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


Thanks - in that case plan B here I come.

Roy Lambert