Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
On Wed, 10 Dec 2014 08:49:21 +0100 Eduardo Morras wrote: > > Why ORDER BY on INSERT? Does it work better? I would expect the > > unnecessary sort to be pure overhead. > > If you insert in correct index order, the index update phase is > faster because it don't need rebalance the b-tree so often after each > insert. OK, but at the cost of sorting the input first. Rebalancing a tree requires diddling a few pointers. Sorting in the average case is O(log N), and possibly worse, plus the attendant I/O. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
Thank you very much for the explanation and tips, they are appreciated. Dne 9.12.2014 v 14:30 Richard Hipp napsal(a): > Answered by adding a comment at > https://bugzilla.redhat.com/show_bug.cgi?id=1161844 > > On Tue, Dec 9, 2014 at 6:06 AM, Jan Staněk wrote: > > Hi, > some of the banshee users noticed a huge slowdown in its operation > after upgrading to version 3.8.7 from 3.8.6. Here is the related log : > > [4 Debug 13:24:27.263] Executed in 12819ms > DELETE FROM CoreCache WHERE ModelID = 9; > INSERT INTO CoreCache (ModelID, ItemID) SELECT > 9, CoreTracks.TrackID > FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, > CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks > WHERE CoreTracks.Year IN > (SELECT CoreTracks.Year FROM CoreTracks, CoreCache > WHERE CoreCache.ModelID = 371 AND > CoreCache.ItemID = CoreTracks.TrackID ) > ORDER BY Year > > Reverting to 3.8.6, gives back a fast answer : > > [4 Debug 13:21:05.433] Executed in 24ms > DELETE FROM CoreCache WHERE ModelID = 9; > INSERT INTO CoreCache (ModelID, ItemID) SELECT > 9, CoreTracks.TrackID > FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, > CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks > WHERE CoreTracks.Year IN > (SELECT CoreTracks.Year FROM CoreTracks, CoreCache > WHERE CoreCache.ModelID = 371 AND > CoreCache.ItemID = CoreTracks.TrackID ) > ORDER BY Year > > The original bug reporter then went on and possibly isolated the bug. > Details are at https://bugzilla.redhat.com/show_bug.cgi?id=1161844 . > > Thanks for your work, >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Jan Stanek - Red Hat Associate Developer Engineer - Databases Team ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
On Tue, 9 Dec 2014 10:38:34 -0500 "James K. Lowden" wrote: > On Tue, 09 Dec 2014 12:06:20 +0100 > Jan Stan?k wrote: > > > INSERT INTO CoreCache (ModelID, ItemID) > > SELECT > ... > > ORDER BY Year > > Why ORDER BY on INSERT? Does it work better? I would expect the > unnecessary sort to be pure overhead. If you insert in correct index order, the index update phase is faster because it don't need rebalance the b-tree so often after each insert. --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
On Tue, 09 Dec 2014 12:06:20 +0100 Jan Stan?k wrote: > INSERT INTO CoreCache (ModelID, ItemID) > SELECT ... > ORDER BY Year Why ORDER BY on INSERT? Does it work better? I would expect the unnecessary sort to be pure overhead. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
Answered by adding a comment at https://bugzilla.redhat.com/show_bug.cgi?id=1161844 On Tue, Dec 9, 2014 at 6:06 AM, Jan Staněk wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > some of the banshee users noticed a huge slowdown in its operation > after upgrading to version 3.8.7 from 3.8.6. Here is the related log : > > [4 Debug 13:24:27.263] Executed in 12819ms > DELETE FROM CoreCache WHERE ModelID = 9; > INSERT INTO CoreCache (ModelID, ItemID) SELECT > 9, CoreTracks.TrackID > FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, > CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks > WHERE CoreTracks.Year IN > (SELECT CoreTracks.Year FROM CoreTracks, CoreCache > WHERE CoreCache.ModelID = 371 AND > CoreCache.ItemID = CoreTracks.TrackID ) > ORDER BY Year > > Reverting to 3.8.6, gives back a fast answer : > > [4 Debug 13:21:05.433] Executed in 24ms > DELETE FROM CoreCache WHERE ModelID = 9; > INSERT INTO CoreCache (ModelID, ItemID) SELECT > 9, CoreTracks.TrackID > FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, > CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks > WHERE CoreTracks.Year IN > (SELECT CoreTracks.Year FROM CoreTracks, CoreCache > WHERE CoreCache.ModelID = 371 AND > CoreCache.ItemID = CoreTracks.TrackID ) > ORDER BY Year > > The original bug reporter then went on and possibly isolated the bug. > Details are at https://bugzilla.redhat.com/show_bug.cgi?id=1161844 . > > Thanks for your work, > - -- > Jan Stanek - Red Hat Associate Developer Engineer - Databases Team > -BEGIN PGP SIGNATURE- > Version: GnuPG v2 > > iEYEARECAAYFAlSG16wACgkQXbaA6cD3QD38pwCcDiofiIh5jo+E8P5B/DhxLzGF > fGsAn1RJ8SjjEANSjUm4I1j+zReQfj0G > =2QtO > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bugreport - slowdown in sqlite after the ANALYZE statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, some of the banshee users noticed a huge slowdown in its operation after upgrading to version 3.8.7 from 3.8.6. Here is the related log : [4 Debug 13:24:27.263] Executed in 12819ms DELETE FROM CoreCache WHERE ModelID = 9; INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, CoreTracks.TrackID FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks WHERE CoreTracks.Year IN (SELECT CoreTracks.Year FROM CoreTracks, CoreCache WHERE CoreCache.ModelID = 371 AND CoreCache.ItemID = CoreTracks.TrackID ) ORDER BY Year Reverting to 3.8.6, gives back a fast answer : [4 Debug 13:21:05.433] Executed in 24ms DELETE FROM CoreCache WHERE ModelID = 9; INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, CoreTracks.TrackID FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks WHERE CoreTracks.Year IN (SELECT CoreTracks.Year FROM CoreTracks, CoreCache WHERE CoreCache.ModelID = 371 AND CoreCache.ItemID = CoreTracks.TrackID ) ORDER BY Year The original bug reporter then went on and possibly isolated the bug. Details are at https://bugzilla.redhat.com/show_bug.cgi?id=1161844 . Thanks for your work, - -- Jan Stanek - Red Hat Associate Developer Engineer - Databases Team -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlSG16wACgkQXbaA6cD3QD38pwCcDiofiIh5jo+E8P5B/DhxLzGF fGsAn1RJ8SjjEANSjUm4I1j+zReQfj0G =2QtO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
