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; }
...
}
