質問: 現在のプロジェクトでは、複雑な/ネストされたメッセージのコレクションをシステムに送信します。これらのメッセージの頻度は約です。 1000〜2000秒/秒。 これらの複雑なオブジェクトには、追加されるトランザクションデータとマスタデータ(見つからなければ追加されます)が含まれています。しかし、マスターデータのIDを渡す代わりに、顧客は「名前」列を渡します。 これらの名称のマスタデータが存在するかどうかがチェックされます。見つかった場合は、データベースのIDを使用します。そうでない場合は、このマスターデータを最初に作成し、次にこれらのIDを使用します。 マスタデータIDが解決されると、トランザクションデータが(マスタデータIDを使用して)SQL Serverデータベースに挿入されます。メッセージあたりのマスタエンティティの数は約15〜20です。 以下は、我々が採用できるいくつかの戦略です。 1. 最初にC#コードからマスターIDを解決し(見つからなければマスターデータを挿入する)、これらのIDをC#キャッシュに格納することができます。すべてのidsが解決されたら、 `SqlBulkCopy`クラスを使用してトランザクションデータを一括挿入できます。データベースを15回ヒットして異なるエンティティのIDを取得し、もう一度データベースにヒットして最終データを挿入できます。この処理をすべて行った後、同じ接続を使用して終了することができます。 2. マスタデータとトランザクションデータを含むこれらのメッセージをすべて1回のヒットでデータベースに(複数のTVPの形式で)送信し、ストアドプロシージャ内でそれらのメッセージを最初に作成し、トランザクションデータを挿入できます。 誰でもこのユースケースで最善のアプローチを提案できますか? *プライバシーに関する問題のため、実際のオブジェクト構造を共有することはできません。しかしここでは、ビジネスオブジェクトに非常に近い仮説的なオブジェクト構造があります* 。 そのようなメッセージの1つに、異なるベンダーのある製品(マスタデータ)とその価格の詳細(トランザクションデータ)に関する情報が含まれます。 **マスタデータ(見つからない場合は追加する必要があります)** 製品名:ABC、ProductCateory:XYZ、Manufacturer:XXXおよび他のいくつかの詳細(プロパティの数は15-20の範囲です)。 **トランザクションデータ(常に追加されます)** 仕入先名:A、ListPrice:XXX、割引:XXX 仕入先名:B、ListPrice:XXX、割引:XXX ベンダー名:C、ListPrice:XXX、Discount:XXX 仕入先名:D、ListPrice:XXX、割引:XXX マスタデータに関する情報のほとんどは、1つの製品に属するメッセージについては同じままであり(頻繁には変化しません)、トランザクションデータは常に変動します。したがって、システムは、XXXという製品がシステムに存在するかどうかをチェックします。そうでない場合は、この製品に記載されている「カテゴリ」が存在しないかどうかを確認します。そうでなければ、それはカテゴリーと製品のための新しいレコードを挿入します。これは、製造者およびその他のマスタデータに対して行われます。 複数のベンダーが同時に複数の製品(2000〜5000)に関するデータを送信します。 したがって、我々は1000のサプライヤを持っていると仮定し、各ベンダは10-15の異なる製品についてのデータを送信しています。 2〜3秒後に、すべてのベンダーから10個の製品の価格アップデートが送信されます。彼は新製品についてのデータを送信し始めるかもしれませんが、あまり頻繁ではありません。
回答: おそらく#2のアイデアでベストになるでしょう(つまり、複数のTVPを使用してワンショットで15-20エンティティすべてをDBに送信し、2000個までのメッセージ全体を処理します)。 アプリ層でマスターデータの参照をキャッシュし、DBに送信する前に翻訳すると素晴らしい音が鳴りますが、何かが間違っています: 1. とにかく最初のリストを取得するにはDBにヒットする必要があります 2. あなたはとにかく新しいエントリーを挿入するためにDBを打つ必要があるでしょう 3. ディクショナリ内の値を参照してIDで置換するのは、データベースが行うものと*まったく同じです* (これらの名前からIDへのルックアップのそれぞれに非クラスタ化インデックスがあると仮定します) 4. 頻繁に照会値は、(メモリキャッシュ*である* )、そのデータページがバッファプールにキャッシュされています アプリケーション層で何が提供され、 *現在* DB層で行われて*いるのか* 、特に以下を挙げてください。 * 15〜20個のエンティティは最大20k個のレコードを持つことができます(非クラスタ化インデックスは2つのフィールドのみである必要があると考えると、比較的少数です)。 `Name`と`ID`を使用すると多くの行を1つのデータページにまとめることができます100%フィルファクター)。 * すべての20kエントリが「アクティブ」または「現在」であるわけではないので、すべてのキャッシュについて心配する必要はありません。したがって、現在の値が何であるかは問合せされるものとして容易に識別され、 *それらの*データ・ページ(いくつかの非アクティブ・エントリが含まれていても大したことはありません)がバッファ・プールにキャッシュされます。 したがって、古いエントリをエージングしたり、自然に処理される値(つまり、特定の`ID`更新された`Name`が変更されてキーの有効期限やリロードを強制することを心配する必要はありません。 はい、メモリ内キャッシングはすばらしい技術であり、ウェブサイトを大幅に高速化しますが、データベース以外のプロセスが同じデータを繰り返し読み込み専用に要求している場合に使用します。しかし、この特定のシナリオでは、データがマージされ、検索値のリストが頻繁に変更される可能性があります(更新されたエントリに比べて新しいエントリが原因です)。 それはすべて言われている、オプション#2は行く方法です。私は15回のTVPではなく、この技術を何度も成功させました。この特定の状況を調整するために、いくつかの最適化/調整をメソッドに行う必要があるかもしれませんが、私がうまく動作することがわかったのは次のとおりです。 * TVPを介してデータを受け入れます。私は`SqlBulkCopy`よりもこれが`SqlBulkCopy`です: + それは簡単に自己完結したストアドプロシージャ + それは、コレクションを複製して、CPUとメモリを浪費している`DataTable`最初にコレクションをコピーする必要なしに、コレクションをDBに完全にストリームするアプリケーションコードに非常にうまく適合します。これには、 `IEnumerable`を返す各コレクションごとにメソッドを作成し、コレクションを入力として受け入れ、 `yield return;`を使用する必要があります`yield return;` `for`または`foreach`ループ内の各レコードを送信します。 * TVPは統計にはあまり適していないため、(これはクエリで`TOP (@RecordCount)`を使用することで軽減できますが)ジョイニングにはあまり適していませんが、データを埋め込むためにのみ使用するため、心配する必要はありません値のない実表 * ステップ1:各エンティティの欠落した名前を挿入します。各エンティティの`[Name]`フィールドにNonClustered Indexが存在し、そのIDがクラスタ化インデックスであると仮定すると、その値は当然インデックスの一部になるため、 `[Name]`は次の操作を支援することに加えて。また、このクライアントの以前の実行(つまり、ほぼ同じエンティティ値)によって、これらのインデックスのデータページがバッファプール(つまりメモリ)にキャッシュされたままになることを忘れないでください。 ``` ;WITH cte AS ( SELECT DISTINCT tmp.[Name] FROM @EntityNumeroUno tmp ) INSERT INTO EntityNumeroUno ([Name]) SELECT cte.[Name] FROM cte WHERE NOT EXISTS( SELECT * FROM EntityNumeroUno tab WHERE tab.[Name] = cte.[Name] ) ``` * ステップ2:ルックアップテーブルのデータページ(つまり「エンティティ」)がステップ1のためにバッファプールに既にキャッシュされている単純な`INSERT...SELECT`すべての「メッセージ」を`INSERT...SELECT` 最後に、推測/推測/推測された推測はテストの代用ではないことに留意してください。ここで「理想」とみなされる内容に影響を与える可能性のある詳細が共有されている可能性があるため、特定の状況に適したものを確認するためにいくつかの方法を試す必要があります。 メッセージが挿入専用の場合、Vladのアイデアはより速いと言います。ここで説明している方法は、より複雑で完全な同期(更新と削除)が必要な状況で使用し、追加の検証と関連する操作データ(ルックアップ値ではない)の作成を行いました。 `SqlBulkCopy`を使用*する*と、ストレート・インサートでは高速になること*があり*ます(ただし、2000レコードしかない場合は、それには大きな違いがあるとは限りませんが)。これは、中間テーブルまたはステージング・テーブルではなく宛先テーブル(メッセージおよび参照)私はVladのアイデアが`SqlBulkCopy`の目的地テーブルへの直接のものだと信じています)。しかしながら、上述のように、外部キャッシュ(すなわち、バッファプールではない)を使用することは、ルックアップ値の更新の問題のために、より誤りを起こしやすい。特に外部キャッシュを使用するほうがわずかに速い場合は、外部キャッシュを無効にするために考慮する価値よりも多くのコードを必要とする可能性があります。追加のリスク/メンテナンスは、どの方法がお客様のニーズに全体的に優れているかを考慮する必要があります。 **更新** コメントに記載された情報に基づいて、われわれは今知っている: * 複数のベンダーが存在する * 各ベンダーが提供する複数の製品があります * 製品はベンダー固有のものではありません。製品は1社以上のベンダーによって販売されています * 製品の特性は特異である * 価格情報には複数のレコードを持つことができるプロパティがあります * 価格設定情報はINSERT専用です(つまり、ポイントインタイム履歴) * 一意の商品はSKU(または類似のフィールド)によって決定されます。 * 作成されると、既存のSKUを使用しているが、別のプロパティ(カテゴリ、製造元など)を持つ*製品は同じ製品*とみなされます。違いは無視されます これをすべて念頭に置いて、私はまだTVPを推奨しますが、そのアプローチを再考し、製品中心ではなくベンダー中心であるようにします。ここでの仮定は、ベンダーがいつでもファイルを送信するということです。ファイルを取得したら、それをインポートします。事前に行う唯一のルックアップはベンダーです。ここに基本的なレイアウトがあります: 1. システムが未知のソースからファイルをインポートするのはなぜですか?なぜなら、VendorIDをすでに持っていると仮定するのは合理的です。 2. バッチでインポートすることができます 3. 次のような`SendRows`メソッドを作成します。 * ファイルを進めるためのFileStreamなどを受け入れる * `int BatchSize`ようなものを受け入れる`int BatchSize` * `IEnumerable`返します。 * TVP構造に一致する`SqlDataRecord`を作成します。 * for BatchSizeが満たされるか、ファイル内にレコードがなくなるまで * データに必要な検証を実行する * データを`SqlDataRecord`マップする * コール・`yield return;` 4. ファイルを開く 5. ファイルにデータがある間 * ストアドプロシージャを呼び出す * VendorIDを渡す * TVPの`SendRows(FileStream, BatchSize)`を`SendRows(FileStream, BatchSize)` 6. ファイルを閉じる 7. 実験する: * FileStreamの周りのループの前にSqlConnectionを開き、ループが終了した後に閉じます * SqlConnectionを開き、ストアドプロシージャを実行し、FileStreamループ内のSqlConnectionを閉じます。 8. さまざまなBatchSize値を試してください。 100、次に200、500などから開始します。 9. ストアドプロシージャは、新しい製品の挿入を処理します このタイプの構造を使用すると、使用されていないProductプロパティが送信されます(つまり、SKUのみが既存の製品の検索に使用されます)。しかし、ファイルサイズに関する上限がないので、非常によくスケールされます。ベンダーが50の製品を送付する場合は、罰金が科せられます。彼らが50kの製品を送っても、大丈夫です。彼らが400万本の製品を送った場合(それは私が取り組んだシステムであり、そのプロパティのすべてで異なる製品情報の更新を処理しました!アプリケーション層やDB層でのメモリの増加がなく、1000万本の製品も処理できます。送信される製品の量に合わせて、インポートにかかる時間が増加するはずです。 **更新2** ソースデータに関する新しい詳細: * Azure EventHubから来ます * C#オブジェクト(ファイルなし)の形式で提供されます。 * 製品の詳細は、OPのシステムのAPIを介して来る * 単一のキューに集められます(データをデータベースに挿入するだけです) データソースがC#オブジェクトの場合は、最初の更新で説明した方法(ie `IEnumerable`を返すメソッド)を介してそのまま送ることができるので、TVPを使用するのが最も確実です。価格/オファーごとのベンダー詳細の1つまたは複数のTVPを送信しますが、単一のプロパティー属性の通常の入力パラメーターを送信します。例えば: ``` CREATE PROCEDURE dbo.ImportProduct ( @SKU VARCHAR(50), @ProductName NVARCHAR(100), @Manufacturer NVARCHAR(100), @Category NVARCHAR(300), @VendorPrices dbo.VendorPrices READONLY, @DiscountCoupons dbo.DiscountCoupons READONLY ) SET NOCOUNT ON; -- Insert Product if it doesn't already exist IF (NOT EXISTS( SELECT * FROM dbo.Products pr WHERE pr.SKU = @SKU ) ) BEGIN INSERT INTO dbo.Products (SKU, ProductName, Manufacturer, Category, ...) VALUES (@SKU, @ProductName, @Manufacturer, @Category, ...); END; ...INSERT data from TVPs -- might need OPTION (RECOMPILE) per each TVP query to ensure proper estimated rows ```