Hacker News Reader

Comment by cbdumas | Hacker News Reader

So I read the script used to compare 'dbm' and 'sqlite3', and in sqlite it creates a table with no index. Hard to take that comparison seriously. I wrote a little benchmark script of my own just now and sqlite3 beats DBM handily if you add an index on the key.

View on HN

Yeah I tried that myself - I took that benchmark script and changed the CREATE TABLE lines to look like this (adding the primary key):

    CREATE TABLE store(key TEXT PRIMARY KEY, value TEXT)

Here are the results before I made that change:

    sqlite
    Took 0.032 seconds, 3.19541 microseconds / record
    dict_open
    Took 0.002 seconds, 0.20261 microseconds / record
    dbm_open
    Took 0.043 seconds, 4.26550 microseconds / record
    sqlite3_mem_open
    Took 2.240 seconds, 224.02620 microseconds / record
    sqlite3_file_open
    Took 7.119 seconds, 711.87410 microseconds / record

And here's what I got after adding the primary keys:

    sqlite
    Took 0.040 seconds, 3.97618 microseconds / record
    dict_open
    Took 0.002 seconds, 0.19641 microseconds / record
    dbm_open
    Took 0.042 seconds, 4.18961 microseconds / record
    sqlite3_mem_open
    Took 0.116 seconds, 11.58359 microseconds / record
    sqlite3_file_open
    Took 5.571 seconds, 557.13968 microseconds / record

My code is here: https://gist.github.com/simonw/019ddf08150178d49f4967cc38356...

Just to spell out the results: Adding the primary key appears to improve SQLite performance but still falls short of DBM.

Yeah now that I dig in a little further it looks like it's not as clear cut as I thought. DBM read performance is better for me with larger test cases than I was initially using, though I am getting some weird performance hiccups when I use very large test cases. It looks like I'm too late to edit my top level response to reflect that.

The index is the least of the issue with the SQLite implementation. It's calling one INSERT per each record in that version, so the benchmark is spending something like 99.8% of its time opening and closing transactions as it sets up the database.

Fixing that on my machine took the sqlite3_file_open benchmark from 16.910 seconds to 1.033 seconds. Adding the index brought it down to 0.040 seconds.

Also, I've never really dug into what's going on, but the dbm implementation is pretty slow on Windows, at least when I've tried to use it.

For use cases where you want a very simple key-value store, working with single records is probably a good test?

Maybe?

Sure, mutating data sets might be a useful use case. But, inserting thousands of items at once one at a time in a tight loop, then asking for all of them is testing an unusual use case in my opinion.

My point was that we're comparing apples and oranges. By default, I think, Python's dbm implementation doesn't do any sort of transaction or even sync after every insert, where as SQLite does have a pretty hefty atomic guarantee after each INSERT, so they're quite different actions.

I was working on a project to insert a billion rows in SQLite under a minute, batching the inserts made it crazy fast compared to individual transactions.

link: https://avi.im/blag/2021/fast-sqlite-inserts/

> Also, I've never really dug into what's going on, but the dbm implementation is pretty slow on Windows, at least when I've tried to use it.

Seems like this would be why: https://news.ycombinator.com/item?id=32852333

"the slow-but-simple implementation in module dbm.dumb will be used"

No kidding.

https://github.com/python/cpython/blob/main/Lib/dbm/dumb.py

It uses a text file to store keys and pointers into a binary file for values. It works .. most of the time .. but yeah, that's not going to win any speed awards.

Also you can use WAL

Reply on news.ycombinator.com