4 ms·
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 litt
by cbdumas 4y ago
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.
- simonw 4y agoYeah 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/019ddf08150178d49f4967cc383563ea https://gist.github.com/simonw/019ddf08150178d49f4967cc38356...
- divbzero 4y agoJust to spell out the results: Adding the primary key appears to improve SQLite performance but still falls short of DBM.
- cbdumas 4y agoYeah 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.
- banana_giraffe 4y agoThe 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.
- Steltek 4y agoFor use cases where you want a very simple key-value store, working with single records is probably a good test?
- banana_giraffe 4y agoMaybe? 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.
- avinassh 4y agoI 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/ https://avi.im/blag/2021/fast-sqlite-inserts/
- deleted 4y ago[deleted]
- OGWhales 4y ago> 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 https://news.ycombinator.com/item?id=32852333