Hey everyone! I am very new to SQLite and I am doing a little experiment where I am trying to figure out really fast way to insert rows in SQLite. I am fine with db corruptions, issues due to process crash / OS.
I have been able to insert **100M rows in 33 seconds** on my local machine. I was wondering if there are any further optimisations / tricks I can do.
I create a db with one table which has 3 columns. Then I randomly generate these 3 values and insert them. I found this excellent Stack Overflow [post](https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) and trying it out.
Here's what I have done so far:
1. PRAGMA settings:
PRAGMA journal_mode = OFF;
PRAGMA synchronous = 0;
PRAGMA cache_size = 1000000;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA temp_store = MEMORY;",
1. Wrap all insertions in a transaction
2. Use a prepared statement
3. Insert in batches: First I inserted each row one after another, it took about 60s. Then I did a batch insert (of 100 rows) with prepared statement it came down to 33 seconds.
If you would like to check my code:
1. the [rust version](https://github.com/avinassh/fast-sqlite3-inserts/blob/009694f3dc1e6dd9d6caa6f57a8d5fd9432f4f83/src/bin/basic_batched.rs), does 100M inserts in 33ish seconds.
2. almost similar [python version](https://github.com/avinassh/fast-sqlite3-inserts/blob/009694f3dc1e6dd9d6caa6f57a8d5fd9432f4f83/sqlite3_opt_batched.py), does 100M inserts in 150ish seconds using PyPy.
What else I can do to speed this even further?
1. Any PRAGMA settings I can tweak further?
2. Does doing insertions multithreaded mode can help? I am really not sure what should be my `journal_mode` and other settings should be if I want to use multithreading.
Appreciate any suggestions. Thank you =)
---
My machine details:
mac OS Big Sur, 2.4Ghz Quad Core Intel Core i5, 8GB RAM, 120GB SSD
I run using gnu time for measuring it:
/usr/bin/time bench_binary
33.98 real
30.40 user
2.58 sys