Inserting a billion rows in SQLite under a minute

Another crazy idea is to learn about SQLite file format and just write the pages to disk.

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.

For most SQL systems, the fastest way to do inserts is always just going to batched inserts. There's maybe some extra tricks to reduce network costs/optimize batches [0], but at it's core you are still essentially inserting into the table through the normal insert path. You can basically then only try and reduce the amount of work done on the DB side per insert, or optimize your OS for your workload.

Some other DB systems (more common in NoSQL) let you actually do real bulk loads [1] where you are writing direct(ish) database files and actually bypassing much of the normal write path.

[0] https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.... [1] https://blog.cloudera.com/how-to-use-hbase-bulk-loading-and-...

Faster by 10% than fastest author implementation on my machine - 19 seconds against 21 for 'threaded_batched'.

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...

;)

https://www.sqlite.org/csv.html

I was actually just working on SQLite speed for Objective-S (http://objective.st), partly as a driver for getting some of the more glaring inefficiencies out.

Using a "to do list" schema, I currently get the 100M rows out in 56 seconds, which is around half the speed of the Rust example given here, but 3 times the speed of PyPy and almost 10x faster than Python.

This is from an interpreted script that not only does the inserts and creates the objects to insert in the first place, but also defines the actual class.

The lower-level code is written in Objective-C, like the rest of Objective-S.

Class definition:

    class Task {
      var <int> id.
      var <bool> done.
      var <NSString> title.
      -description { "<Task: title: {this:title} done: {this:done}>". }
      +sqlForCreate {
          '( [id] INTEGER PRIMARY KEY, [title] NVARCHAR(220) NOT NULL, [done] INTEGER );'.
      }
    }.

     1 to:10 do: {
         this:tasksTable insert:taskList.
     }.

At billion rows, that is 18GB of data. With some overhead for storing page info, let’s call it 20GB flat.

A modern SSD can deliver ~500MB/s write speed. That means writing 20GB of data can be done in 40 seconds.

Therefore a billion rows in a minute is quite plausible. At-least not bottlenecked by disk speed (if we can saturate disk).

Do that cartesian join three more times, and you have over 4 billion results.

Then you simply need to use the random function in conjunction with division, rounding and case statements to get the desired random numbers.

Prepared statements should be considered the norm for SQLite - they have pretty major performance benefits, and any decent ORM or query engine can probably do it for you implicitly or with a single flag, so it's practically free for many applications. I'm always surprised how often I see applications or benchmarks or "how to be fast with SQLite" not using them, so I'm definitely glad to see them covered here :)

- language has very little to do since the bottleneck will most likely be the way you insert data

- indeed prepared statements are useful but the performance didn't change much when I did long transactions and commit every certain amount of thousand of rows

- having lots of rows in your table is good but certain queries, like aggregation over many rows, are not what SQLite is great about.

- ClickHouse can easily ingest that and more in a laptop without even any scripting language.

      /fast-sqlite3-inserts (master)> time make busy-rust
      
      Sun Jul 18 17:04:59 UTC 2021 [RUST] busy.rs (100_000_000) iterations
      
      real 0m9.816s
      user 0m9.380s
      sys 0m0.433s
      
      ________________________________________________________
      Executed in    9.92 secs    fish           external
      usr time    9.43 secs    0.20 millis    9.43 secs
      sys time    0.47 secs    1.07 millis    0.47 secs
      
      
      fast-sqlite3-inserts (master)> time make busy-rust-thread
      
      Sun Jul 18 17:04:48 UTC 2021 [RUST] threaded_busy.rs (100_000_000) iterations
      
      real 0m2.104s
      user 0m13.640s
      sys 0m0.724s
      
      ________________________________________________________
      Executed in    2.33 secs    fish           external
      usr time   13.68 secs    0.20 millis   13.68 secs
      sys time    0.78 secs    1.18 millis    0.78 secs

This is on a M1 MacBook Air.

  pragma temp_store = memory;

[0] https://sqlite.org/pragma.html#pragma_temp_store

# createSQL.tcl

set increment 100000

set loop [expr {100000000 / $increment}]

puts {PRAGMA journal_mode = OFF; PRAGMA synchronous = 0; PRAGMA cache_size = 1000000; PRAGMA locking_mode = EXCLUSIVE; PRAGMA temp_store = MEMORY;

CREATE TABLE user( pk INTEGER PRIMARY KEY, area INTEGER, age INTEGER, active INTEGER );}

for {set i 0} {$i < $loop} {incr i} { puts " WITH RECURSIVE tmp(pk,area,age,active) AS ( SELECT [expr {$i * $increment}], 500001, 5, 0 UNION ALL SELECT pk+1, 10000*(abs(random())%9+1)+abs(random())%100000, 5*(abs(random()%3)+1), abs(random()%2) FROM tmp WHERE pk < [expr {($i+1)*$increment-1}] ) INSERT INTO user SELECT * FROM tmp;" }

#EOF

Core 2 Duo 2.53 GHz, 4GB RAM, SSD:

$ time -p tclsh createsql.tcl | sqlite3 test.db

off

exclusive

real 213.37

user 208.10

sys 4.58

1: is it useful / feasible to set a sort of "lower bound" to these optimizations by profiling the raw write time on a set of a certain size?

2. assuming that writes are ultimately the limiting factor, could you gain performance by calculating the minimum batch size as it's write time intersects in-memory calculation, and as soon as you hit that size, pop a thread to write that batch to disk - then return some async signal when the write is done to trigger the next batch of writes? is it possible to "stitch together" these batches post-writes?

edit: mobile formatting is le suck

INSERT INTO t (...) SELECT ... from virtual_table

would be any faster.

The source code can be found here: https://github.com/wuxb45/remixdb/blob/master/i100m.c

    $ make i100m.out libremixdb.so
    $ numactl -N 0 ./i100m.out
    insert time without sync: 24.865s, with sync: 25.801s

That is about 6 million combinations. This is not such a big space that it would be impractical to precompute all possible combinations. I wonder if hashing in to such a precomputed table might help. Might not if the overhead of maintaining the lookup table is too high.

I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between SQLite and a "real" DB engine?

(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).

Lastly, how long does it take to make a copy of an already prepared 1b row SQLite file?that seems easier than generating a new one.

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.

This is actually a little bit mind blowing for me. I'm gonna go and play with this. But what a cool read!

But the results given are all for inserting 0.1B rows, not 1B rows. What are the full results?

Also interesting whether batched numpy version would compare better to Rust.

PRAGMA locking_mode = EXCLUSIVE;

improve performance much?

Worth it.

>I could just run s the script again.

Found a typo.

The author must have a rather low opinion of programmers... generating data with a script is good for 1,000 rows. Maybe 10,000 rows. 100,000 is probably stretching it. Beyond that the slowness will be meaningful.

Anyway, if the "test database" data is given in advance, you want an in-memory DBMS which can load data from files. If it isn't - use an in-memory DBMS which supports generated columns, or apply your random-number-generating function on a trivial column.

(MonetDB standalone/embedded would be a decent option; maybe the columnar storage mechanism in MariaDB? And of course there are commercial offerings.)

> Unfortunately, it was slow. Really slow. So I did what any programmer would do: went down the rabbit hole of learning more about SQLite...

That's the wrong approach. SQLite isn't called "lite" for nothing. It's not what you would use for bulk operations on large tables.

Read the SQLite FAQ: https://www.sqlite.org/whentouse.html

it oversells itself a bit, but still says its (only) advantages for data analysis are: "easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed."