I think I found a performance regression bug of sqlite 3.6.1 8. A sql will cost 1800 seconds to return the query result with sqlite 3.6.18 and with the previous version it only cost about 170 seconds.
I just attach the sql can reproduce the bug under below. But the database is a little large (4M), how to upload it to the mail list? -- romermb's incredible combination of events, shared events plus 'marriage' and alternate names as Facts 2010-01-06 -- revised by ve3meo to to bring out RoleName from RoleTableand Sharee's Names from NameTable, tried to speed up using UNION ALL - not much gain - still ~270 secs on a 50,000 event, 16,000 person table. -- revised by romermb on 2010-01-07 to incorporate COUNT, GROUP BY -- start with a table of nominally female spouses for the nominally male partners (RIN in FatherID) as a 'fact' for the latter - a Family, even if no actual Marriage 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 UNION ALL -- add nominally male spouses of nominally female partners (RIN in MotherID) as a 'fact' for the latter - a family even if no actual Marriage SELECT FactTypeTable.Name COLLATE NOCASE, 'Principal', NameTable2.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE, COUNT(1) 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 UNION ALL -- add all events for Individual SELECT FactTypeTable.Name COLLATE NOCASE, 'Principal', NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL, COUNT(1) FROM EventTable INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN NameTable ON EventTable.OwnerID = NameTable.OwnerID WHERE EventTable.OwnerType = 0 AND NameTable.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 UNION ALL -- add Alternate Name as a 'Fact' SELECT 'Alternate name', 'Principal', OwnerID, Surname COLLATE NOCASE, Suffix COLLATE NOCASE, Prefix COLLATE NOCASE, Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL, COUNT(1) FROM NameTable WHERE IsPrimary = 0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 UNION ALL -- add shared events other than family as 'Facts' - revised by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable SELECT FactTypeTable.Name COLLATE NOCASE, RoleName COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE, EventTable.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, COUNT(1) FROM WitnessTable INNER JOIN roletable ON Role = RoleID INNER JOIN EventTable ON WitnessTable.EventID = EventTable.EventID INNER JOIN NameTable AS NameTable1 ON WitnessTable.PersonID = NameTable1.OwnerID INNER JOIN NameTable AS NameTable2 ON EventTable.OwnerID = NameTable2.OwnerID INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID WHERE NameTable1.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ORDER BY RIN; best regards chinshou _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
