2.3.X/2.4 Database architecture

[Phil Longstaff]

I just had an interesting session with my company's database architect about how the gnucash db is built.  He also has a background in accounting apps.  My concern is that for reports, the process of selecting transactions for the required set of splits, then loading the transactions and their slots, and splits and their slots can take a long time.  In fact, in my home db (~25000 splits), it takes sqlite3 about 10 minutes to select the transactions for a balance sheet.

For reports, all that seems to be required is the value of an account on a certain date.  The current method to get this is to load the set of splits from the beginning of time through that date, then loop and add up the split values.  This should be unnecessary with a db engine.  However, as Derek says, gnc is not a db app.

Anyhow, he had a number of suggestions:
1) Add an account-balances table.  There used to be one in the old postgres backend.  The idea would be to have an entry per account per month with the value at the end (or beginning) of the month.  This would reduce the problem of finding the value at a certain date to loading the correct account-balances table value and adding in the value of all splits between that entry's date and the desired date.
2) Denormalize the transactions and splits tables into a single transactionsplits table.  Each row in the splits table would essentially gain the columns for the associated transaction.  Would make saving/updating transactions a bit messier, but would remove the need to join the two tables on queries.

I'm planning to release 2.3.2 either today or tomorrow (depending when I can find the time).  I'll then start to experiment with these two for 2.3.3.  For now, I've sped up queries by ignoring any terms which don't have to do with the account guid.  It turns out with sqlite3 (and probably mysql and postgresql as well) to be *much* faster to ask for all transactions with splits with account_guid = 123 than to ask for all transactions before a certain date with splits with account_guid = 123.

Phil