Rebuild Indexes and Update Birth and Death Years #update #date #birth #death #index #speed #rmnocase

Rebuilding the indexes of large databases using RootsMagic’s File > Database tools > Rebuild Indexes function is ponderously slow, still at version 6.0.0.2. A 162000 person database took over 100 minutes. This page provides procedures that take but a few minutes.

In RootsMagic version 5.0.2, the Rebuild Indexes tool was introduced under Menu > File > Database Tools; it not only rebuilds the SQLite database indexes which must be kept accurate but also updates the BirthYear and DeathYear columns of the NameTable. These values are displayed optionally in the sidebar Index and are used in some other views and reports. Prior to 5.0.2, there was no tool to rebuild the SQLite indexes and when the sidebar BirthYear and the Birth fact got out of sync (they still can), the only way to update them in RM was to edit the Birth fact and save it. To help find such mismatches, the following procedures were developed:

• Birth Year Mis-Match
• Death Year Mismatch

I do not recall for certain why no batch update procedure was published at the time. Perhaps it had something to do with the RMNOCASE issue, now worked around by RMNOCASE – faking it in SQLite Expert, command-line shell et al or RMNOCASE – faking it in SQLiteSpy and the fact that whatever index discrepancies between the fake and the real collation can be resolved by running RM’s Rebuild Indexes!

I suspect that what takes RM so long is not the SQLite Re-indexing but rather the recalculating and display of the Birth and Death years because re-indexing with the fake collation in SQLiteSpy takes less than two minutes with this big database. So the shortcut procedure outlined here relies on the assumption that RootsMagic’s SQLite database engine is as efficient as SQLiteSpy’s and completes the critical database indexing in a similar length of time, if such re-indexing is required.

1. Run RM’s Menu > File > Database tools > Test database integrity. Despite the warning, you can expect it to complete in tens of seconds, not tens of minutes, on even a fairly low end computer, varying with size.
2. If the result of 1 is OK, skip the next step.
3. If the result of 1 is NOT OK, then run RM’s Rebuild Indexes. After a few minutes, use the Windows Task Manager to stop RootsMagic. Return to step 1.
4. Once database integrity is OK, then it is safe to proceed with SQLiteSpy to update the Birth and Death Years using the query below.
5. Having completed the SQLite query, close and reopen RootsMagic Explorer to view the results. Retest database integrity to be satisfied.

UpdateBirthDeathYears.sql
2012-12-06
Rev A: revised to respect the Primary fact if there are multiples. Completed update of 162,000 person database in 25 seconds.
Rev B: Christen, Baptism and Burial, Cremation now alternate Birth, Death dates in that order. BC dates. Update time doubled to 55 seconds.
Some may prefer the faster, leaner version.

-- UpdateBirthDeathYears.sql
/*
2012-11-14 Tom Holden ve3meo
2012-12-06 revA: priority to first record set to Primary, else first record
 when multiple Birth or Death facts.
           revB: incorporated Christen, Baptism as alternate Birth facts; Burial, Cremation
 as alternate Death facts - in that order. Same priority for Primary facts in same type.
 Now supports BC dates.
 
Sets Birth and Death years as seen in the sidebar index and various other
reports and displays to match the corresponding facts.
 
Close and reopen RootsMagic Explorer to see the results.
 
*/
UPDATE NameTable
  SET
    BirthYear=
    (
      SELECT BirthYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS BirthYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (1,3,7) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Births
      WHERE NameTable.OwnerID = Births.OwnerID
     ),
    DeathYear=
    (
      SELECT DeathYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS DeathYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (2,4,5) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Deaths
      WHERE NameTable.OwnerID = Deaths.OwnerID
    )
;

Discussions & comments from Wikispaces site

Inline comments