Rebuild+Indexes+and+Update+Birth+and+Death+Years

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: 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!
 * Birth Year Mis-Match
 * Death Year Mismatch

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.

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. code format="sql" -- 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]..\d\d\d\d.+' 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]..\d\d\d\d.+' 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    ) code