MarlinBits.SqliteBatchOps 0.1.0

MarlinBits.SqliteBatchOps

Fast insert/update/delete for Microsoft.Data.Sqlite.

This library can execute 30x more commands (inserts/deletes/updates) per second compared to execution of individual commands. But it has ~55ms overhead for individual commands.

What?

MarlinBits.SqliteBatchOps has batch command executor that is using Dapper to execute SQL statements in single transaction for faster execution.

Why?

SQLite is very slow with opening and closing transactions. You can perform 200 inserts per second without transaction or hundreds of thousands of inserts per second with a few transactions.

If you don't specify a transaction SQLite will use new transaction for each SQL write statement (including deletes and updates). Since SQLite can have only one writer, it is a bottleneck.

This library is using Dapper to transparently execute modifying operations in batches to speed up inserts, updates and deletes.

How it works?

For each SQLite db your application use, you can create an instance of BatchOps (BatchOpsFactory.GetBatchOps). It will use a thread-synced queue in the background and trigger writes every 50,000 operations (configurable) or every 50 ms (configurable), whichever comes first.

Use BatchOpsFactory to ensure single instance of BatchOps per database/connection string. When using with IoC container register BatchOpsFactory as a singleton.

How to use it?

Reference nuget package MarlinBits.SqliteBatchOps:

> dotnet add package MarlinBits.SqliteBatchOps

using Dapper;
using MarlinBits.SqliteBatchOps;
using Microsoft.Data.Sqlite;

namespace MarlinBatchOpsExample;

public class Program
{
    private const string ConnectionString = "Data Source=my-db.sqlite";
    private readonly BatchOpsFactory _factory = new(); //!!! Register as singleton in web app
    private readonly BatchOps _batchOps;

    // Even though it makes no sense to use BatchOps in single client mode
    // this code shows basic functions of the library

    public Program()
    {
        _factory.SetSettingsForBatchOps(ConnectionString, new BatchOpsSettings
        {
            UseWriteAheadLogging = true
        });

        _batchOps = _factory.GetBatchOps(ConnectionString);
    }

    public static async Task Main()
    {
        Program p = new();
        await p.RunAsync();
    }

    private async Task RunAsync()
    {
        CreateTable();

        await _batchOps.ExecuteAsync("INSERT INTO T1(Val1, Val2) VALUES (@val1, @val2)", new
        {
            val1 = 1,
            val2 = "1"
        });

        await _batchOps.ExecuteAsync("INSERT INTO T1(Val1, Val2) VALUES (@val1, @val2)", new
        {
            val1 = 2,
            val2 = "2"
        });

        await using SqliteConnection c = new(ConnectionString);

        int count = c.QuerySingle<int>("SELECT COUNT(1) FROM T1");

        Console.WriteLine($"T1 row count: {count}");
    }

    private void CreateTable()
    {
        const string sqlCreateTable =
            """
            CREATE TABLE IF NOT EXISTS T1 (Val1 INTEGER, Val2 TEXT)
            """;

        using SqliteConnection c = new(ConnectionString);
        c.Execute("PRAGMA journal_mode = WAL");
        c.Execute(sqlCreateTable);

        Console.WriteLine("Table created (if it didn't exist already)");
    }
}

Downsides

This library has an overhead of ~50 ms by default, it can be lowered to ~25 ms. It isn't suitable for single client applications (mobile, desktop). For those types of application use regular transactions when inserting/deleting/updating in batch.

It is highly recommended to use it in WAL mode (see BatchOpsSettings).

Only async operations are supported. Tasks are used as callback functions and there is no way to achieve the same with sync operation.

SQL statements with errors will slow down other statements (see last four benchmark tables). This shouldn't be a real life problem if application is tested properly.

Benchmarks using NVMe drive and WAL mode

INSERT INTO T1 (Val1 /*INTEGER*/, Val2 /*TEXT*/) VALUES (@val1, @val2)

Performance gains:

Batch operations take consistently around 60ms, better results may be achieved with different BatchOpsSettings (MillisecondsWait property, default value is 50).

Below are results of batch inserts only, because running more than 1000 discrete inserts is very slow (even in WAL mode).

MillisecondsWait property set to 50:

MillisecondsWait property set to 10:

Somewhat better results are achieved with lowering wait time, but not significantly.

Percentage of error statements and how it affect performance:

All benchmarks are executed using BenchmarkDotNet

BenchmarkDotNet v0.14.0, Windows 11 (10.0.26100.3775)
AMD Ryzen 5 5600X, 1 CPU, 12 logical and 6 physical cores
.NET SDK 9.0.201
  [Host]     : .NET 9.0.3 (9.0.325.11113), X64 RyuJIT AVX2
  Job-VOMVFZ : .NET 9.0.3 (9.0.325.11113), X64 RyuJIT AVX2

When not to use the library

Looking at the tables above it is clear that this library should not be used for databases with low number of concurrent commands. In other words, don't use it for single client applications (like mobile or desktop applications). Also this library is not suitable for internal web applications with low number of user, or low number of writes.

When to use the library

If you are developing web application (SSR/API) with burst write loads (where you expect to exceed more than 50 writes per second) I would recommend to use the library, and only in WAL mode.