WJb Persistent Storage Benchmark: SQLite vs SQL Server

#ai #sql #csharp #dotnet

WJb Persistent Storage Benchmark: SQLite vs SQL Server

WJb is a lightweight, explicit workflow and background job engine for .NET. One of its key strengths is storage-agnostic design: jobs can be persisted through different providers while keeping the same programming model.

This article examines the Persistent Storage Benchmark from the WJb.Demo repository. The benchmark compares two commercial storage providers:

WJb.SQLite — embedded, file-based storage
WJb.SqlServer — full SQL Server / LocalDB storage

Benchmark Scenario

The test measures a complete enqueue → dequeue cycle:

Enqueue N jobs (each with a simple payload)
Dequeue every job until the queue is empty
Verify that the number of processed jobs matches the number enqueued

This is a pure storage throughput test. No real job execution logic is involved beyond the store operations themselves.

Code Overview

The benchmark is implemented with BenchmarkDotNet and targets .NET 10:

[SimpleJob(RuntimeMoniker.Net10_0)]
[MemoryDiagnoser]
public class PersistentStorageBenchmark
{
    [Params(1, 10, 100)]
    public int JobCount { get; set; }

    // Connection strings for SQLite and SQL Server (LocalDB)

    [GlobalSetup]
    public async Task GlobalSetup()
    {
        // Initialize both databases and clear existing jobs
    }

    [Benchmark(Baseline = true)]
    public async Task SQLite()
    {
        var store = new SqliteStore(() => new SqliteConnection(SqliteConnectionString));
        await RunAsync(store);
    }

    [Benchmark]
    public async Task SqlServer()
    {
        var store = new MsSqlStore(() => new SqlConnection(SqlServerConnectionString));
        await RunAsync(store);
    }

    private async Task RunAsync(IStore store)
    {
        for (int i = 0; i < JobCount; i++)
        {
            await store.EnqueueAsync("echo", new EchoPayload(i, $"User {i}"));
        }

        var processed = 0;
        while (true)
        {
            var job = await store.DequeueAsync();
            if (job is null) break;
            processed++;
        }

        if (processed != JobCount)
            throw new InvalidOperationException(...);
    }
}

Both providers are initialized once, the job tables are truncated/cleared before each run, and the same enqueue/dequeue logic is used.

Results

The following results were obtained on a 12th Gen Intel Core i7-12700K (3.60 GHz) using .NET 10:

Method	JobCount	Mean	Error	StdDev	Median	Ratio	Allocated
SQLite	1	12,244.8 µs	100.16 µs	78.20 µs	12,254.4 µs	1.00	9.84 KB
SqlServer	1	454.0 µs	7.11 µs	6.65 µs	452.6 µs	0.04	19.46 KB
SQLite	10	130,801.1 µs	2,582.78 µs	5,934.37 µs	129,952.9 µs	1.00	75.01 KB
SqlServer	10	3,438.7 µs	131.03 µs	386.34 µs	3,449.9 µs	0.03	139.7 KB
SQLite	100	1,420,633.0 µs	60,542.05 µs	178,509.61 µs	1,358,625.9 µs	1.00	728.21 KB
SqlServer	100	85,296.4 µs	9,039.37 µs	26,652.79 µs	84,720.8 µs	0.06	1,344.89 KB

Key observations:

SQL Server is dramatically faster across all job counts:
- ~27× faster for 1 job
- ~38× faster for 10 jobs
- ~16–17× faster for 100 jobs
Memory allocation is higher on SQL Server (roughly 1.85–1.98×), which is expected due to the richer client and protocol overhead.
SQLite shows higher variance at larger job counts, while SQL Server remains relatively consistent.

Interpretation

WJb.SqlServer delivers excellent throughput and is clearly the preferred choice for:

Server-side applications
Cloud / multi-instance deployments
High-throughput or multi-process workloads
Scenarios requiring concurrent access and strong consistency

WJb.SQLite remains a solid option when you need:

Zero external dependencies
Simple local / desktop / single-user applications
Easy distribution (single file database)
Development and testing environments

Conclusion

The benchmark demonstrates that WJb’s storage abstraction works well: the same application code can switch between providers with minimal changes, while the underlying performance characteristics differ significantly.

Choose WJb.SqlServer when performance and scalability matter.
Choose WJb.SQLite when simplicity and zero infrastructure overhead are more important.

You can find the complete benchmark source code here:

https://github.com/UkrGuru/WJb.Demo/tree/main/benchmark/Storage

For more information about WJb, visit https://wjb.pro.

DEV Community