Summary of slow down issues

Development
1

Making a note of speed-up findings while fresh on my mind.

  1. Performed some tests on IsAncestorOf on the Example tree
  2. If I run the filter on every person, it takes 2 minutes and 10 seconds
  3. If I avoid all unpickling, it comes it at 2 or 3 seconds
  4. Operating in batches is faster, fewer queries
  5. Unpickling can be completely avoided, even when we need info from inside the blobs
  6. This involves saving the unpickled data in a new column and using JSON_EXTRACT() on the JSON-based text
  7. We could implement such methods in a business-logic layer on top of the database. The current, unpickled, deserialized objects can still be the fallback.
  8. Another enhancement for regular operation is to not unpickle the same object repeatedly. We could have a cache of unpickled, or deserialized objects.

What does a completely optimized low-level business logic function look like?

def get_parent_handles_from_main_family(db, person_handle):
   db.dbapi.execute(
       "SELECT JSON_EXTRACT(unblob, '$[9]') FROM person WHERE handle = ? limit 1;",
       [person_handle]
   )
   row = db.dbapi.fetchone()
   parent_family_list =  json.loads(row[0])
   if parent_family_list:
       return parent_family_list[0]

It is low-level, but not any more so that the unserialize methods.

The above replaces this which requires an unpickled, unserialized Person to begin with, and then unpickles and unserializes a Family too:

        fam_id = person.get_main_parents_family_handle()
        if fam_id:
            fam = db.get_family_from_handle(fam_id)
            if fam:
                f_id = fam.get_father_handle()
                m_id = fam.get_mother_handle()
3 Likes
2

@StoltHD I saw your message from earlier this year, and thought you might be interested in seeing what you mentioned in Performance Issues with Gramps - #37 by StoltHD looks like. :point_up:

1 Like
3

Just for completeness, Nick noted that a reason BSDDB could be faster with large trees is because SQLite has a smaller cache.

Could/should the cache be made adaptive to the Tree size?

And there’s an outstanding addition for the wiki article:

1 Like
4

To lookup all ancestors of all 2,157 people in the Example tree Gramps called pickle.loads() 4,731,244 times (that’s almost exactly 2157 * 2157). When using replacement functions like above, it was called 38 times. That is the number one issue of slowness IMHO.

6 Likes
5

That’s right, and my experience too in other software areas. Tweaking cache sizes and adding indexes can speed things up a bit, even with a factor of 2 or more, but rewriting code can make things dozens of times faster. I did that with Oracle too some 20 years ago, reducing the time needed by a query from 20 minutes to 20 seconds. That’s what better code can do.

2 Likes
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.