GDA interim benchmarking synopsis

[Mark Johnson]

I won't post specific numbers here yet.  I still have to explore some 
adjustments to the queries, etc.

On SQLite, I ran VACUUM and ANALYZE prior to testing.  On MySql, I ran 
ANALYZE on each table.  It doesn't have a VACUUM command.  (VACUUM 
probably did nothing for SQLite anyway as a freshly saved db should have 
no space to reclaim.)

SQLite performs poorly on versions of queries using joins.  It performs 
very well on forms of queries using sub-queries.  The sub-queries take 
the place of the current lists of guids being sent by the register 
opening queries.  These are somewhat slower than the current versions, 
but still sub-second.  The join versions of the queries take seconds to 
minutes to run.  Unacceptable performance for joins.

MySql performs very well on versions of the queries using joins.  
However, it lends new meaning to the word "abysmal" when using 
sub-queries.  The worst query took 38 minutes to run!  (This one was 
sub-second on SQLite.)  The MySql manual indicates that it runs 
sub-queries from the outside to the inside.  This appears to mean that 
for every row considered by the "outside" query, it re-runs the "inside" 
query.  At least, this is consistent with the extreme run-times of these 
queries.

PostgreSQL can only be partially tested due to the SERIAL problem.  I 
think it will perform well on both forms of query, but it is hard to be 
sure when the slots queries seem to be the most challenging.

SQLite and MySql are completely opposite as to which queries perform 
well and poorly.  That's why I want to see if I can adjust the queries 
and indices to do better.

I may take a break from this to see about patching the PostgreSQL 
provider to test it, too.

Mark