SQLite Forum

Fast way to insert rows in SQLite
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