Re:Many small queries are efficient in SQLite
by iveqy on Tuesday February 16, 2021 @04:00AM
(#26152189)
No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
Re:Many small queries are efficient in SQLite
by Multicomp on Tuesday February 16, 2021 @07:02AM
(#26153125)
We both learned something new today. Looks like this is what you want in combination with using an in memory database.
I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology!
Re:Many small queries are efficient in SQLite
by beagle3 on Tuesday February 16, 2021 @08:26AM
(#26153612)
In Python you just have to open the special file name “:memory:” to get a memory-based db. I don’t remember what the raw SQLite incantation is (or if it’s different). Also, pay attention to “ATTACH” - it’s the way to use multiple databases (file and/or memory) while still letting SQLite handle it all (e.g. join a memory db to a file db, insert result into 3rd file db - all without having to look at records in your own code)
Re:Many small queries are efficient in SQLite
by folmar on Tuesday February 16, 2021 @10:17AM
(#26154612)
It's the same in plain sqlite.
Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want!
Sqlite is amazing when you don't expect the DB to do replication or failover on its own.