Entity Framework：データベースにデータを保存するときのパフォーマンスを改善する

大量のレコード（10³以上）を追加/変更する場合、Entity Frameworkのパフォーマンスには多くの要望が残ります。 この理由は、フレームワーク自体のアーキテクチャ上の機能と最適でない生成されたSQLの両方です。 先を見据えて-コンテキストをバイパスしてデータを保存すると、実行時間が大幅に短縮されます。



記事の内容：

1.標準のEntity Frameworkツールによる挿入/更新

2.問題の解決策を見つける

3. Entity FrameworkとSqlBulkCopyの統合

4. MERGEを使用した高度な挿入

5.パフォーマンスの比較

6.結論

1.標準のEntity Frameworkツールによる挿入/更新

挿入から始めましょう。 データベースに新しいレコードを追加する標準的な方法は、それをコンテキストに追加して保存することです。

context.Orders.Add(order); context.SaveChanges();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Addメソッドを呼び出すたびに、内部DetectChangesアルゴリズムの実行コストが高くなります。 このアルゴリズムは、コンテキスト内のすべてのエンティティをスキャンし、各プロパティの現在の値をコンテキストに保存されている元の値と比較し、エンティティ間の関係などを更新します。 EF 6のリリースまで関連するパフォーマンスを改善するための既知の方法は、コンテキストにエンティティを追加する間、 DetectChangesを無効にすることです。

  context.Configuration.AutoDetectChangesEnabled = false; orders.ForEach(order => context.Orders.Add(order)); context.Configuration.AutoDetectChangesEnabled = true; context.SaveChanges();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、コンテキストを維持し、 ここに示すようにN個ごとに新しいオブジェクトを作成しながら、何万ものオブジェクトをコンテキストに保持せずにデータをブロックに保存することもお勧めします 。 最後に、EF 6では、 Add + AutoDetectChangesEnabledバンドルレベルにパフォーマンスを向上させる最適化されたAddRangeメソッドが登場しました。

  context.Orders.AddRange(orders); context.SaveChanges();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

残念ながら、上記のアプローチは主な問題を解決しません。つまり、データベースにデータを保存するとき、 新しいレコードごとに個別のINSERT要求が生成されます！



更新では、状況は似ています。 次のコード：

  var orders = context.Orders.ToList(); //..    context.SaveChanges();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

変更されたオブジェクトごとに個別のSQLクエリが実行されます。



最も単純なケースでは、 EntityFramework.Extendedが役立つ場合があります。

 //update all tasks with status of 1 to status of 2 context.Tasks.Update( t => t.StatusId == 1, t2 => new Task { StatusId = 2 });
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このコードはコンテキストをバイパスし、1つのSQLクエリを生成します。 EFの速度とこのライブラリの操作の詳細については、 tp7を 参照してください 。 明らかに、ソリューションは普遍的ではなく、同じ値のすべてのターゲット行への書き込みにのみ適しています。

2.問題の解決策を見つける

「バイク」を書くことに対する執persistentな嫌悪感を感じて、私は最初にEFを使用した複数挿入のベストプラクティスを探しました。 典型的なタスクのように思えますが、「箱から出して」適切なソリューションが見つかりませんでした。 同時に、SQL Serverは、 bcpユーティリティやSqlBulkCopyクラスなど、データをすばやく挿入するための多くの手法を提供します。 後者については以下で説明します。



System.Data.SqlClient.SqlBulkCopyは、大量のデータをSQL Serverテーブルに書き込むように設計されたADO.NETのクラスです。 データソースとして、 DataRow [] 、 DataTable 、またはIDataReader実装を使用できます。

できること：

• トランザクションサポートを使用してデータをサーバーブロックに送信します。
• DataTableからデータベーステーブルへの列マッピングを実行します。
• 制約、挿入時の外部キーを無視します（オプション）。

短所：

• 原子挿入（オプション）;
• 例外が発生した後に作業を継続できない。
• 弱いエラー処理機能。

JeanLouisの 記事でクラスの詳細を読むことができますが、ここでは緊急の問題、つまりSqlBulkCopyとEFの統合の欠如について検討します。 このような問題を解決する確立されたアプローチはありませんが、次のようなさまざまな程度の適合性のプロジェクトがいくつかあります。



EntityFramework.BulkInsert

実際、それは動作不能であることが判明しました。 課題を勉強していると、悪名高いジュリー・ラーマンとの議論に出会いました。私の問題と似ており、プロジェクトの作者が答えていない問題について説明しています。



EntityFramework.Utilities

ライブプロジェクト、アクティブなコミュニティ。 Database Firstサポートはありませんが、追加することを約束します。



Entity Frameworkの拡張

300ドル。

3. Entity FrameworkとSqlBulkCopyの統合

すべて自分でやろうとしましょう。 最も単純なケースでは、 SqlBulkCopyを使用してオブジェクトのコレクションからデータを挿入すると、次のようになります。

  //entities -   EntityFramework using (IDataReader reader = entities.GetDataReader()) using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlBulkCopy bcp = new SqlBulkCopy(connection)) { connection.Open(); bcp.DestinationTableName = "[Order]"; bcp.ColumnMappings.Add("Date", "Date"); bcp.ColumnMappings.Add("Number", "Number"); bcp.ColumnMappings.Add("Text", "Text"); bcp.WriteToServer(reader); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

オブジェクトのコレクションに基づいてIDataReaderを実装するタスク自体は簡単なので、 リンクに限定して、 SqlBulkCopyを使用して挿入中にエラーを処理する方法を説明します。 デフォルトでは、データは独自のトランザクションに挿入されます。 例外が発生すると、 SqlExceptionがスローされ、ロールバックが発生します。 データベース内のデータはまったく書き込まれません。 また、このクラスの「ネイティブ」エラーメッセージは情報価値がないとは言えません。 たとえば、 SqlException.AdditionalInformationを含む可能性があるものは次のとおりです。

データソースのString型の指定された値は、指定されたターゲット列のnvarchar型に変換できません。

または：

SqlDateTimeオーバーフロー。 1/1/1753 12:00:00 AMから12/31/9999 11:59:59 PMの間でなければなりません。

残念ながら、 SqlBulkCopyは多くの場合、エラーの原因となった行/エンティティを一意に識別する情報を提供しません。 もう1つの不快な機能は、プライマリキーに重複レコードを挿入しようとすると、 SqlBulkCopyが例外をスローして終了し、状況を処理して実行を継続する機会を提供しないことです。



マッピング

正しく生成されたエンティティとデータベースの場合、ここにあるように、型のコンプライアンスまたはテーブル内のフィールドの長さのチェックは無関係になります 。 SqlBulkCopy.ColumnMappingsプロパティを介して実行される列マッピングを処理する方が便利です。

データソースと宛先テーブルの列数が同じで、データソースの各ソース列の開始位置が対応する宛先列の開始位置に対応する場合、ColumnMappingsコレクションは必要ありません。 ただし、列の数または順序が異なる場合、ColumnMappingsを使用して、列間のデータが正しくコピーされるようにする必要があります。

EFの場合99％の場合、 ColumnMappingsを明示的に設定する必要があります（ナビゲーションプロパティと追加のプロパティのため）。 ナビゲーションプロパティは、Reflectionで除外できます。



このようなコードは、追加のプロパティなしでPOCOクラスに適合します。それ以外の場合は、「手動制御」に切り替える必要があります。 テーブルスキーマの取得も非常に簡単です。



これにより、ソースクラスとターゲットテーブルを手動でマッピングできます。



SqlBulkCopy.BatchSizeプロパティとSqlBulkCopyOptionsクラスの使用



SqlBulkCopy.BatchSize ：

バッチサイズ

各パッケージの行数。 各パケットの終わりに、それに含まれる行数がサーバーに送信されます。

SqlBulkCopyOptions-列挙：

メンバー名	説明
チェック制約	データを挿入するときの制限を確認してください。 デフォルトでは、制限はチェックされていません。
デフォルト	すべてのパラメーターにデフォルト値を使用します。
ファイアトリガー	この設定を指定すると、サーバーはデータベースに挿入された行の挿入トリガーを呼び出します。
KeepIdentity	ソース識別値を保存します。 この設定が指定されていない場合、識別値はターゲットテーブルによって割り当てられます。
Keepnulls	デフォルト値の設定に関係なく、ターゲットテーブルにNULL値を格納します。 この設定が指定されていない場合、可能な場合はヌル値がデフォルト値に置き換えられます。
テーブルロック	一括コピー操作中に一括更新ロックを取得します。 この設定が指定されていない場合、行ロックが使用されます。
UseInternalTransaction	この設定を指定すると、各バルクデータコピー操作がトランザクションで実行されます。 この設定を設定し、コンストラクタにSqlTransactionオブジェクトを提供すると、ArgumentExceptionがスローされます。

オプションで、データベース側でトリガーと制限の検証を有効にできます（デフォルトでは無効）。 BatchSizeとUseInternalTransactionを指定すると、データは個別のトランザクションでブロック単位でサーバーに送信されます。 したがって、最初のエラーが発生するまで成功したすべてのブロックはデータベースに保存されます。

4. MERGEを使用した高度な挿入

SqlBulkCopyはテーブルにレコードを追加できるだけで、既存のレコードを変更する機能は提供しません。 それでも、更新操作の実行を高速化できます！ どうやって？ データを一時的な空のテーブルに貼り付け、SQL Server 2008で導入されたMERGEステートメントを使用してテーブルを同期します。

MERGE（Transact-SQL）

ソース表への結合の結果に基づいて、ターゲット表の挿入、更新、または削除操作を実行します。 たとえば、別のテーブルで見つかった違いに基づいて1つのテーブルの行を挿入、更新、または削除することにより、2つのテーブルを同期できます。

MERGEを使用すると、複製を処理するためのさまざまなロジックを簡単かつ簡単に実装できます。ターゲットテーブルのデータを更新するか、一致するレコードを無視または削除することさえできます。 したがって、次のアルゴリズムを使用して、データベース内のEFオブジェクトのコレクションからデータを保存できます。

1. ターゲット表と完全に同一の一時表を作成/消去します。
2. SqlBulkCopyを使用して一時テーブルにデータを挿入します。
3. MERGEを使用して、一時テーブルのエントリをターゲットに追加します。

手順1と3について詳しく説明します。



一時テーブル

データを挿入するために、テーブルレイアウトを完全に繰り返すテーブルをデータベースに作成する必要があります。 手動でコピーを作成することは可能な限り最悪のオプションです。テーブルスキーマの比較と同期に関するこれ以降のすべての作業も負担になります。 回路をプログラムで挿入する直前にコピーする方が安全です。 たとえば、 SQL Server管理オブジェクト（SMO）を使用する場合 ：

  Server server = new Server(); //SQL auth server.ConnectionContext.LoginSecure = false; server.ConnectionContext.Login = "login"; server.ConnectionContext.Password = "password"; server.ConnectionContext.ServerInstance = "server"; Database database = server.Databases["database name"]; Table table = database.Tables["Order"]; ScriptingOptions options = new ScriptingOptions(); options.Default = true; options.DriAll = true; StringCollection script = table.Script(options);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

生成されたSQLを微調整するための数十個のパラメーターを含むScriptingOptionsクラスに注意する価値があります。 結果のStringCollectionをStringに展開します。 残念ながら、スクリプトでソーステーブルの名前を一時的なString.Replace（ "Order"、 "Order_TEMP"）の名前に置き換えるよりも良い解決策は見つかりませんでした。 同じデータベース内にテーブルのコピーを作成するという美しい決定のヒントに感謝します。 完成したスクリプトを便利な方法で実行しましょう。 テーブルのコピーが作成されました！







一時テーブルからターゲットにデータをコピーします

SQL Server側でMERGEステートメントを実行し、一時テーブルとターゲットテーブルの内容を比較し、必要に応じて更新または挿入を実行します。 たとえば、[Order]テーブルの場合、コードは次のようになります。

 MERGE INTO [Order] AS [Target] USING [Order_TEMP] AS [Source] ON Target.Id = Source.Id WHEN MATCHED THEN UPDATE SET Target.Date = Source.Date, Target.Number = Source.Number, Target.Text = Source.Text WHEN NOT MATCHED THEN INSERT (Date, Number, Text) VALUES (Source.Date, Source.Number, Source.Text);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このSQLクエリは、一時テーブル[Order_TEMP]のレコードをターゲットテーブル[Order]のレコードと比較し、Idフィールドに同じ値を持つレコードが見つかった場合は更新を実行し、そのようなレコードが見つからない場合は挿入を実行します。 便利な方法でコードを実行しましょう。これで完了です！ 一時テーブルを削除することを忘れないでください。

5.パフォーマンスの比較

ランタイム環境：Visual Studio 2013、Entity Framework 6.1.1（データベース優先）、SQL Server2012。テストには、[Order]テーブルを使用しました（テーブル図は上記のとおりです）。 記事で検討したデータベースにデータを保存するアプローチのランタイムを測定し、結果を以下に示します（時間は秒単位で示されています）。

挿入

データベースへの変更をコミットする方法	レコード数
	1000	10,000	100,000
追加+変更を保存	7.3	101	6344
追加+（AutoDetectChangesEnabled = false）+ SaveChanges	6.5	64	801
追加+個別のコンテキスト+ SaveChanges	8.4	77	953
AddRange + SaveChanges	7.2	64	711
SqlBulkCopy	0.01	0,07	0.42

わあ！ Addメソッドを使用してコンテキストに追加し、 SaveChangesを保存する場合、100,000レコードをデータベースに保存するのに約2時間かかります。 SqlBulkCopyは同じタスクに1秒もかかりません！

更新する

データベースへの変更をコミットする方法	レコード数
	1000	10,000	100,000
変更を保存	6.2	60	590
SqlBulkCopy + MERGE	0.04	0.2	1,5

再びSqlBulkCopyを競争から排除します。 テストアプリケーションのソースコードはGitHubで入手できます 。

結論

多数のオブジェクト（10³以上）を含むコンテキストを操作する場合、Entity Frameworkインフラストラクチャを放棄（コンテキストに追加+コンテキストを保存）し、データベースへの書き込みのためにSqlBulkCopyに切り替えると、パフォーマンスが数十倍または数百倍になります。 しかし、私の意見では、EF + SqlBulkCopyバンドルをどこでも使用することは、アプリケーションのアーキテクチャに何か問題があることを明確に示しています。 この記事で検討されているアプローチは、何らかの理由でアーキテクチャ/テクノロジーを変更することが困難な場合、すでに記述されたシステムのボトルネックのパフォーマンスを加速するための簡単な手段と考えるべきです。 Entity Frameworkを使用する開発者は、このツールの長所と短所を知っている必要があります。 頑張って！