On November 20, 2008 06:38:14 pm Phil Longstaff wrote: > On November 16, 2008 02:01:05 pm Derek Atkins wrote: > > Phil Longstaff <[EMAIL PROTECTED]> writes: > > >> Wait, the ENTIRE contents are read in? Historically only "necessary > > >> data" was read in. That would be the Accounts and Commodities from > > >> the main CoA. The transactions were all loaded on demand. > > > > > > Yes. I wanted to only read "necessary data". However, my (admittedly > > > incomplete) knowledge of the engine led me to the conclusion that parts > > > of th engine assume that all data is present. I couldn't get the > > > account tree to show correct values, for example, unless all splits for > > > an account were present. I ended up just loading the whole database > > > into memory. > > > > There should not be any dependence on reading in all the data from the > > DB. The old PG Backend certainly did not, and it worked fine. I think it > > got around it by having checkpoints for things like running account > > balances, so you only need to load "current" transactions, not all of > > them. > > > > If you're loading all data all the time then the only benefit to the DB > > backend over the XML backend is save-on-commit. > > I was just looking at the pg backend code again. In single user mode, it > *did* read all of the transaction data at startup time.
OK. I have modified code (not committed yet) which doesn't read tx/splits at startup. At startup, it executes: SELECT account_guid, reconcile_state, sum(quantity_num) AS quantity_num, quantity_denom FROM splits GROUP BY account_guid, reconcile_state, quantity_denom This gives me 1 row per guid/reconcile_state/quantity_denom triplet. From these, I set the start balance, start cleared balance and start reconciled balance for each account. When the backend receives a query to load splits for an account, it uses this algorithm: 1: save start and end balance, cleared balance and reconciled balance for each account 2: stop qof events 3: load all transactions which contains splits for the target account. This loads all splits for those transactions, even those in other accounts. 4: for each account, get new end balances. If end balance doesn't match end balance from step 1, calculate old_start_balance-(new_end_balance- old_end_balance) and set this as the new start balance. 5: resume qof events IOW, if the end balance for an account increases by X because of newly loaded splits, step 4 decreases the start balance by X to make the end balance match what is should be. Eventually, once all tx/splits are loaded, all start balances are 0. Each account that is the target of a query for all splits is marked so that it's splits are never reloaded. Done as a performance improvement but would need to be revisited for a multi-user back end. I need a bit more testing, but once I'm happy with this, I'll commit it. It does lead to a delay when opening an account with a lot of splits. I may look at adding a progress bar or other indication that the app is busy. Phil _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
