昨日書いたばかりの、これだけど、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 という拡張メソッドを作ります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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 に変換するパターン
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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 に変換するパターン
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 を直接使ってしまたほうが早いです。