"Simon Slavin" <slav...@bigfraud.org> wrote in message news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org... > > The database structure has not changed. It is optimisation of a > particular kind of query which seems to be working differently -- and not > as well. Unfortunately, the example supplied is extremely complicated and > it's not easy to tell which aspect of the query is at fault.
Sorry. What I meant: is it possible that it is just the particular structure of my database that is susceptible to this performance regression? I agree that the optimisation strategy has changed for the better between 3.6.17 and 3.6.20 for the unANALYZED unINDEXED query. I postulate that the optimisation strategy is defective for the ANALYZED, INDEXED query for both 3.6.17 and 3.6.20 while 3.5.4 throws up a false syntax error. The example I've given is a UNION of five SELECTs but it is the first two that have been the most problematic. As they are virtually identical, let me repeat the EXPLAIN QUERY PLAN results, first for the always slow, not deliberately indexed query and then for the once fast, explicitly indexed query. All the index files are predefined by the genealogy database application software. The full query returns ~52,000 rows in the approx. time shown for each SQLite release and condition. Some variation is to be expected from varying concurrent processes but, generally, sqlite consumes ~98% of CPU, most of the time without disk thrashing. ---------------------------------- THE UNINDEXED QUERY SELECT FactTypeTable.Name COLLATE NOCASE AS Fact, 'Principal' AS 'Role Type', NameTable1.OwnerID AS RIN, NameTable1.Surname COLLATE NOCASE AS Surname, NameTable1.Suffix COLLATE NOCASE AS Suffix, NameTable1.Prefix COLLATE NOCASE AS Prefix, NameTable1.Given COLLATE NOCASE AS 'Given Name', NameTable2.OwnerID AS 'Sharer RIN', NameTable2.Surname COLLATE NOCASE AS 'Sharer Surname', NameTable2.Suffix COLLATE NOCASE AS 'Sharer Suffix', NameTable2.Prefix COLLATE NOCASE AS 'Sharer Prefix', NameTable2.Given COLLATE NOCASE AS 'Sharer Given Name', COUNT(1) AS Count FROM EventTable INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID INNER JOIN NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 . . ORDER BY RIN RESULTS BEFORE ANALYZE SQLite 3.5.4: ~250s EXPLAIN QUERY PLAN results BEFORE ANALYZE "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" SQLite 3.6.17: ~250s cf 190s after ANALYZE EXPLAIN QUERY PLAN results BEFORE ANALYZE 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, SQLite 3.6.20: ~2500s cf ~165s after ANALYZE EXPLAIN QUERY PLAN results BEFORE ANALYZE "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" RESULTS AFTER ANALYZE SQLite 3.5.4: ~198s cf ~250s before ANALYZE EXPLAIN QUERY PLAN results AFTER ANALYZE "order", "from", "detail" "0", "1", "TABLE FactTypeTable" "1", "2", "TABLE FamilyTable" "2", "0", "TABLE EventTable WITH INDEX idxOwnerEvent" "3", "3", "TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4", "4", "TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" SQLite 3.6.17: ~190s cf ~250s before ANALYZE EXPLAIN QUERY PLAN results AFTER ANALYZE order,from,detail 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, SQLite 3.6.20: ~165s cf ~2500s before ANALYZE EXPLAIN QUERY PLAN results AFTER ANALYZE "order","from","detail" "0","1","TABLE FactTypeTable" "1","2","TABLE FamilyTable" "2","0","TABLE EventTable WITH INDEX idxOwnerEvent" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" ------------------------------------- THE INDEXED QUERY SELECT FactTypeTable.Name COLLATE NOCASE AS Fact, 'Principal' AS 'Role Type', NameTable1.OwnerID AS RIN, NameTable1.Surname COLLATE NOCASE AS Surname, NameTable1.Suffix COLLATE NOCASE AS Suffix, NameTable1.Prefix COLLATE NOCASE AS Prefix, NameTable1.Given COLLATE NOCASE AS 'Given Name', NameTable2.OwnerID AS 'Sharer RIN', NameTable2.Surname COLLATE NOCASE AS 'Sharer Surname', NameTable2.Suffix COLLATE NOCASE AS 'Sharer Suffix', NameTable2.Prefix COLLATE NOCASE AS 'Sharer Prefix', NameTable2.Given COLLATE NOCASE AS 'Sharer Given Name', COUNT(1) AS Count FROM EventTable INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID INNER JOIN NameTable AS NameTable1 indexed by idxNameOwnerID ON FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN NameTable AS NameTable2 indexed by idxNameOwnerID ON FamilyTable.MotherID = NameTable2.OwnerID WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 . . ORDER BY RIN RESULTS BEFORE ANALYZE SQLite 3.5.4: Error near indexed SQLite 3.6.17: ~2s cf ~210s after ANALYZE EXPLAIN QUERY PLAN results BEFORE ANALYZE 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, SQLite 3.6.20: ~2s cf 185s after ANALYZE EXPLAIN QUERY PLAN results BEFORE ANALYZE "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" RESULTS AFTER ANALYZE SQLite 3.5.4: error near "indexed" SQLite 3.6.17: ~210s cf ~2s before ANALYZE EXPLAIN QUERY PLAN results AFTER ANALYZE order,from,detail 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, SQLite 3.6.20: ~185s cf ~2s before ANALYZE EXPLAIN QUERY PLAN results AFTER ANALYZE "order","from","detail" "0","1","TABLE FactTypeTable" "1","2","TABLE FamilyTable" "2","0","TABLE EventTable WITH INDEX idxOwnerEvent" "3","3","TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID" "4","4","TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID" Thanks for any follow-up. Is this the right way to flag a problem for the SQlite developers? Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
