SQL storage slowness

[Stuart D. Gathman]

Chris Lyttle wrote:
> 
> I was wondering how the work on the sql backend functionality is
> progressing? I have attempted with 1.6.0 and 1.6.1 to use the sql
> functionality but had to resort to going back to the xml flat file
> format as it takes 5 mins to open a file that is 7MB in xml form from
> the database.

This is probably due to adding up the running balances.  This can be
done efficiently as follows:

Divide each fiscal year into periods (months or user defined).

Maintain a summary by acct,fiscal year,period.

Maintain starting balance records by acct,fiscal year.

To get the running total for any transaction in an account register:
  Get starting balance.
  Add period summaries for previous periods in fiscal year.
  Add previous transactions in period.

This reduces the work to adding up at most 1 periods worth of
transactions to get the running total in sync.

Any number of abstract summary levels could be defined (kind of like a
database index), but the above corresponds to the intuitive structure of
paper books.

-- 
Stuart D. Gathman <stuart@bmsi.com>
Business Management Systems Inc.  Phone: 703 591-0911 Fax: 703 591-6154
"Confutatis maledictis, flamis acribus addictis" - Mozart background
song for the Microsoft "Where do you want to go from here?" commercial.