15k inserts/s with Rust and SQLite (2021)

100k inserts using `better-sqlite3` (difference: it does use prepared statements), all single-threaded with the same pragmas.

- 8.6k inserts/s with `uuid` and `Date.toUTCString()`

- 12.0k inserts/s with faking uuid and date (generated unique strings)

...So a large amount of time was spend in uuid generation and current date.

EDIT: I mixed up the two numbers above. Simple strings is of course faster than syscall+date format+entropy. Fixed now.

It's fair to say that although Rust is slightly more Web Scale, this benchmark simply measures the excellent performance of SQLite.

Now do it while simultaneously serving 15k selects/second.

Now try it with 15k counts/second. You'll see where this breaks down fast.

  Is it possible to improve this micro benchmark? Of course, by bundling all the inserts in a single transaction, for example, or by using another, non-async database driver, but it does not make sense as it's not how a real-world codebase accessing a database looks like. We favor simplicity over theorical numbers.

Nothing theoretical or not-real-world about it. It is how SQLite docs recommend you do it.

https://www.sqlite.org/faq.html#q19

https://rogerbinns.github.io/apsw/tips.html#sqlite-is-differ...

And what magical potion Rust adds to this result?

And I didn't have to do any fancy trick like in this article, just setting `journal_mode=WAL` and `synchronous=normal` was enough.

p/s: yeah transaction helps a lot.

    sqlite> create table users (id blob primary key not null, created_at text not null, username text not null);
    sqlite> create unique index idx_users_on_id on users(id);
    sqlite> pragma journal_mode=wal;
    sqlite> .load '/tmp/uuid.c.so'
    sqlite> .timer on
    sqlite> insert into users(id, created_at, username)
            select uuid(), strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello'
            from generate_series limit 100000;
    Run Time: real 1.159 user 0.572631 sys 0.442133

In the interest of finding where the bulk of the time is spent and on a hunch I tried swapping the UUID for plain auto-incrementing primary keys as well:

    sqlite> insert into users(created_at, username) select strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello' from generate_series limit 100000;
    Run Time: real 0.142 user 0.068090 sys 0.025507

[0]: https://news.ycombinator.com/item?id=27872575

[1]: https://idle.nprescott.com/2021/bulk-data-generation-in-sqli...

[2]: https://sqlite.org/src/file/ext/misc/uuid.c

Here is a totally unscientific benchmark in C++. Of course there is "lies, damned lies and benchmarks".

AMD Ryzen 9 5900X 12-Core Processor

./sqlitetest

For writing to disk: Inserted 1500000 records in 13 seconds, at a rate of 115385 records per second.

For writing to RAM: change users.db to :memory: Inserted 1500000 records in 9 seconds, at a rate of 166667 records per second.

Credit goes not to me but to everyone's favorite AI programmer.

I've made a tiny attempt to optimise - likely much more can be done.

To compile on Linux:

g++ sqlitetest.cpp -lsqlite3 -luuid -o sqlitetest

    #include <iostream>
    #include <cstring>
    #include <ctime>
    #include <chrono>
    #include <sqlite3.h>
    #include <uuid/uuid.h>
    
    struct User {
        uuid_t id;
        char created_at[25];
        char username[6];
    };
    
    int main() {
        sqlite3 *db;
        sqlite3_open(":memory:", &db);
    
        const char *sql = "CREATE TABLE IF NOT EXISTS users ("
                          "id BLOB PRIMARY KEY NOT NULL,"
                          "created_at TEXT NOT NULL,"
                          "username TEXT NOT NULL"
                          ");";
        sqlite3_exec(db, sql, nullptr, nullptr, nullptr);
    
        sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr);
    
        User user;
        char uuid_str[37];
        time_t now = time(nullptr);
    
        int count = 0;
        auto start = std::chrono::steady_clock::now();
    
        for (int i = 0; i < 1500000; i++) {
            uuid_generate(user.id);
            uuid_unparse_lower(user.id, uuid_str);
            strftime(user.created_at, sizeof(user.created_at), "%Y-%m-%d %H:%M:%S", localtime(&now));
            strncpy(user.username, "Hello", sizeof(user.username));
            sqlite3_stmt *stmt;
            sqlite3_prepare_v2(db, "INSERT INTO users (id, created_at, username) VALUES (?, ?, ?);", -1, &stmt, nullptr);
            sqlite3_bind_blob(stmt, 1, user.id, sizeof(user.id), SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, user.created_at, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, user.username, -1, SQLITE_STATIC);
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
            count++;
        }
    
        sqlite3_exec(db, "COMMIT", nullptr, nullptr, nullptr);
    
        auto end = std::chrono::steady_clock::now();
        auto elapsed = std::chrono::duration_cast<std::chrono::seconds>(end - start).count();
        double rate = static_cast<double>(count) / elapsed;
    
        std::cout << "Inserted " << count << " records in " << elapsed << " seconds, at a rate of " << rate << " records per second." << std::endl;
    
        sqlite3_close(db);
    
        return 0;
    }

I was inserting data at 5MB/s using MySQL and C++ some years ago.

MySQL was the bottleneck, the C++ part could potentially run many times faster.