Microsoft.Data.Sqlite Mutlithread test

README.md

Thread:100 WAL:true Pool:true

TotalCount : 100000
TotalTime : 17869
TPS : 5596.28406737926
Select1 : 12659
Select2 : 12353
Insert1 : 12382
Insert2 : 12742
Update1 : 12723
Update2 : 12495
Delete1 : 12379
Delete2 : 12267
Total : 100000

Thread:100 WAL:true Pool:false

TotalCount : 100000
TotalTime : 30873
TPS : 3239.07621546335
Select1 : 12496
Select2 : 12588
Insert1 : 12514
Insert2 : 12468
Update1 : 12189
Update2 : 12640
Delete1 : 12513
Delete2 : 12592
Total : 100000

Thread:100 WAL:false Pool:true (Bad)

TotalCount : 100000
TotalTime : 62880
TPS : 1590.3307888040713
Select1 : 12449
Select2 : 12221
Insert1 : 12389
Insert2 : 12566
Update1 : 12435
Update2 : 12329
Delete1 : 12379
Delete2 : 12381
Total : 99149

Thread:100 WAL:false Pool:false (Bad)

TotalCount : 100000
TotalTime : 79465
TPS : 1258.4156546907443
Select1 : 11830
Select2 : 12275
Insert1 : 12168
Insert2 : 12071
Update1 : 12182
Update2 : 12129
Delete1 : 12178
Delete2 : 12227
Total : 97060

Thread:100 WAL:false Pool:true Cache:true

TotalCount : 100000
TotalTime : 63517
TPS : 1574.3816616024055
Select1 : 12636
Select2 : 12504
Insert1 : 12437
Insert2 : 12421
Update1 : 12523
Update2 : 12594
Delete1 : 12436
Delete2 : 12449
Total : 100000

Thread:100 WAL:false Pool:false Cache:true

TotalCount : 100000
TotalTime : 68486
TPS : 1460.1524399147272
Select1 : 12455
Select2 : 12500
Insert1 : 12561
Insert2 : 12516
Update1 : 12484
Update2 : 12432
Delete1 : 12564
Delete2 : 12488
Total : 100000

Program.cs

using System.CommandLine;
using System.CommandLine.NamingConventionBinder;
using System.Diagnostics;

using Microsoft.Data.Sqlite;

using Smart.Data.Mapper;

#pragma warning disable CA1812

var rootCommand = new RootCommand("SQLite benchmark");
rootCommand.AddOption(new Option<int>(new[] { "--thread", "-t" }, () => 100, "thread"));
rootCommand.AddOption(new Option<int>(new[] { "--loop", "-l" }, () => 1000, "loop"));
rootCommand.AddOption(new Option<bool>(new[] { "--wal", "-w" }, () => true, "wal"));
rootCommand.AddOption(new Option<bool>(new[] { "--pool", "-p" }, () => true, "pool"));
rootCommand.AddOption(new Option<bool>(new[] { "--shared", "-s" }, () => true, "shared"));
rootCommand.Handler = CommandHandler.Create(async (IConsole console, int thread, int loop, bool wal, bool pool, bool shared) =>
{
    File.Delete("Test.db");
    var builder = new SqliteConnectionStringBuilder
    {
        DataSource = "Test.db",
        Pooling = pool
    };
    if (shared)
    {
        builder.Cache = SqliteCacheMode.Shared;
    }
    var connectionString = builder.ConnectionString;

#pragma warning disable CA2007
    await using var db = new SqliteConnection(connectionString);
#pragma warning restore CA2007

    await db.ExecuteAsync("CREATE TABLE Data (Id INTEGER NOT NULL, Name TEXT NOT NULL, PRIMARY KEY(Id))").ConfigureAwait(false);

    for (var i = 0; i < thread / 2; i++)
    {
        await db.ExecuteAsync("INSERT INTO Data VALUES (@Id, @Name)", new Data { Id = i, Name = $"Data-{i}" }).ConfigureAwait(false);
    }

    var select1 = new Counter();
    var select2 = new Counter();
    var insert1 = new Counter();
    var insert2 = new Counter();
    var update1 = new Counter();
    var update2 = new Counter();
    var delete1 = new Counter();
    var delete2 = new Counter();

    var tasks = new List<Task>();
    for (var i = 0; i < thread; i++)
    {
        tasks.Add(Task.Run(async () =>
        {
            var rand = new Random();

            for (var j = 0; j < loop; j++)
            {
#pragma warning disable CA2007
                await using var con = new SqliteConnection(connectionString);
#pragma warning restore CA2007
                await con.OpenAsync().ConfigureAwait(false);
                if (wal)
                {
                    await con.ExecuteAsync("PRAGMA journal_mode=WAL").ConfigureAwait(false);
                }

#pragma warning disable CA5394
                var id = rand.Next(100);
                switch (rand.Next(4))
                {
                    case 0:
                        var entity = await con.QueryFirstOrDefaultAsync<Data>("SELECT * FROM Data WHERE Id = @Id", new { Id = id }).ConfigureAwait(false);
                        if (entity is not null)
                        {
                            select1.Increment();
                        }
                        else
                        {
                            select2.Increment();
                        }
                        break;
                    case 1:
                        try
                        {
                            await con.ExecuteAsync("INSERT INTO Data VALUES (@Id, @Name)", new Data { Id = id, Name = $"Data-{id}" }).ConfigureAwait(false);
                            insert1.Increment();
                        }
                        catch (SqliteException e)
                        {
                            if (e.SqliteErrorCode != 19)
                            {
                                throw;
                            }
                            insert2.Increment();
                        }
                        break;
                    case 2:
                        var updated = await con.ExecuteAsync("UPDATE Data SET Name = @Name WHERE Id = @Id", new { Id = id, Name = $"Updated-{id}" }).ConfigureAwait(false);
                        if (updated == 1)
                        {
                            update1.Increment();
                        }
                        else
                        {
                            update2.Increment();
                        }
                        break;
                    case 3:
                        var deleted = await con.ExecuteAsync("DELETE FROM Data WHERE Id = @Id", new { Id = id }).ConfigureAwait(false);
                        if (deleted == 1)
                        {
                            delete1.Increment();
                        }
                        else
                        {
                            delete2.Increment();
                        }
                        break;
                }
#pragma warning restore CA5394
            }
        }));
    }

    var watch = Stopwatch.StartNew();

    // 完了待ち
#pragma warning disable CA1031
    try
    {
#pragma warning disable CA1849
        Task.WaitAll(tasks.ToArray());
#pragma warning restore CA1849
    }
    catch (Exception e)
    {
        console.WriteLine(e.ToString());
    }
#pragma warning restore CA1031

    var total = thread * loop;
    console.WriteLine($"TotalCount : {total}");
    console.WriteLine($"TotalTime : {watch.ElapsedMilliseconds}");
    console.WriteLine($"TPS : {(double)total / watch.ElapsedMilliseconds * 1000}");
    console.WriteLine($"Select1 : {select1.Value}");
    console.WriteLine($"Select2 : {select2.Value}");
    console.WriteLine($"Insert1 : {insert1.Value}");
    console.WriteLine($"Insert2 : {insert2.Value}");
    console.WriteLine($"Update1 : {update1.Value}");
    console.WriteLine($"Update2 : {update2.Value}");
    console.WriteLine($"Delete1 : {delete1.Value}");
    console.WriteLine($"Delete2 : {delete2.Value}");
    console.WriteLine($"Total : {select1.Value + select2.Value + insert1.Value + insert2.Value + update1.Value + update2.Value + delete1.Value + delete2.Value}");
});

return await rootCommand.InvokeAsync(args).ConfigureAwait(false);

#pragma warning disable CA1050
public class Data
{
    public int Id { get; set; }

    public string Name { get; set; } = default!;
}

public class Counter
{
    private readonly object sync = new();

    private int value;

    public int Value
    {
        get
        {
            lock (sync)
            {
                return value;
            }
        }
    }

    public void Increment()
    {
        lock (sync)
        {
            value++;
        }
    }
}
#pragma warning restore CA1050