データの一括登録
Dapper を使用してDB処理を実装していたのですが、データ件数が多い場合に INSERT処理 に多くの時間がかかるようになりました。 1
大量データの一括登録処理向けに、SqlBulkCopy を使用する方法を導入したので、簡単にまとめておきます。2
(NULL値を含むデータを登録する時に例外が発生して対応に困ったので、その対処についての覚書を兼ねてます)
SqlBulkCopy
System.Data.SqlClient.SqlBulkCopy を使用すると SQL Server のテーブルに大量データを高速に一括登録することが出来ます。
今回は DataTable を作成していますが、データベース間でのデータ移行では IDataReader を渡す方法が便利です。
Qiitaでは以下の記事で Oracle から SQL Server へのデータ移行に IDataReader を渡す方法が紹介されています。
注意事項
- .NET 5、 SQL Server 2019 Developer エディション で動作確認しています。
- データベースのテーブルに対応する マッピング用のデータクラス3 が必要となります。
- 以下のソースコードは例外処理や細かい制御を省いています。
- パフォーマンス測定を目的とした記事では無いので、所要時間は厳密な測定を行っていません。
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 を以下のように作成することが出来ます。
データクラスのプロパティ名 を 登録先のテーブルの項目名 として扱う為、プロパティ名 と 項目名 は一致している必要があります。4
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 を使用したデータ登録5 と SqlBulkCopy を使用したデータ登録にかかった時間を比較します。
登録したデータは 100万件 で 80MB 程度のデータサイズとなります。
| 件数 | Dapper | SqlBulkCopy |
|---|---|---|
| 10万件 | 約 40 秒 | 1 秒 未満 |
| 100万件 | 約 420 秒 | 約 10 秒 |
条件により実際にかかる所要時間は変わりますが、多くの場合データの一括登録処理を SqlBulkCopy に置き換えることで相当な高速化が期待できると思います。