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

2019-11-062020-06-10

はじめに

ユーザ定義型としてList<T>を利用するケースは多々あります。
今回は、データベースへ保存する方法として「SqlBulkCopy」を利用して保存します。

1行ずつInsertしていくこともできますが、大容量データなど処理に時間がかかるため、「SqlBulkCopy」は覚えておきたい方法の一つではあります。

どのくらい速度が違うのか

実際に1行ずつInsertしていくのと、SqlBulkCopyでどのくらい速度の差がでるかというと、だいたい約100倍は違います。
結果や測定方法については、下記「Moonmile Solutions Bolog」様のサイトにて詳しく記載されていますので、ご参照ください

LINQ の INSERT を SqlBulkCopy にするとどれだけ早くなるのか？ | Moonmile Solutions Blog

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();
}