Logo Home    Page list

Query is taking a very, very long time

I have been using this query for a quite a while now:
-- Alternate Name Typos
Select f.FamilyID,
-- 1 -- Column B Gets the surname, given name and fathered for the fatherid from the famillytable for the fathers primary record. This is the husband.
(select n1.surname || ", " || n1.given || " " || f.FatherID
from nametable n1
where f.fatherid = n1.ownerid -- Get the father info from the primary record
and n1.isprimary = 1) as Husband,
-- 2 -- Column C Gets the surname, given name and mothered from the familytable for the motherid from the mothers primary record. This is the Wife.
(select n2.surname || ", " || n2.given || " " || f.MotherID
from nametable n2
where f.motherid = n2.ownerid -- Get the mother info from the primary record
and n2.isprimary = 1) as Wife,
-- 3 -- Column D -- Gets the wife’s given name, maiden name, married name, husband’s given name and MothersID from the wife’s alternate name record. This is the Alternate Name.
(select n3.given || " " || n5.surname || " " || n5.suffix || " " || f.MotherID
from nametable as n3
inner join -- get the father's record from nametable file for the primary record
nametable as n4
on f.fatherid = n4.ownerid
and n4.isprimary = 1
inner join -- get the surname and suffix from the nametable for the father's primary record
nametable as n5
on "(" || n4.given || ")" = n5.suffix
where f.motherid = n3.ownerid -- get the mother's information from the alternate record
and n3.isprimary = 0) as Alternate,
-- 4 -- Column E -- Gets the wife’ given name from the wife’s alternate record. This is the Alternate Maiden Name.
(select n6.given
from nametable n6
inner join -- get the father's record from nametable file for the primary record
nametable as n7
on f.fatherid = n7.ownerid
and n7.isprimary = 1
where f.motherid = n6.ownerid -- get the mother's information for the alternate record and the husbands Surname
and n6.isprimary = 0
and n7.surname = n6.surname) as Alt_Maiden_Name,
-- 8 -- Column I -- Gets the wife’s surname from the alternate record. This is the Alternate Surname.
(select n18.surname
from nametable as n16
inner join -- gets the father's record from nametable file
nametable as n17
on (f.fatherid = n17.ownerid)
inner join -- gets the father's record from the nametable with the the suffix
nametable as n18
on "(" || n17.given || ")" = n18.suffix
and f.MotherID = n18.ownerid
and n17.surname = n18.surname
where f.motherid = n16.ownerid
and n16.isprimary = 0) as Alt_Surname,
-- 6 -- Column G -- Gets the suffix for the wife from the alternate name record. This is the Alternate Suffix.
(select n12.suffix
from nametable as n10
inner join -- get the father's record from nametable file for the primary record
nametable as n11
on f.fatherid = n11.ownerid
and n11.isprimary = 1
inner join -- get the suffix from the nametable for the father's primary record
nametable as n12
on n10.given || " " || n11.surname = n12.given || " " || n12.surname
and "(" || n11.given || ")" = n12.suffix
where f.motherid = n10.ownerid
and n10.isprimary = 0) as Alt_Suffix,
-- 5 -- Column F -- Gets the wife’s given name and surname from the wife’s primary record. This is the Primary Maiden Name.
(Select n9.given || " " || n9.surname
from nametable as n9
where f.motherid = n9.ownerid
and n9.isprimary = 1) as Pri_Maiden_Name,
-- 9 -- Column J -- Gets the husband’s surname form the husband’s primary record. This is the husband’s surname.
(select n19.surname
from nametable as n19
where f.fatherid = n19.ownerid
and n19.isprimary = 1) as Primary_Surname,
-- 7 -- Column H -- Puts parenthesis around the husbands given name from the primary records. This is the Primary Siffix.
(select "(" || n13.given || ")"
from nametable as n13
where f.fatherid = n13.ownerid
and n13.isprimary = 1) as Pri_Suffix
from familytable f
where f.FatherID is not 0 -- fatherid is not 0
and f.MotherID is not 0 -- motherid is not 0
--and substr(wife, 1, 1) <> "," -- the first position of the wife was not a comma
--and $Enterfamilyid = f.familyid -- Inputs the familyid
--and familyid in(66, 1617)
order by wife, husband;

Up until a couple days ago, it took about 3 seconds to run. All of a sudden it is taking minutes like at least 5 minutes. I am using sqlite Expert 3.4.76.2306. I have tried Sqlite 5 also and i am getting the same thing. I created a GED and imported it back into rootsmagic. I have rebooted my machine. I have run ccleaner and malwarebytes. Nothing seems to work. Any ideas on what is happening or what I need to do?

I am adding a new SQL Tab and it is taking minutes.

Discussions

ve3meo

Comments

ve3meo 23 November 2017 14:08:31

Something changed. Think back. Your last sentence suggests that it's not the database or the query; rather the OS or the program. Maybe RAM has been overflowed into the swapdisk file. That would explain sluggish database but not the slow new tab. Run the Windows Task Manager and see what part of the system is saturating.

ve3meo
ve3meo 24 November 2017 04:54:07

I had a quick look at your script and ran it on both SQLite Expert Personal 4-32b and on SQLiteSpy and got similar results on a smallish database (729 records in NameTable of which only 170 are Alternate) ~200 milliseconds.

Explain Query Plan suggests that it could bog down because there are so many correlated sub-queries (9) involving a total of 16 SEARCHes. Fortunately, the optimiser applies an INDEX to all the SEARCHes else these would have become SCANs. A redesign to eliminate or reduce the CORRELATED SUBQUERIES could make a huge improvement on a large database.

A minor point perhaps is this. IsPrimary is already a logic value. There is no need to test that it =1 or =0. WHERE NOT IsPrimary produces the same result as WHERE IsPrimary=0; WHERE IsPrimary is one less operation than WHERE IsPrimary = 1.

And this may no longer be true. In early RM4+, I found that WHERE +IsPrimary was actually faster than WHERE IsPrimary. But that was due to an Indexing error on the part of the developer which, I believe, was corrected.

www.000webhost.com