データの一括登録

Dapper を使用してDB処理を実装していたのですが、データ件数が多い場合に INSERT処理に多くの時間がかかるようになりました。 ¹
大量データの一括登録処理向けに、SqlBulkCopy を使用する方法を導入したので、簡単にまとめておきます。²
(NULL値を含むデータを登録する時に例外が発生して対応に困ったので、その対処についての覚書を兼ねてます)

SqlBulkCopy

System.Data.SqlClient.SqlBulkCopy を使用すると SQL Server のテーブルに大量データを高速に一括登録することが出来ます。

今回は DataTable を作成していますが、データベース間でのデータ移行では IDataReader を渡す方法が便利です。
Qiitaでは以下の記事で Oracle から SQL Server へのデータ移行に IDataReader を渡す方法が紹介されています。

注意事項

.NET 5、 SQL Server 2019 Developer エディションで動作確認しています。
データベースのテーブルに対応するマッピング用のデータクラス³ が必要となります。
以下のソースコードは例外処理や細かい制御を省いています。
パフォーマンス測定を目的とした記事では無いので、所要時間は厳密な測定を行っていません。

SqlBulkCopy の使用方法

SqlBulkCopy の簡単な使用例を記載します。
詳しい使用方法はMicrosoftのドキュメントを参照ください。

SqlBulkCopy クラス (System.Data.SqlClient) | Microsoft Docs
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlbulkcopy

string connectionString = "****"; // 接続文字列
string tableName = "****"; // 登録先のテーブル名
List<T> data = new List<T> { /* 省略 */ } // 一括登録するデータ(Tはマッピング用のデータクラス)

using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
    connection.Open();

    using (var transaction = connection.BeginTransaction())
    {
        // DataTableの作成(後述)
        System.Data.DataTable dataTable = CreateDataTable(data);

        // SqlBulkCopyインスタンスの作成・利用
        using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection, System.Data.SqlClient.SqlBulkCopyOptions.Default, transaction))
        {
            // 登録先のテーブル名を指定
            bulkCopy.DestinationTableName = tableName;

            // データを一括登録
            bulkCopy.WriteToServer(dataTable);
        }

        transaction.Commit(); // コミット
    }
}

DataTable の作成

マッピング用のデータクラスのリストから、SqlBulkCopy に受け渡すための DataTable を以下のように作成することが出来ます。
データクラスのプロパティ名を登録先のテーブルの項目名として扱う為、プロパティ名と項目名は一致している必要があります。⁴

public static System.Data.DataTable CreateDataTable<T>(IEnumerable<T> items)
{
    var table = new System.Data.DataTable();

    var properties = typeof(T).GetProperties();

    // 項目定義の作成
    foreach (var prop in properties)
    {
        // Null許容の値型(Nullable)
        if (prop.PropertyType.IsGenericType
            && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            table.Columns.Add(new System.Data.DataColumn
            {
                ColumnName = prop.Name,
                DataType = Nullable.GetUnderlyingType(prop.PropertyType)
            });
        }
        // Nullable以外
        else
        {
            table.Columns.Add(new System.Data.DataColumn
            {
                ColumnName = prop.Name,
                DataType = prop.PropertyType
            });
        }
    }

    // データ投入
    foreach (var item in items)
    {
        var row = table.NewRow();

        foreach (var prop in properties)
        {
            row[prop.Name] = prop.GetValue(item) ?? System.DBNull.Value;
        }

        table.Rows.Add(row);
    }

    return table;
}

NULL許容な値型への対応

DataTable の項目定義として DataColumn のインスタンスを作成していますが、DataType プロパティには NULL許容型(Nullable<>)を指定することが出来ません。

PropertyInfo のインスタンス prop を用いて、基本的には以下のように DataColumn のインスタンスを作成できますが、

new System.Data.DataColumn
{
    ColumnName = prop.Name,
    DataType = prop.PropertyType
}

対象となるプロパティが int? や Nullable<int> 等の NULL許容の値型の場合、以下の例外が発生します。

System.NotSupportedException
  HResult=0x80131515
  Message=DataSet は System.Nullable<> をサポートしていません。
  Source=System.Data

その為、上記 CreateDataTableメソッドでは、プロパティが Nullable の場合はそのベースとなる型(Nullable<int> に対する int)を使用するように実装しています。

if (prop.PropertyType.IsGenericType
    && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
    table.Columns.Add(new System.Data.DataColumn
    {
        ColumnName = prop.Name,
        DataType = Nullable.GetUnderlyingType(prop.PropertyType)
    });
}

SqlBulkCopy を使用した効果

最後に、Dapper を使用したデータ登録⁵ と SqlBulkCopy を使用したデータ登録にかかった時間を比較します。
登録したデータは 100万件で 80MB 程度のデータサイズとなります。

件数	Dapper	SqlBulkCopy
10万件	約 40 秒	1 秒未満
100万件	約 420 秒	約 10 秒

条件により実際にかかる所要時間は変わりますが、多くの場合データの一括登録処理を SqlBulkCopy に置き換えることで相当な高速化が期待できると思います。

100万件のデータ登録に5分以上かかったりしました。 ↩
有料版の Dapper Plus では Bulk Insert を使用することができるそうです。 ↩
データベースのテーブルの項目名とクラスのプロパティ名が一致する必要があります。 ↩
一致させられない場合、カスタム属性を用いてマッピングを行う等の工夫が必要になります(この記事では割愛します)。 ↩
INSERT文を指定して Dapper.SqlMapper.Executeメソッドを実行しました。 ↩