Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
How effective is this as a view? |
Sat, Feb 9 2019 8:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Feb 11 2019 12:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 12 2019 2:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Thanks - in that case plan B here I come. Roy Lambert |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |