[C#] 性能改善のためにList型の大容量データを保存する場合はSqlBulkCopyを利用する

目次
はじめに
ユーザ定義型としてList<T>を利用するケースは多々あります。
今回は、データベースへ保存する方法として「SqlBulkCopy」を利用して保存します。
1行ずつInsertしていくこともできますが、大容量データなど処理に時間がかかるため、「SqlBulkCopy」は覚えておきたい方法の一つではあります。
どのくらい速度が違うのか
実際に1行ずつInsertしていくのと、SqlBulkCopyでどのくらい速度の差がでるかというと、だいたい約100倍は違います。
結果や測定方法については、下記「Moonmile Solutions Bolog」様のサイトにて詳しく記載されていますので、ご参照ください
SqlBulkCopyのための準備
SqlBulkCopyを利用するための手順は下記の通りです。
- List<T>をDataTableに変換する
- SqlBulkCopy.WriteToServerで保存する
とても簡単にできそうです。
ListをDataTableに変換する
public static DataTable ToDataTable<T>(this List<T> data)
{
var properties = TypeDescriptor.GetProperties(typeof(T));
var table = new DataTable();
foreach(PropertyDescriptor prop in properties)
{
table.Columns.Aadd(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach(T item in data)
{
var row = table.NewRow();
foreach(PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
}
return table;
}
ちなみに、List<T>型のプロパティ名とテーブルのカラム名が異なる場合は、Datatableのカラムを設定し直す必要があります。
List<Info> infoList = new List<Info>()
{
id = 1, infoName = “test”
}
DataTable table = new Datatable(“SampleTable”);
table.Columns.Add(new Datacolumn(“id”,typeof(int)));
table.Columns.Add(new Datacolumn(“name”,typeof(string)));
foreach(Info target in infoList)
{
table.Rows.Add(target.id, target.infoName);
}
SqlBulkCoppy.WriteToServerで保存する
では、SqlBulkCopyを利用して保存してみます。
string connectionString = “Data Source=SampleDB\SQLExPRESS;Initial Catalog=SampleTable;User ID=sa;Password=P@ssw0rd”;
using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connecttionS))
{
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = “SampleTable”;
bulkCopy.WriteToServer(table);
}
実行時、トランザクションを張った場合に「既存のトランザクションは予期しないものです。」というエラーが発生した場合は、下記で対応が可能です。
connection.Open();
using(var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
{
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = “SampleTable”;
bulkCopy.WrieToServer(table);
}
tran.Commit();
}
最後に
以上でSqlBulkCopyが動作しました。
大容量データを利用する場合は、SqlBulkCopyを利用するのが良いですが、データが少ない場合ではInsertを利用するで良いと思います。
利用ケースに応じて使い分けしてみてはいかがでしょうか?