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 _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
