On Friday, 27 December, 2019 14:19, Simon Slavin <slav...@bigfraud.org> wrote:
>On 27 Dec 2019, at 7:46pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 >minutes. (Perhaps it disables some of the internal multithreaded sorters >-- I don't know). >Can anyone explain this ? The increase was in the summarization query which basically does a group by/order by of the ~11 million row run results into an ~25,000 row summary. This is a significant amount of data to group and sort, and since the database model is fully relational (that is it is normalized to 4th normal) that is a LOT of data to sort since indexes are non-helpful. Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When in one of the multithreaded modes, that query utilizes an average of 60% CPU, compared to 12% when running singlethreaded. (Looking at that query again I found an error in the group by/order by that was causing SQLite to have to sort twice -- once for the group by then a partial sort for a non-matching order by -- making them the same chopped the time in half again). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
