Inserting a billion rows in SQLite under a minute
1–10 of 164 posts
Re: Inserting a billion rows in SQLite under a minute
#2There are some cool ideas in there!
I wonder how much I could speed up my test suites (that don't rely on transaction rollbacks) by disabling journalling. Those milliseconds per query add up to seconds per tests and minutes per PR.
Re: Inserting a billion rows in SQLite under a minute
#3I wonder if defining a virtual table (https://www.sqlite.org/vtab.html) and just doing
INSERT INTO t (...) SELECT ... from virtual_table
would be any faster.
Re: Inserting a billion rows in SQLite under a minute
#4You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence).
We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.
Re: Inserting a billion rows in SQLite under a minute
#5I wonder if something like https://paulbradley.org/sqlite-test-data/ could be used to generate the test data in sqlite3 directly, and if it would be any faster.
(It would likely be faster for other DB engines, because there is network overhead in the communication between the program and the DB; no such things for sqlite).
Re: Inserting a billion rows in SQLite under a minute
#6If it's single, in memory table, is there really need to use database? Won't language provided data structures suffice?
Re: Inserting a billion rows in SQLite under a minute
#7You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence). We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.
Are you suggesting have a template and then updating rows with random values where necessary?
Re: Inserting a billion rows in SQLite under a minute
#8Is PRAGMA temp_store = MEMORY the same as putting the file on a TempFS?
Re: Inserting a billion rows in SQLite under a minute
#9If it's single, in memory table, is there really need to use database? Won't language provided data structures suffice?
It is a good question, but I'm guessing that the whole reason for creating the database in the first place is to test interactions with the database, in which case you'd have to mock up all the interactions being tested (selects, deletes, joins, etc.).
Re: Inserting a billion rows in SQLite under a minute
#10Creator of RediSQL / zeeSQL (https://zeesql.com/)
Insertion performance on a single table are very very hard to optimize.
A single process looping it is your best bet.
I would just increase the batch size, which is the most influent factor.
Then another point... When you do batches, you do
BEGIN TRANSACTION;
for i in range(1, 50):
execute_stmt
COMMIT;
https://github.com/avinassh/fast-sqlite3-inserts/blob/master...
;)