Max Vlasov <max.vla...@...> writes: > > On Mon, Jan 11, 2010 at 2:33 AM, Tom Holden <holden_fam...@...>wrote: > > > I have been trying out a number of SQLite managers, one test being the > > execution time for the same query on the same database on the same computer. > > The scattering of results was very surprising with a spread on the order of > > 10:1. > > > > Is the message posted 11 hours ago about the same issue? ( > http://www.mail-archive.com/sqlite-users-CzDROfG0BjIdnm+yROfE0A <at> public.gmane.org/msg49650.html) > Anyway I still suggest the same (see in the thread) - compare VDBE code > sequences > > Max Yes, that is the developer of one of the SQLite managers who posted the earlier message re "Performance regression...". I am not a developer, merely a new user of applications that use SQLite who is delving into the application database. I compared the EXPLAIN QUERY PLAN results for SQLite 3.5.4, 3.6.17 and 3.6.20. The first two are identical; the third is quite different. I don't quite know what that means; maybe it indicates that the later version joins the tables less efficiently. Oddly enough, by revising the query to explicitly use an index, the two later releases are much faster than for the unindexed query while the first returns an error near 'INDEXED'.
Here are the results of the EXPLAIN QUERY PLAN for the original, unindexed query: For 3.5.4 and 3.6.17 which executed in ~240s: "order", "from", "detail" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "2", "TABLE FamilyTable WITH INDEX idxFamilyFatherID" "2", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "3", "1", "TABLE FactTypeTable USING PRIMARY KEY" "4", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "2", "TABLE FamilyTable WITH INDEX idxFamilyFatherID" "2", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "3", "1", "TABLE FactTypeTable USING PRIMARY KEY" "4", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "0", "2", "TABLE NameTable WITH INDEX idxNamePrimary" "1", "0", "TABLE EventTable WITH INDEX idxOwnerDate" "2", "1", "TABLE FactTypeTable USING PRIMARY KEY" "0", "0", "TABLE NameTable WITH INDEX idxNamePrimary" "0", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1", "0", "TABLE WitnessTable WITH INDEX idxWitnessPersonID" "2", "1", "TABLE roletable USING PRIMARY KEY" "3", "2", "TABLE EventTable USING PRIMARY KEY" "4", "5", "TABLE FactTypeTable USING PRIMARY KEY" "5", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" And for 3.6.20 which executed the same query in ~2500s: "order","from","detail" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","4","TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "2","2","TABLE FamilyTable WITH INDEX idxFamilyMotherID" "3","0","TABLE EventTable WITH INDEX idxOwnerDate" "4","1","TABLE FactTypeTable USING PRIMARY KEY" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","4","TABLE NameTable AS NameTable2 WITH INDEX idxNamePrimary" "2","2","TABLE FamilyTable WITH INDEX idxFamilyMotherID" "3","0","TABLE EventTable WITH INDEX idxOwnerDate" "4","1","TABLE FactTypeTable USING PRIMARY KEY" "0","2","TABLE NameTable WITH INDEX idxNamePrimary" "1","0","TABLE EventTable WITH INDEX idxOwnerDate" "2","1","TABLE FactTypeTable USING PRIMARY KEY" "0","0","TABLE NameTable WITH INDEX idxNamePrimary" "0","3","TABLE NameTable AS NameTable1 WITH INDEX idxNamePrimary" "1","0","TABLE WitnessTable WITH INDEX idxWitnessPersonID" "2","1","TABLE roletable USING PRIMARY KEY" "3","2","TABLE EventTable USING PRIMARY KEY" "4","5","TABLE FactTypeTable USING PRIMARY KEY" "5","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" Here are the results for the indexed query, identical for 3.6.17 and 3.6.20 but an error with 3.5.4 so no result to compare, executes in ~2s: "order","from","detail" "0","0","TABLE EventTable" "1","1","TABLE FactTypeTable USING PRIMARY KEY" "2","2","TABLE FamilyTable USING PRIMARY KEY" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" "0","0","TABLE EventTable" "1","1","TABLE FactTypeTable USING PRIMARY KEY" "2","2","TABLE FamilyTable USING PRIMARY KEY" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" "0","2","TABLE NameTable WITH INDEX idxNamePrimary" "1","0","TABLE EventTable WITH INDEX idxOwnerDate" "2","1","TABLE FactTypeTable USING PRIMARY KEY" "0","0","TABLE NameTable WITH INDEX idxNamePrimary" "0","0","TABLE WitnessTable" "1","1","TABLE roletable USING PRIMARY KEY" "2","2","TABLE EventTable USING PRIMARY KEY" "3","5","TABLE FactTypeTable USING PRIMARY KEY" "4","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "5","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" The results for these two queries taken together suggest that a release between 3.5.4 and 3.6.17 instituted changes that corrected a problem for this indexed query and that a change after 3.6.17 adversely affects the speed of executing the unindexed query. 3.6.17 seems to be a 'sweet spot' that is best for both the indexed and unindexed queries. It would be great if others might compare execution times for the same queries using 3.6.17 as a control and later ones as test. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
