GDA interim benchmarking synopsis

Mark Johnson mrj001 at shaw.ca
Fri Feb 15 15:37:34 EST 2008


Derek Atkins wrote:
> Mark Johnson <mrj001 at shaw.ca> writes:
>
>   
>>> I'm really beginning to wonder if we need to write special SQL
>>> based on the database we're using in order to tune the performance
>>> because of issues like this?  Yes, I know that that sort of defeats
>>> the purpose of using a library like GDA, but if GDA can't form
>>> an optimal query then it might behoove us to do the right thing
>>> rather than hope they do.
>>>   
>>>       
>> I hope not, but we are talking not about minor differences in
>> performance, but orders of magnitude.  I was absolutely floored at
>> MySql's performance with sub-queries.  I do have a couple of things to
>> try with it, though:
>> 1. creation of a secondary index on slots.obj_guid
>> 2. There is a section of the MySql manual on optimizing this stuff.
>> Maybe it has something helpful.
>>     
>
> Exactly.  If it were just a minor difference then I just wouldn't
> care, but the difference between seconds and minutes is sufficiently
> large that we may need to work around the issue ourselves.
>
> -derek
>   
I have now created the following indices:
create index idx_splits_account_guid on splits (account_guid);
create index idx_slots_obj_guid on slots (obj_guid);
Phil, you really should create this in gnucash-gda.  They will help.

These have improved the performance of the MySql queries by a factor of 
nearly 10.  However, they are still far too slow.  Waiting a few minutes 
instead of tens of minutes for a register to open simply won't be 
acceptable.

I've also gone through the section of the MySql manual on optimizing "IN 
subquery" types of queries.  I tried their "pushdown" method.  Still 
unacceptable performance.  (I didn't time it, but I think it was 
slower.)  I really don't understand why a sub-query which produces a 
CONSTANT set of rows needs to be re-run by the outer query, which 
appears to be what the MySql manual says and also what is happening.

I could not run the "slots" queries on PostgreSQL.  However, it 
performed quite well on both forms of the remaining queries (sub-query 
and join).  It did so even before I created the 2 indices.

I am surprised at this.  Comparisons I've read claim that MySql is 
faster than PostgreSQL.  However, I'm not seeing it.  Perhaps, these 
were based upon older versions.

MySql version 5.0.51
SQLite3 version: 3.3.7
PostgreSQL version 8.2.6

Mark



More information about the gnucash-devel mailing list