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 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;",
   

2. Wrap all insertions in a transaction

3. Use a prepared statement

4. 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, does 100M inserts in 33ish seconds.
2. almost similar python version, 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

Beside playing with the page_size, there isn't much that can be improved.

The default is 4KB pages these days, and you might gain a little with 8KB
and 16KB at insertion time, at the expense of more IO later on updates and
deletes, so there's a balance to find specific to what you are doing.

Note that you cache_size is affected by the page_size, unless you switch to
a negative value, for an absolute value in KBs. See pragma_cache_size

Multi-threading won't help at all on the SQLite side. But you can of course
prepare all your batches in a separate thread, and use an SPSC queue to pass
those batches around to the SQLite thread, to at least get some concurrency
between the SQLite code, and your own code.

(But you probably want your queue to be fixed-size and blocking when full,
to throttle the producer-side, which is going to be (much?) faster than the
SQLite consumer side actually doing the IO)

Maybe others will have further suggestions. --DD

The default is 4KB pages these days, and you might gain a little with 8KB and 16KB at insertion time, at the expense of more IO later on updates and deletes, so there's a balance to find specific to what you are doing.

I tried increasing it to 8KB, I didn't see much difference. Let me try again with 8KB and 16KB and report here.

Multi-threading won't help at all on the SQLite side. But you can of course prepare all your batches in a separate thread, and use an SPSC queue to pass those batches around to the SQLite thread, to at least get some concurrency between the SQLite code, and your own code.

I tried this, but went with MPSC. The numbers are almost similar as non-threaded:

   34.53 real
   45.11 user
   4.47 sys

(It could be very much possible that I am doing something wrong, I am new to Rust as well. Linked tthe code at the end)

(But you probably want your queue to be fixed-size and blocking when full, to throttle the producer-side, which is going to be (much?) faster than the SQLite consumer side actually doing the IO)

I used the unbounded channel, so producer won't block on it. Can you tell me what difference it would make?

here is my code.

I tried increasing it to 8KB, I didn't see much difference. Let me try again with 8KB and 16KB and report here.

Writing it out to an in-memory db, provided you have enough RAM, will tell you your theoretical maximum speed - writing it to persistent storage faster than that won't be possible. You could use such a test to estimate the approximate performance cost/overhead of the storage layer.

This is such a great and clever idea! How come I didn't think of this!

So, I took my best performing code, just updated the file path to :memory:. It took almost same time:

33.08 real
31.58 user
0.72 sys

So I guess this is the best I could do.

16KB was faster for me, but your row data is tiny, mine was bigger (strings and blobs, up to 1MB per row).

If you measured the time the producing side takes,
versus the consuming (SQLite) side, you'll see the majority is on the SQLite side (> 99% I suspect).

So creating many producers (1-per CPU) to parallelize the 1% or less of the total, is not going to help much. A single producer is enough.

Plus having many producers increases contention too.

W/o using a fixed-size queue, and having a producer much faster than the consumer side, you are actually accumulating too much memory in the queue, increasing allocation, etc...
A fixed-size queue blocks the producer when the queue is full, and wakes it up only after the consumer processed one-or-more items.

Measure peak RAM, in addition to wall time. With a fixed-size queue, the peak RAM should be tiny.
While w/o one, I expect the peak RAM to be much larger, close to the full data side in your case, the producing side being so much faster.

In a real world scenario, there's not such an imbalance between what SQLite must do, and what the rest of the code must do,
so the SPSC approach on two threads works fine, up to a maximum of 2x faster in the perfect case.
But it can't be lower than max(producer, consumer) so if your consumer is already at 99%, at most you save 1%...

At over 3M rows per seconds, you're already very fast. Can't get any faster IMHO.

Stephan's suggestion to use an in-memory DB is also a good one.
Will give you the maximum throughput w/o the vagaries of the filesystem.

Thank you! Your message includes lots of important learnings for me :)

What is the schema of the table you are inserting into? If you are able to omit UNIQUE constraints and PRIMARY KEYs (other than INTEGER PRIMARY KEY), and use "CREATE UNIQUE INDEX" statements that happen after the insert in place of UNIQUE constraints, that will probably work faster.

Here's the DDL from the OP's Rust code linked above:

        "CREATE TABLE IF NOT EXISTS user (
                id INTEGER not null primary key,
                area CHAR(6),
                age INTEGER not null,
                active INTEGER not null)",

Thanks for posting here. And yes, I confirm that I am not creating any indexes other than the primary key one

How does WITHOUT ROWID affect performance?

WITHOUT ROWID simply uses another typically heavier Index as the primary key in stead of the rowid. Unless this index is also an INTEGER PK it is guaranteed to be slower, and if it is an Integer PK, it should be close to the same speed as the row id, but possibly still marginally slower.

The WITHOUT ROWID optimization is really a space-saver where another PK must be present anyway. It should not be any faster[1].

Put it like this: If that was faster, the devs should immediately implement that as the new row_id mechanism!

[1] Note: If the without rowid optimization replaces a previous duo of row_id + PK to now only have the PK, then it is bound to be faster, but simply replacing a rowid with a previously non-existing PK, can never be faster.

Hey, it does help in the case of SQLite. Here is what I did,

1. Without batching:

   I called INSERT INTO user VALUES (NULL,?,?,?); for 100M and the whole thing took 60s:

   59.64 real
   57.01 user
   2.27 sys
   

   The code is here

2. With batching:

   I batched, did inserts of 50 rows at once, INSERT INTO user VALUES (NULL,?,?,?) (NULL,?,?,?) x50 in loop of 100M/50, it took 34s:

   34.04 real
   31.66 user
   2.20 sys
   

   The code is here

I also tried increasing the batch size to 100, it was almost same and at 500 it took more time.

https://docs.python.org/3/library/sqlite3.html

The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement
(i.e. INSERT/UPDATE/DELETE/REPLACE).
[...]
Changed in version 3.6: sqlite3 used to implicitly commit an
open transaction before DDL statements. This is no longer the
case.

Given the above, I'm not sure whether you are correctly controlling your transactions. I would be explicitly issue both BEGIN and COMMIT commands. But I don't know enough about Python to understand the implications.

Not at all sure that this is your problem, but it might help you figure out why Python is so much slower than Rust.

For the Python library, adding in "isolation_level = None" in the connection call gets rid of all the automatic transaction handling and leaves it in autocommit mode/full manual transaction control.

This isn't an answer to your question, but if the data is already in some other container exposing it through a virtual table can eliminate the need to do an insert.

Unfortunately the data doesn't exist. However, this does give me a reason to experiment more using virtual tables. I shall do that later :D

I ran another experiment, to see if there would be any perf boost if I generate the pk instead of SQLite.

1. When I let SQLite generate PK, it took 60s:

   59.99 real
   56.83 user
   2.33 sys
   

   code here

2. When I generated it in the code, it took 75s, almost 15s extra!:

   76.12 real
   71.95 user
   2.71 sys
   

   code here

Since SQLite makes a function call I thought providing pk may be turn out faster. How wrong I was!

Of course it takes longer if you specify the rowid.

When the rowid is automatically generated it is simply the one greater than the last one present, which is obtained simply by going to the tail of the b-tree (following all the right-hand pointers to the end of the line) and adding one.

If you specify the rowid, then your provided rowid must be looked up in a separate seek operation to decide what to do (succeed or have an abortion).

An extra lookup in the sqlite_sequence table is only required if you used the AUTOINCREMENT keyword, which will make the process even longer.

"CREATE TABLE IF NOT EXISTS user ( id INTEGER not null primary key, area CHAR(6), age INTEGER not null, active INTEGER not null)"

Depending on the id value, its storage might be around 7 bytes.

Assuming ages are in 0 to 127, and active values are 0 or 1 the SQLite storage for those is two or three bytes (1 byte per column "overhead", and another byte holds age when it is more than 1)

You are probably looking at around 17 bytes per row if length(area) is typically 6 bytes (look at your database disk size after you've inserted a million records to see if I'm right).

You could probably save 2 bytes per row by using a BLOB to hold all of the non-key information. The blob could hold age and active in a single byte, and its remaining bytes would hold area.

CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, info BLOB);

You could create a VIEW that extracts value in your preferred multi-column form (and the VIEW could have triggers that support inserts and updates in the multi-column form, but those triggers would probably not be as fast as building the BLOB in your application).

Saving 2 bytes out of 17 might give you a 10% or more speed boost.

Actually, I would need the columns so that I can query them later. But I will experiment with BLOB as well

If you create a module and use a virtual table that just link to your data in memory, then copy this data to your table, you can drastically improve insert speed.

Can't come close to 3.3 million per second! Get only 400,000 per second (which I thought was fast.)

You have to also think in terms of throughput, not just rows/sec.

You can have inflated rows/sec numbers with tiny rows. The OP's is in the 16-bytes range, which is quite small.

Mines is more in the 100-bytes range. And there, I reach 800K rows/sec.
You cannot scale linearly of course, but that's still around 6x more content.
That's of course made-up data, unrealistic data, so not really a real-world use-case.
But in the same ballpark as the OP IMHO.