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

昨日書いたばかりの、これだけど、SqlBulkCopy を使うとどれだけ早くなるのかを再び実験

LINQ の INSERT が遅いときは AutoDetectChangesEnabled を False にする
http://www.moonmile.net/blog/archives/9646

結論

結論から言えば、SqlBulkCopy を使うほうが100倍位早いですね。1万件位だと6秒から0.06秒という誤差？っぽい感じだけど、100万件になるとLINQのINSERTでは難しいので、SqlBulkCopy を直接使えという感じです。

SqlBulkCopy は DataTable を受け取る

SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Docs
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.7.2

SQL Server専用の SqlBulkCopy な訳ですが、引数に DataTable か DataRow の配列を取ります。いわゆるEFじゃない DataSet/DataTable のものを使わないといけないので、EFのDbSetがそのまま渡せません。
逆に言えば、EFのDbSetを渡せるようにDataTableに変換してやれば、LINQとSqlBulkCopyが共存可能になります。

任意のオブジェクトの配列をDataTableに変換する – Qiita
https://qiita.com/keidrumfreak/items/f092b3cacfc2961610b6

この記事を参考にしながら、というかそのまま使って、AsDataTable という拡張メソッドを作ります。

public static class DataTableExtenstions
{
    public static DataTable AsDataTable<T>(this DbSet<T> src) where T : class
    {
        return DataTableExtenstions.AsDataTable( src.Local );
    }
    public static DataTable AsDataTable<T>(this IEnumerable<T> src) where T : class
    {
        var properties = typeof(T).GetProperties();
        var dest = new DataTable();
        // テーブルレイアウトの作成
        foreach (var prop in properties)
        {
            dest.Columns.Add(prop.Name, prop.PropertyType);
        }
        // 値の投げ込み
        foreach (var item in src)
        {
            var row = dest.NewRow();
            foreach (var prop in properties)
            {
                var itemValue = prop.GetValue(item, new object[] { });
                row[prop.Name] = itemValue;
            }
            dest.Rows.Add(row);
        }
        return dest;
    }
}

EFのDbSetは内部でLocalプロパティ（データベースに反映する前のデータを取っている持っておくコレクション）があるので、これを利用して DataTable に変換します。

実験

SqlBulkCopy に渡すデータを List で用意してから DataTable に変換するパターン

private void clickBulk(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    var lst = new List<BulkT>();
    for (int i = 0; i < 10000; i++)
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        lst.Add(t);
    }
    var dt = lst.AsDataTable();
    var cn = ent.Database.Connection as SqlConnection;
    var bc = new SqlBulkCopy(cn);
    bc.DestinationTableName = "BulkT";
    cn.Open();
    bc.WriteToServer(dt);
    cn.Close();

    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

SqlBulkCopy に渡すデータをLINQのAddで追加してから DataTable に変換するパターン

private void clickAsDataTable(object sender, RoutedEventArgs e)
{
    var ent = new testdbEntities();
    ent.Configuration.AutoDetectChangesEnabled = false;
    ent.Configuration.ValidateOnSaveEnabled = false;
    ent.Database.ExecuteSqlCommand("delete BulkT");
    var start = DateTime.Now;
    for (int i = 0; i < 10000; i++)
    {
        var t = new BulkT()
        {
            GUID = Guid.NewGuid().ToString("N"),
            Created = DateTime.Now,
        };
        ent.BulkT.Add(t);
    }
    var cn = ent.Database.Connection as SqlConnection;
    var bc = new SqlBulkCopy(cn);
    bc.DestinationTableName = "BulkT";
    var dt = ent.BulkT.AsDataTable();
    cn.Open();
    bc.WriteToServer(dt);
    cn.Close();

    var tend = DateTime.Now;
    var span = (tend - start).TotalSeconds;
    System.Diagnostics.Debug.WriteLine(span.ToString());
}

結果

List を使って DataTable に変換
0.0477976
0.0400377
0.034159

LINQのAddを使って DataTable に変換
1.7919409
1.7825315
1.7763977

AutoDetectChangesEnable = false の場合
6.311641
5.8724151
6.0832671

単純に比較すると、AutoDetectChangesEnable を false にしただけよりも、SqlBulkCopy を使ったほうが6倍位早くなります。さらに、EFのDbSetを使わずに、Listだけを使った場合は、25倍位早くなるってことですね。どうやら、DbSetに対してAdd/Removeしたときに DetectChanges() 等のチェックルーチンが走るらしく、単純な大量 INSERT の場合には SqlBulkCopy を直接使ってしまたほうが早いです。