People in Limbo

Our genealogy database program.

Moderator: Nick Hunter

Post Reply
laschapas
Posts: 74
Joined: Tue Jan 24, 2006 9:48 am
Location: Derbyshire, UK

People in Limbo

Post by laschapas » Mon May 25, 2009 8:52 pm

Hi Nick,

Tools -> People in limbo produces a text file listing the P number & name of people in limbo. This text editor file cannot be left open while editing any of the people listed. To use this information I have to copy the data to an external text editor, close the internal text editor, and then enter the P number in the Edit person window, to work on the person found in the people in limbo test. Would it be possible to produce the output of the people in limbo search as a table that can be clicked to give direct access to the edit person window. I have been playing with the "Links" database using the SQL query feature to try to create the people in limbo test but I don't fully understand the "Links" database to get it to work. The same problem exists with the "find date errors" output.

John Wood

Nick Hunter
Posts: 669
Joined: Sun Dec 18, 2005 9:36 am
Location: SpanSoft, Scotland
Contact:

Post by Nick Hunter » Wed May 27, 2009 8:30 am

Hi John,

We will definitely look at clickable results lists for these functions.

I don't think that it is possible to create an SQL query which will find the people in limbo.

The Links table holds both "Children" and "Marriages". If the field RecType = "F" then PersFam = family code and ChildMarr = child code. If the field RecType = "P" then PersFam = person code and ChildMarr = marriage code.

jhunt02
Posts: 19
Joined: Fri Jul 03, 2009 4:15 pm

SQL query for people in limbo

Post by jhunt02 » Sat Jul 04, 2009 6:10 am

This query can be used in Kith & Kin to show all people who are in limbo. Furthermore, as you add the people to their families, they immediately disappear from the query result list ("live" update), so you can tell how far along you've gotten. Pretty cool!

-- List all people who are in limbo
select * from People
where Code not in
(select P.Code from People P, Links L where
(P.Code = L.ChildMarr and L.RecType = 'F') or (P.Code = L.PersFam and L.RecType = 'P'));

laschapas
Posts: 74
Joined: Tue Jan 24, 2006 9:48 am
Location: Derbyshire, UK

Post by laschapas » Sat Jul 04, 2009 3:51 pm

Hi,

I have just tried this with my database of over 3000 people and it did produce the same list as the Tools -> People in limbo feature. The result can also be used to directly select a person's edit window, that's just what I wanted, thanks.

The Tools -> People feature produces the list as a text file showing only the Person Description field, in under a second. The query takes 30 seconds on my AMD Athlon XP 3200+ running Windows XP. This query certainly solves the problem I raised on 25 May.

Why not add this query example to the "Favourite SQL queries" topic?

Is it possible to produce a similar query for the date errors problem? There are far more user defined variables to test.

John Wood

jhunt02
Posts: 19
Joined: Fri Jul 03, 2009 4:15 pm

Post by jhunt02 » Sat Jul 04, 2009 5:11 pm

Hello,

Glad you liked the limbo query. The "date errors" problem is more complex. Theoretically it could be solved with a single SQL query, but the database engine probably can't process queries which are that complex, so the actual testing of date-error conditions is done in the program itself or using multiple queries.

I'm surprised the limbo query takes so long to execute (30 seconds). How many people in limbo did it return when you first tried it out? Execution could possibly be speeded up by adding a couple of indexes on the Links and/or People table, but as I don't work for SpanSoft, I'm afraid can't do that.

Best regards,
John Hunt

laschapas
Posts: 74
Joined: Tue Jan 24, 2006 9:48 am
Location: Derbyshire, UK

Post by laschapas » Sat Jul 04, 2009 8:40 pm

Hi John,

I packed the database and retimed the query. It still took 30 seconds to find 22 people from 3023, with 100% CPU Usage for the whole 30 seconds. There is some disc activity but it is not continuous. Running it a second time still took 30 seconds. the people.dat & people.idx files are both about 5Mb

John Wood

Post Reply