Dapper Plus - Bulk Insert Benchmark on SQL Server & SQLite

Bench-result.md

                                                     SQL Server
╭──────────────────────┬──────────┬─────────────┬───────────────────────────┬────────────────────────────────────────╮
│ Action               │ Entities │ Performance │ TimeFaster                │ ReducedPercent                         │
├──────────────────────┼──────────┼─────────────┼───────────────────────────┼────────────────────────────────────────┤
│ Dapper               │ 2000     │ 1491 ms     │ N/A                       │ N/A                                    │
│ Dapper (Transaction) │ 2000     │ 1370 ms     │ 1.09x faster than Dapper  │ Time reduced by 8% compared to Dapper  │
│ Dapper Plus          │ 2000     │ 63 ms       │ 23.67x faster than Dapper │ Time reduced by 96% compared to Dapper │
╰──────────────────────┴──────────┴─────────────┴───────────────────────────┴────────────────────────────────────────╯
                                                   SQLite :memory:
╭──────────────────────┬──────────┬─────────────┬──────────────────────────┬────────────────────────────────────────╮
│ Action               │ Entities │ Performance │ TimeFaster               │ ReducedPercent                         │
├──────────────────────┼──────────┼─────────────┼──────────────────────────┼────────────────────────────────────────┤
│ Dapper               │ 2000     │ 32 ms       │ N/A                      │ N/A                                    │
│ Dapper (Transaction) │ 2000     │ 26 ms       │ 1.23x faster than Dapper │ Time reduced by 19% compared to Dapper │
│ Dapper Plus          │ 2000     │ 32 ms       │ 1x faster than Dapper    │ Time reduced by 0% compared to Dapper  │
╰──────────────────────┴──────────┴─────────────┴──────────────────────────┴────────────────────────────────────────╯
                                                       SQLite file
╭──────────────────────┬──────────┬─────────────┬─────────────────────────────┬─────────────────────────────────────────╮
│ Action               │ Entities │ Performance │ TimeFaster                  │ ReducedPercent                          │
├──────────────────────┼──────────┼─────────────┼─────────────────────────────┼─────────────────────────────────────────┤
│ Dapper               │ 2000     │ 24581 ms    │ N/A                         │ N/A                                     │
│ Dapper (Transaction) │ 2000     │ 24 ms       │ 1024.21x faster than Dapper │ Time reduced by 100% compared to Dapper │
│ Dapper Plus          │ 2000     │ 53 ms       │ 463.79x faster than Dapper  │ Time reduced by 100% compared to Dapper │
╰──────────────────────┴──────────┴─────────────┴─────────────────────────────┴─────────────────────────────────────────╯

The results show that Dapper Plus's superior performance is largely dependent on the database's support for Bulk Copy operations, which is present in SQL Server but absent in SQLite. For SQLite, particularly when dealing with file-based databases, wrapping operations in a transaction is the most effective way to optimize performance, as SQLite's low round-trip overhead otherwise makes advanced bulk insertion techniques less impactful.

BulkInsertBench.cs

#:package Dapper@*
#:package Z.Dapper.Plus@*
#:package Microsoft.Data.SqlClient@*
#:package Microsoft.Data.Sqlite@*
#:package Spectre.Console@*
#:package Spectre.Console.Cli@*

#:property PublishAot=false
#:property NoWarn=CS8618

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using Microsoft.Data.SqlClient;
using Dapper;
using Z.Dapper.Plus;
using Spectre.Console;
using System.Reflection;
using Microsoft.Data.Sqlite;
using System.Data;

public static class Program
{
    public static void Main()
    {
        // Map your entity
        DapperPlusManager.Entity<Product>()
            .Table("Product");

        IDbConnection connection = CreateMsSqlTable();
        Main1(connection, "SQL Server");

        connection = CreateSqliteTable(memory: true);
        Main1(connection, "SQLite :memory:");

        connection = CreateSqliteTable(memory: false);
        Main1(connection, "SQLite file");
    }

    public static void Main1(IDbConnection connection, string title)
    {
        List<BenchmarkResult> BenchmarkResults = new List<BenchmarkResult>();

        JustInTime_Compile(connection);

        var products = GenerateProducts(2000);


        var clockDapper = new Stopwatch();
        // Dapper
        {
            clockDapper.Start();
            connection.Execute(@"INSERT INTO Product (Name, Description, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9)
											  VALUES (@Name, @Description, @Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7, @Column8, @Column9)", products);
            clockDapper.Stop();

            BenchmarkResults.Add(new BenchmarkResult() { Action = "Dapper", Entities = products.Count, Performance = clockDapper.ElapsedMilliseconds + " ms" });
        }

        // Dapper (Transaction)
        {
            var clockDapperPlus = Stopwatch.StartNew();
            clockDapperPlus.Start();
            using (var transaction = connection.BeginTransaction())
            {
                connection.Execute(@"INSERT INTO Product (Name, Description, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9)
											  VALUES (@Name, @Description, @Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7, @Column8, @Column9)", products, transaction);
                transaction.Commit();
            }
            clockDapperPlus.Stop();

            var timeFaster = Math.Round((double)clockDapper.ElapsedMilliseconds / clockDapperPlus.ElapsedMilliseconds, 2);
            var reducedPercent = Math.Round((double)(clockDapper.ElapsedMilliseconds - clockDapperPlus.ElapsedMilliseconds) / clockDapper.ElapsedMilliseconds, 2) * 100;
            BenchmarkResults.Add(new BenchmarkResult() { Action = "Dapper (Transaction)", Entities = products.Count, Performance = $"{clockDapperPlus.ElapsedMilliseconds} ms", TimeFaster = $"{timeFaster}x faster than Dapper", ReducedPercent = $"Time reduced by {reducedPercent}% compared to Dapper" });
        }

        // Dapper Plus
        {
            // BulkInsert
            var clockDapperPlus = new Stopwatch();
            clockDapperPlus.Start();
            connection.BulkInsert(products);
            clockDapperPlus.Stop();

            var timeFaster = Math.Round((double)clockDapper.ElapsedMilliseconds / clockDapperPlus.ElapsedMilliseconds, 2);
            var reducedPercent = Math.Round((double)(clockDapper.ElapsedMilliseconds - clockDapperPlus.ElapsedMilliseconds) / clockDapper.ElapsedMilliseconds, 2) * 100;
            BenchmarkResults.Add(new BenchmarkResult() { Action = "Dapper Plus", Entities = products.Count, Performance = $"{clockDapperPlus.ElapsedMilliseconds} ms", TimeFaster = $"{timeFaster}x faster than Dapper", ReducedPercent = $"Time reduced by {reducedPercent}% compared to Dapper" });
        }

        connection.DropTable("Product");

        WriteTable(BenchmarkResults, title: title);
    }

    public static SqlConnection CreateMsSqlTable()
    {
        var builder = new SqlConnectionStringBuilder()
        {
            DataSource = "localhost",
            InitialCatalog = "tempdb",
            UserID = "sa",
            Password = "25ae06df-4653-4893-99a6-0b5d9c8d4414",
            TrustServerCertificate = true
        };

        var connection = new SqlConnection(builder.ToString());
        connection.Open();

        // connection.CreateTable<Product>();
        connection.Execute(@"create table Product
(
	ProductID int identity
		constraint PK_Product
			primary key,
	Name nvarchar(max),
	Description nvarchar(max),
	Column1 nvarchar(max),
	Column2 nvarchar(max),
	Column3 nvarchar(max),
	Column4 nvarchar(max),
	Column5 nvarchar(max),
	Column6 nvarchar(max),
	Column7 nvarchar(max),
	Column8 nvarchar(max),
	Column9 nvarchar(max)
);");
        return connection;
    }

    public static SqliteConnection CreateSqliteTable(bool memory)
    {
        var builder = new SqliteConnectionStringBuilder();
        builder.DataSource = memory ? ":memory:" : ".\\temp.db";
        var connection = new SqliteConnection(builder.ToString());
        connection.Open();

        connection.Execute(@"create table if not exists Product
(
	ProductID INTEGER primary key,
	Name TEXT,
	Description TEXT,
	Column1 TEXT,
	Column2 TEXT,
	Column3 TEXT,
	Column4 TEXT,
	Column5 TEXT,
	Column6 TEXT,
	Column7 TEXT,
	Column8 TEXT,
	Column9 TEXT
);");
        return connection;
    }

    public static List<Product> GenerateProducts(int count)
    {
        var products = new List<Product>();

        for (int i = 0; i < count; i++)
        {
            if (i % 3 == 0)
            {
                products.Add(new Product() { Name = "Dapper Plus", Description = @"Use <a href=""https://dapper-plus.net/"" target=""_blank"">Dapper Plus</a> to extend your IDbConnection with high-performance bulk operations.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });
            }
            else if (i % 3 == 1)
            {
                products.Add(new Product() { Name = "C# Eval Expression", Description = @"Use <a href=""https://eval-expression.net/"" target=""_blank"">C# Eval Expression</a> to compile and execute C# code at runtime.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });
            }
            else if (i % 3 == 2)
            {
                products.Add(new Product() { Name = "Entity Framework Extensions", Description = @"Use <a href=""https://entityframework-extensions.net/"" target=""_blank"">Entity Framework Extensions</a> to extend your DbContext with high-performance bulk operations.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });
            }
        }

        return products;
    }

    public static void JustInTime_Compile(IDbConnection connection)
    {
        var products = GenerateProducts(20);

        // Dapper
        {
            connection.Execute(@"INSERT INTO Product (Name, Description, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9)
											  VALUES (@Name, @Description, @Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7, @Column8, @Column9)", products);
        }

        // Dapper Plus
        {
            connection.BulkInsert(products);
        }
    }

    [Table("Product")]
    public class Product
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ProductID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public string Column3 { get; set; }
        public string Column4 { get; set; }
        public string Column5 { get; set; }
        public string Column6 { get; set; }
        public string Column7 { get; set; }
        public string Column8 { get; set; }
        public string Column9 { get; set; }
    }

    public class BenchmarkResult
    {
        public string Action { get; set; }
        public int Entities { get; set; }
        public string Performance { get; set; }
        public string TimeFaster { get; set; }
        public string ReducedPercent { get; set; }
    }

    public static void WriteTable<T>(
            this IEnumerable<T> items,
            string? title = null,
            TableBorder? borderStyle = null,
            Func<string, TableColumn>? columnStyleFunc = null)
    {
        AnsiConsole.Write(ToTable(items, title, borderStyle, columnStyleFunc));
    }

    public static Table ToTable<T>(
            this IEnumerable<T> items,
            string? title = null,
            TableBorder? borderStyle = null,
            Func<string, TableColumn>? columnStyleFunc = null)
    {
        var table = new Table();

        if (!string.IsNullOrEmpty(title))
        {
            table.Title(new TableTitle(title, new Style(foreground: Color.Aqua, decoration: Decoration.Underline)));
        }

        table.Border(borderStyle ?? TableBorder.Rounded);
        PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach (var prop in properties)
        {
            if (columnStyleFunc != null)
                table.AddColumn(columnStyleFunc(prop.Name));
            else
                table.AddColumn($"[bold blue]{prop.Name}[/]");
        }

        foreach (var item in items)
        {
            var rowValues = new List<string>();
            foreach (var prop in properties)
            {
                object? value = prop.GetValue(item);
                rowValues.Add(value?.ToString() ?? "[grey]N/A[/]");
            }
            table.AddRow(rowValues.ToArray());
        }

        return table;
    }
}