I’m currently working with a rather large Gramps database (about 20,000 individuals) on an old Windows laptop, using Gramps version 6.0.
One issue I constantly face is the very slow search when I try to look up individuals by surname. It takes about 10 seconds for results to appear. Since I have to search frequently, this delay becomes quite frustrating.
Even on my newer desktop PC, search isn’t exactly fast — it still takes about 4–5 seconds to return results, which is just barely acceptable. But on the old laptop, it becomes almost unusable.
Is there any simple way to speed up the search process? Maybe some settings I can adjust, or tips to improve performance on slower hardware?
Ok some more information please, which Gramps version of 6.0.x the latest is 6.0.3?
Which Windows version?
For both computers do you know what type of storage technology they use for example are they old enough to still be using old style mechanical hard disk drives (HDD) or do they use the newer style Solid State Drive (SDD) or similar?
When you mention slow searches do you mean in the Category List views, or via one of the addons like Graphview that has its own search bar?
The search using the Filter gramplet’s name field is complicated by it being a multiple parameter pattern search. It searches each word across a multitude of name-related fields in the database. Each additional word decreases the performance.
The searchbar was a more responsive option in older versions of Gramps. But I seem to recall that it was “upgraded” recently form a simple single‐string ‘exact’ search to the same ‘multiple string, multiple pass’ search in a recent update.
In past discussions, @SNoiraud has recommended using the RegEx option to force the Filter gramplet back to a single pass search. However, another thread on the forum says that RegEx performance took a hit under 6.0
There are other gramplets that use other name searching techniques with faster response. And Nick suggests experimenting with increasing the caching size. See the postings by @Nick-Hall and @kku in the following discussion:
Just to avoid a misunderstanding: this hit has nothing to do with RegEx searches. It exists with “normal” searches as well. As a result, v6 is virtually unusable with really large databases (ca 100k individuals). v6 on SQLite doubled/tripled the search times compared with v5.1.6 on BSDDB which I’m still using.
We have had some thorough discussions on this forum about the speed issue with large databases. The consensus appears to be that the problem will not go away as long as the Python blobs are with us. Those blobs make it impossible to use the index capabilities of modern database backends which means that searching will basically be done by the frontend and not by the database backend where it should be done.
My solution was to feed the Gramps XML file into a PostgreSQL database with proper indexing and do all the searching there which results in searches with run times in the range of fractions of a second. Even if frontend logic is added to this, I’m still under a second.
From within Gramps, there’s not much you can do.
Under the tab Tools->Family Tree Repair you can try to run ‘Rebuild secondary indexes’ to see if this might change performance.
Using command line sqlite3 you can do quite a lot to improve performance.
First thing is to look at memory in your PC. Gramps sqlite database is configured with a page size of 4096 bytes (or 4 KB) and a buffer size of 2000 pages. This is about 8 MB of memory. If you have 8 GB meomry in your PC, you’re only using 0.1% for buffering Gramps data. increase the buffer and you’ll see better performance.
Using sqlite3 utility the command is ‘pragma cache_size = 10000;’ to increase the buffer to 40 MB.
The ‘person’ table has four indexes ‘handle’, 'gramps_id, ‘surname’ and ‘given_name’. Filtering on one of those should be quite fast. Filtering on any other person value will result in a full table scan, thus take longer time.
If filtering on an indexed value is time consuming, it might be caused by sqlite lacking statistical data used for the optimizer. If this is the case run the ‘analyze’ command in sqlite3 using ‘pragma analyze;’.
This is not correct. Sqlite has the capability to create virtual columns using the function json_extract(). You can then build an index on the virtual column.
Right, but the blobs aren’t JSON objects. According to my understandig of the discussions, the idea (I’m not sure if it’s really a strategy yet) is to go from blobs to JSON objects and then be able to use modern database backends and their indexing capabilities.
Ok, so I guess my understanding of what you told me here was wrong. Sorry for that and communicating my wrong understanding, but it appears that moving from Python blobs to JSON objects didn’t increase the speed of queries, quite the contrary. What would then be the reason for the (quite dramatic) performance hit when going from v5.1.6 on BSDDB to v6 on SQLite?
As I understand, JSON objects give potential possibilities to make Gramps faster including search and filters. But JSON objects - its not enough. It needs some additional conceptual changes in Gramps core, more additional Gramps APIs, right?
It’s true that switching from Python blobs to JSON hasn’t yet improved query speed—in fact, queries may even be slower for now. But this transition is a necessary step toward unlocking more advanced SQLite features like indexes using json_extract(), views, and user-defined functions (UDFs). These features enable filter-like functionality similar to what’s already in Gramps.
While it’s not a small, one-off change, converting to JSON is a key milestone that lays the groundwork for more efficient querying and scalable data handling in future versions of Gramps.
So we just need to be a bit patient—developers will likely continue improving things and start tapping into backend database features that can boost performance quite a lot.
Thank you all so much for your thoughtful and kind replies!
Many thanks to @emyoulation for explaining why the search in the right sidebar might be slow — it helped me understand things a bit better.
@StoltHD thank you for explaining. I see this with optimism — it looks like things are moving in the right direction, it just takes a bit of patience.
Thanks again to everyone who joined the discussion. You’ve managed to explain complex things in such a clear and friendly way that it makes me want to keep learning and using Gramps with even more interest
That is correct. One complicating factor is that Gramps must work with any database backend. For example, the grampsweb uses PostgreSQL. But it should be able to support any DB (say MongoDB) that implements the API. So we still need some abstraction away from the details of any particular database.
Having said that, a long-term goal of mine has been to be able to exploit the low-level database speed, but fall back to a general solution if that is not available. I’m hoping that the developers can agree on a way forward, and we can begin working on this for Gramps 6.1.
One big impact of going from the pickled arrays (aka “blobs”) to the new JSON data is that the JSON is quite a bit larger in size. So, I suspect the amount of RAM available could have an impact.
On the other hand, the optimizer in 6.0.3 can make some filtered searches much faster. We’ll continue that trend as we move forward with new versions of Gramps.
