Jump to content


Photo

Speeding up searches - can fields be indexed?


  • Please log in to reply
5 replies to this topic

#1 OpaJay

OpaJay

    New Member

  • Members
  • Pip
  • 1 posts

Posted 05 May 2015 - 11:35 AM

I run large Legacy database (1.3 million names, 1.6 Gb). I am encountering file size problems with Legacy, I just started with RM7. The import process was flawless.

My frustration with RM7 is search speed. Even with the database in a RamDisk, it takes 30 - 45 seconds to jump to a known record number.

Can record numbers be indexed to avoid the serial search through all records, or am I just missing the obvious?

For quicker searches, is it possible to index other fields (such as a custom reference number we use that is similar to Soundex)?



#2 Renee Zamora

Renee Zamora

    Advanced Member

  • Admin
  • PipPipPip
  • 8519 posts

Posted 06 May 2015 - 10:41 AM

If you have a extremely large database about the only thing you can do to help speed it up in RootsMagic is to increase the amount of RAM your computer is running. Next, its using the File>Database Tools to help keep those indexes flowing smoothly.

 

Are you using Search>Person List>Record to locate people by their record numbers?


Renee
RootsMagic

#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 06 May 2015 - 08:23 PM

How does the speed of Rootsmagic compare to that of Legacy FT for the similar databases?

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.


#4 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 07 May 2015 - 09:26 AM

You say that you have the 1.6GB file in RAMdisk. Do you note any significant HD activity while you execute a RootsMagic operation? Possibly the Journal file is being written to HD. Under some conditions, the Journal file can be as large as the database file. Normally it is written to the same directory as the database file so I would expect it to be written to the RAMdisk drive and if there was insufficient space there would be an error message.

 

I assume that you have lots of RAM since you can afford the RAMdisk allocation so I doubt that RootsMagic would benefit from more RAM (unless there is significant HD activity).

 

Your question assumes an absence of a database index on the Record Number and that adding such an index would speed up the process. The RootsMagic database has many tables and they are all indexed on their Primary Key and several on key combinations specifically for search optimisation. The Record Number (known variously as PersonID, OwnerID, HusbandID, WifeID, ChildID...) is indexed. SQLite is described as "extremely fast and efficient" in SQLite vs MySQL vs PostgreSQL:...

 

However, the application may not rely exclusively on the SQLite search functions which exploit the indexes; maybe it is doing some things in its high level language (Delphi) that are independent of SQLite and are taking undue time. One of these is display - it is my impression that RootsMagic can bog down with a very large result set from a SQLite query. I have seen similar behaviour with some SQLite managers. There is, I believe, grounds for and room for improvement in the code design.

 

A user cannot add a SQLite table Index for what I assume you mean is a specific Fact Type such as the Reference Number (builtin) or Soundex Number (user custom). However, FactTypeID is the Primary Key for the FactTypeTable which defines each unique fact type and has indexes on the Name, Abbrev and GEDCOM Tag fields. The EventTable contains the values for each instance of a FactType and is indexed on the OwnerID (Record Number) and EventType (FactType); your Soundex-like value is stored in the Details field of this table for which there is no explicit index. The SQLite Query Optimiser might create a temporary index for this field, depending on the nature of the query.

 

There is a SQLite database function called ANALYZE that has not been exposed in RootsMagic. What it does is gather statistics about the data that is stored in tables for the Query Optimizer to use in query planning. Whether it would help is moot, given that there are many predefined indexes directed at optimising specific queries.

 

There is a SQLite database function called VACUUM that is exposed under Database Tools as "Compact" which might be of some help - moreso for the database file on the HD than is likely for it in RAMdisk.


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.


#5 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3438 posts

Posted 07 May 2015 - 11:05 AM

How does the speed of Rootsmagic compare to that of Legacy FT for the similar databases?

 

I would also like to know the answer to that speed comparison.


Customers should never be frustrated by things they cannot do.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#6 Ludlow Bay

Ludlow Bay

    Advanced Member

  • Members
  • PipPipPip
  • 868 posts

Posted 07 May 2015 - 06:43 PM

Perhaps you have allotted too much memory to the ram disk and starved the needs of system memory.