.NET CoreでNULL付きのデータをSQL Serverに大量投入する

2年ほど前、LINQ を使って大量データを INSERT すると遅すぎるので、LINQ の INSERT を SqlBulkCopy にするとどれだけ早くなるのか? | Moonmile Solutions Blog で、SqlBulkCopy を試しました。この記事の例では、6秒から 0.06 秒になって 100 倍になっています。実際は、仕事で使うデータ移行分があって、2万件ほどが1時間ぐらいかかっていたのが、数秒で終わるように効率化されています。

このツール自体は .NET Framework の EF6 で書いているのですが、現在 .NET5 の EF Core で書き換え中です。いままで、EF6 を使って Visual Studio 上で Model クラスを作っていたのですが、EF Core のほうでコードファースト的にテテーブル用に対応するクラスを作っています。コードファーストとはいえ、実際にデータベースに反映してはいません。しかし、いちいち Visual Studio 上でデータベース内のテーブルとの同期をとらなくてよいので楽です。

まあ、それはそれで、手作業でテーブルを書き換えてはマイグレーションするわけですが。

さて、EF Core の INSERT も EF6 の INSERT と同じようにかなり遅いです。EF6 の場合は、

  • AutoDetectChangesEnabled
  • ValidateOnSaveEnabled

を false にすることで、INSERT の高速化がそこそこできる(5倍ぐらい早くなる)のですが、数万件のデータを投入しようとする結構かかります。さらに、手元のデータでは EF6 よりも EF Core の INSERT が 5倍ぐらい遅いので、ちょっと大きめのデータだと INSERT だけでは実用的に無理ということになります。

そこで、再び SqlBulkCopy の出番なのです。以前書いていた拡張メソッド AsDataTable だと null 許容型が通らないので少し書き換えます。

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)
        {
            DataColumn dc = new DataColumn();
            dc.ColumnName = prop.Name;
            if (prop.PropertyType.IsGenericType && 
                prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                dc.DataType = Nullable.GetUnderlyingType(prop.PropertyType);
                dc.AllowDBNull = true;
            } else
            {
                dc.DataType = prop.PropertyType;
            }
            dest.Columns.Add(dc);
        }
        // 値の投げ込み
        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 ?? System.DBNull.Value;
            }
            dest.Rows.Add(row);
        }
        return dest;
    }
}

データを投入するときに null から System.DBNull.Value にしておきます。

private void blukSave<T>(string tablename, IEnumerable<T> items, bool keepid = true ) where T : class
{
    var cnstr = toEnt.Database.GetDbConnection().ConnectionString;
    SqlBulkCopy bc;
    if (keepid == true)
    {
        bc = new SqlBulkCopy(cnstr, SqlBulkCopyOptions.KeepIdentity);
    }
    else
    {
        bc = new SqlBulkCopy(cnstr);
    }
    bc.DestinationTableName = tablename;
    var dt = items.AsDataTable();
    bc.WriteToServer(dt);
}

確か、以前の BulkCopy は ID をインクリメントしなかったような気がするのですが、現在の SqlBulkCopy は ID を挿入時にインクリメントしてしまいます。大量データを投入するときは、ID はあらかじめ振ってあることが多い(他のデータから移行するため)ので、INSERT 時に ID の値が変わらないようにします。

オプションで SqlBulkCopyOptions.KeepIdentity をつけておきます。

実際の使い方はこんな感じ。予約テーブルは実は ACCESS から移行するデータなので大量に Nullable が入っています。いったん List にため込んでから、BlukCopy を行うので一時的にため込まれる List のメモリ量が心配ですが、まあ、大丈夫でしょう。最初の DropTable 関数は、内部で TRUNCATE TABLE を呼び出しています。

public bool To予約()
{
    DropTable("予約");
    var lst = new List<予約>();
    foreach (var it in fromEnt.T_予約)
    {
        var t = new 予約();
        t.ID = it.KID;
        t.顧客ID = it.TID.Value;
        t.顧客SUBID = it.TIDa ?? 0;
        t.予約者 = it.予約者;
        ...
        t.UpdateAt = DateTime.Now;

        lst.Add(t);
    }
    this.blukSave("予約", lst);
    return true;
}

この状態で、2万件のデータを投入すると数秒で終わります。EF Core の LINQ の INSERT を使うと、2時間ぐらいかかるので、これは実用的とは言えません。何が遅いのがいまいち不明ですね。。。

余談ですが、.NET Core の DbContext を使ったときに、AutoDetectChangesEnabled や ValidateOnSaveEnabled がありません。代わりに、OnConfiguring をオーバーライドして UseQueryTrackingBehavior を使います。トラッキングをしないようにすると、多少は早くなる(2,3倍ぐらい)のですが、SqlBulkCopy の 100倍には遠く及びません。

public class KaigiDbContext : DbContext
{
    public KaigiDbContext()
    {

    }
    public KaigiDbContext(DbContextOptions<AccessDbContext> options) : base(options)
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string cnstr = "data source=.;initial catalog=会議室;integrated security=True";
        optionsBuilder.UseSqlServer(cnstr);
        optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
    public DbSet<予約> 予約 { get; set; }
...
}

カテゴリー: 開発 パーマリンク