SQL Woes under 1.6

[Michael T. Garrison Stuber]

> Hmm. Well, the sql backend is supposed to handle KVP frames. Did you
> discover the name of this kvp frame by reading the source code, or by
> seeing it in the  db?  Is it being stored in the db, but just not being
> fetched correctly?

Digging through source.  Actually, I first found it by saving my data as a 
file, changing the setting, saving it again, and doing a diff.  From there 
I went digging throught the Account code.  <FLAMEBAIT> To be honest I find 
the KVP stuff in the database mind-numbing.  I realize why it's there, and 
on some level it's a really neato solution, but working on a database 
application professionally, I find it frustrating that there isn't just a 
table for accounts, with a column for the price-source.  I mean, what's the 
point of having a relational database underneath GNUCash, if you can't 
really use it for anything?  (I know, it's for multiuser, and it's not 
there for people to use for integration or direct access, and if I just 
understood it better . . . )</FLAMEBAIT>  I have yet to figure out where 
the keys get stored in the database.  It looks to me like they don't, they 
are just connected through a GUID and an iPATH to the value for the key. 
Of course, I may be thoroughly confused.

> There was a major performance performance fix in 1.6.1, you're sure you
> are  running 1.6.1?

Quite.  I compile from source, synced to the head of the 1.6 branch.

> I presume that the response time is bad for just one particular
> operation,  (e.g. opening a register window or running a report) and  not
> overall?  If so, then could you run gnucash --loglevel 4 and mail me the
> output immediately leading up to the point where it hangs?

Opening, closing and saving.  Normal operations are quite zippy.  Bringing 
the thing up takes forever, saving takes 3 minutes (I'm running in 
single-user update mode) you'd think it would be instant -- calling a 
commit or something.   Exiting can take 5 minutes.  I've watched it with 
top.  It appears that postmaster is taking it all of my CPU, and that 
GNUCash is doing something synchronous where it waits for postmasters to 
return.  I bumped up the number of buffers for postgres, but it didn't do 
any good.  I've tried running in multiuser mode, but every time I do it 
hoses the something related to dividends, and my net worth goes up to $8 
million.  (Wouldn't that be nice)

> Could you also mention what it is about your dataset that you think is
> unique?  Do you have thousands of accounts?  Maybe one account with
> thousands of transactions in it?

I don't think it is unique, but maybe it is.  Most of my accounts are two 
levels deep.  I have 15 odd top level accounts, with 5 to 25 sub accounts 
under each.  Some of these have another 2 or 3 accounts below that.  In 
total I guess there are around 150 accounts.  As for transactions, I have 
somewhere between 10 and 2000 per account.  It varies depending on type. 
I'd love to clean out some old transactions, but that's not easily 
supported right now, and I haven't had the gumption to write it.

> Another time-killer might be that you set up your 'home window' to have
> a dozen reports in it, with each report showing several years worth of
> data in it ... then probably no one operation is slow, but the startup
> time is pitiful ...

I do have a few open reports, but I know how much time they take.  My big 
issue is the save and the exit.  I guess my understanding the reports is 
that it's the options that are saved, and they rerun at startup.  To that 
end I didn't figure they would impact savings or exiting.  BTW, is there a 
way to save report options without leaving the report open forever?  It 
would be really nice for users to be able to define some standard customize 
reports and recall later without leaving them open forever.

Thanks for the response!  I really appreciate it.