SqlBulkCopy でデータを一括登録する(SQL Server)

データの一括登録

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 に置き換えることで相当な高速化が期待できると思います。

1. 100万件のデータ登録に5分以上かかったりしました。 ↩

2. 有料版の Dapper Plus では Bulk Insert を使用することができるそうです。 ↩

3. データベースの テーブルの項目名 と クラスのプロパティ名 が一致する必要があります。 ↩

4. 一致させられない場合、カスタム属性を用いてマッピングを行う等の工夫が必要になります(この記事では割愛します)。 ↩

5. INSERT文 を指定して Dapper.SqlMapper.Executeメソッド を実行しました。 ↩