Jump to content


Photo

Large Database Slow on Color Coding, Reindex,...

speed color coding reindex sqlite large database

  • Please log in to reply
No replies to this topic

#1 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6217 posts

Posted 14 November 2012 - 09:28 PM

The SQLite database engine on which RootsMagic is built has a fine reputation for being fast with large databases when queries and operations are well designed. RootsMagic has a pretty good reputation for being lean and fast amongst its brethren in the field of genealogy software. Some recent experience with Family Tree Maker 2012 bears that out. However, RM is not consistently efficient; some operations can become ponderously slow to the point of being unusable as a database grows in size. Case: a 162,000 person database.
  • Rebuild Indexes - forced a shutdown after more than 80 minutes
  • Color coding - some 54000 individuals marked, 100 minutes to complete!
I suspect there are other processes that are really time consuming but it takes so long to see if the program is in a perpetual loop or will complete the task that I have not yet identified any others apart from Duplicate Search Merge reported a long time ago and since much improved. These two, alone, flag that there is tuning yet to be done (Renee - tracking?).

Meanwhile, there are a couple of workarounds to get to the same desired end result a lot faster:

Rebuild Indexes
  • Don't! Do "Test database integrity" first. If it's okay, the SQLite database indexes are fine.
  • If integrity is not OK, you will have to run RM's Rebuild Indexes for a while, having made a backup first, but you can interrupt it if you want after a few minutes using the Windows Task Manager (Ctrl-Alt-Delete) to stop RootsMagic. Restart RM on the database and go back to step 1.
  • But your Birth and Death Years as shown in the sidebar Index and RootsMagic Explorer might not be in sync with the facts for some persons and you want to be sure they are (if multiple birth or death facts, it's the last one edited that normally sets the year). It is this process that consumed 98% of the time in the test case. Your choices are:
Color Coding
  • When RM reports x number of persons have been marked, it is safe to force RM to stop, using the Windows Task Manager. That said, you did make a backup first? Again, this part consumed about 2% of the time for the test database.
  • When you reopen RM on the database, the new colors will be displayed; for the test case, that saved about 97% of the time it took RM to carry out color coding without interruption.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.