Entity frameworkとSqlBulkCopyを併用して大量データInsertを高速化

はじめに

.Net Frameworkの中で最も便利であるEntity Framework（以下ef）ですが
大量データをUpdate、Insertするとかなりの時間がかかってしまう、時があります。
しかしながら作業環境によってはefを使わざるを得ない、でも遅いのは困る、
というとき使える技を紹介します。
※efの使い方は知っている前提

SqlBulkCopy

SqlBulkCopyとは、.Net Frameworkで使えるクラスで、
大量のデータを高速で一括insertできます。

例えば、データが何十万件もある、
そういったデータをプログラム上で加工した上でDBに格納したい場合に楽になるとのことです。

Insertする機能しかないのですが、ちょっと工夫すれば一括updateしたい場合にも活用できます。

使い方

using System.Data.SqlClient;

namespaceはこちら。

		var dt = new DataTable();
		dt.TableName = "test_table"; // テーブル名を設定
		dt.Columns.Add("column1"); // テーブルのフィールドを設定
		dt.Columns.Add("column2"); // テーブルのフィールドを設定

		// データを１件追加
		var dataRow = dt.NewRow();
		dataRow["column1"] = "data1";
		dataRow["column2"] = "data2";
		dt.Rows.Add(dataRow);

		var connectionString = "接続文字列";
		using (var bulkCopy = new SqlBulkCopy(connectionString))
		{
			bulkCopy.DestinationTableName = dt.TableName; // テーブル名をSqlBulkCopyに教える
			bulkCopy.WriteToServer(dt); // bulkCopy実行
		}

基本的な使い方はこのように。

efとの併用

//大量データの前提なのでTransaction実施
using (var tran = dbContext.Database.BeginTransaction())
{
    try
    {
        //データを取得
        var insertDataList = GetInsertData();
        
        var insertList = new List<INSERT_LIST>();
        var insertDetailList = new List<INSERT_LIST_DETAIL>();

        foreach (var row in insertDataList)
        {
            var insertRecord = new INSERT_LIST()
            {
                column1 = row.insertColumn1;
            };
            insertList.Add(insertRecord);
            
            foreach (var detail in row.detail)
            {
                var detailRecord = new INSERT_LIST_DETAIL()
                {
                    column1 = detail.insertColumn1;
                };
                insertDetailList.Add(detailRecord);
            }
        }
        //DBコネクト回数を減らしたいのでAddRange後SaveChangesを実施
        //処理速度もAddRangeの方が優秀です
        dbContext.INSERT_LIST.AddRange(insertList);
        dbContext.SaveChanges();
        
        //SqlBulkCopyはDataTable型にしか対応していない為、ここで型変換します
        var detailDt = ToDataTable(insertDetailList);
        
        //Connection、Transactionを共有
        using (var bulkCopy = new SqlBulkCopy(
              (SqlConnection)dbContext.Database.Connection,
              SqlBulkCopyOptions.Default,
              (SqlTransaction)tran.UnderlyingTransaction))
        {
            //複数のテーブルのInsertもできます。
            bulkCopy.DestinationTableName = detailDt.TableName;
            bulkCopy.WriteToServer(detailDt);
        }
        
        tran.Commit();
    }
    catch (Exception e)
    {
        tran.Rollback();
        throw e;
    }
}

ToDataTableというメソッドはList型をDataTable型に変換してくれます。
Extentionしてもいいのですがここでは普通のメソッドにしています。

private DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);

    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }

    return dataTable;
}

処理速度

ef単独とSqlBulkCopy併用して20万件のInsertを実施してみます。
どちらもはAutoDetectChangesEnabled = falseに設定してます。

ef単独	併用
5分	20秒

作業環境によって結果が異なってくるかもしれませんが
有意味な結果だと言えます。

注意点

.Net Framework 4.6以上であること。
Sql Server、Azureでしか実行できない。（ここが非常に残念）
内部で一回Selectを実施しているため対象テーブルに「選択」権限が必要。
列名とクラスのプロパティ名は一致させる必要があり、定義順も同様に一致させる必要がある。

おわりに

Insert以外にも、大量Update、PRを高速にできる方法があります。
efは確かに大量データの操作には弱いですが使いやすさとしては抜群だと思います。
ですので皆さんと一緒にどんどんefを活用していきたいと思います。