質問

SQL Server の BULK INSERT コマンドを使用したかなり古いコードを書き直さなければならないところですが、その代わりに TVP を使用したストアド プロシージャに切り替えることを考えるべきかもしれません。

なぜ私がこの質問をするのかを説明するのに役立つかもしれないいくつかの背景情報。

• データは、実際には Web サービスを介して入ってきます。 ウェブサービスはテキストファイルをデータベースサーバーの共有フォルダーに書き込みます。 BULK INSERT . このプロセスはもともと SQL Server 2000 で実装されたもので、当時は数百の INSERT ステートメントをサーバーでチャックする以外に方法がありませんでしたが、これは実際に元のプロセスであり、パフォーマンス上の障害でした。

• データは永続的なステージング テーブルに一括挿入され、その後、はるかに大きなテーブルにマージされます (その後、ステージング テーブルから削除されます)。

• 挿入するデータの量は "large"ですが、巨大ではありません。通常は数百行で、まれに最高で 5-10k 行になることがあるかもしれません。 したがって、私の直感では BULK INSERT がログを取らないオペレーションであることは その というのは大きな違いです（もちろん、私は確信が持てないので質問しました）。

• 挿入は実際にはもっと大きなパイプライン化されたバッチ処理の一部であり、連続して何度も行われる必要があります。 は クリティカルです。

を置き換えたい理由は BULK INSERT をTVPに置き換えたい理由は以下の通りです。

• NetBIOS 上でテキスト ファイルを書き込むことは、おそらくすでに時間がかかっており、アーキテクチャの観点からはかなりぞっとするようなものです。

• ステージング テーブルはなくすことができる (そして、なくすべきである) と私は考えています。 ステージング テーブルがある主な理由は、挿入されたデータは、挿入と同時に他のいくつかの更新に使用される必要があり、ほとんど空のステージング テーブルを使用するよりも、巨大な本番テーブルから更新を試みる方がはるかにコストがかかるからです。 TVPの場合、パラメータは基本的に です。 ステージングテーブルは、メインの挿入の前または後に、私が望むことを何でもすることができます。

• 私は、重複チェック、クリーンアップコード、および一括挿入に関連するすべてのオーバーヘッドをほとんどなくすことができました。

• サーバーが一度にいくつかのこれらのトランザクションを取得した場合、ステージング テーブルまたは tempdb のロック競合を心配する必要はありません (私たちはそれを避けようとしていますが、それは起こります)。

私は当然、本番環境に何かを置く前にこれをプロファイルするつもりですが、すべての時間を費やす前に、最初に周囲に尋ねて、この目的のために TVP を使用することについて発行する厳しい警告を誰かが持っているかどうかを確認するのは良い考えかもしれないと思いました。

SQL Server 2008 に詳しい方で、これを試したり、少なくとも調査したことのある方は、どのように判断されたでしょうか。 たとえば、数百から数千行の挿入がかなり頻繁に行われる場合、TVP は必要不可欠なものでしょうか。 バルク挿入と比較して、パフォーマンスに大きな違いがあるのでしょうか?

更新：クエスチョンマークが 92% 減りました!

(別名: テスト結果)

最終結果は、36 段階のデプロイ プロセスのように感じられた後、現在本番稼動しています。 両方のソリューションが広範囲にわたってテストされました。

• 共有フォルダーのコードを破棄して SqlBulkCopy クラスを直接使用します。
• TVPを使ったストアドプロシージャへの切り替え。

ただ、読者がイメージできるように 何 このデータの信頼性に対する疑念を払拭するために、このインポート プロセスがどのようなものであるかについて、より詳しく説明します。 が実際に行っていること :

1. 通常20-50データポイント程度の時間的データ列から始める（数百になることもある）。

2. データベースとはほとんど関係なく、その上でとんでもない処理をする。 この処理は並列化されているので、(1)の配列のうち8～10個程度が同時に処理されている。 各並列処理で3つの配列が追加で生成されます。

3. 3つの配列すべてと元の配列を取り出し、バッチにまとめます。

4. 現在終了している8～10個の処理タスクすべてからのバッチを1つの大きなスーパーバッチにまとめます。

5. のどちらかを使ってインポートします。 BULK INSERT 戦略（次のステップ参照）、またはTVP戦略（ステップ8へスキップ）のいずれかを使用してインポートします。

6. を使用します。 SqlBulkCopy クラスを使用して、スーパーバッチ全体を4つの永続的なステージングテーブルにダンプします。

7. ストアドプロシージャを実行し、(a) 2つのテーブルで、いくつかの JOIN 条件を実行し、次に (b) MERGE を、集計データと非集計データの両方を用いて、6つの生産テーブルで実行します。 (終了)

   または

8. 4を生成する DataTable オブジェクトを生成します。そのうちの3つは、残念ながらADO.NET TVPで適切にサポートされていないCLR型を含んでいるので、文字列表現として押し込む必要があり、パフォーマンスに少し影響があります。

9. TVPをストアドプロシージャに送り、(7)と基本的に同じ処理を行いますが、受信したテーブルを直接使用します。 (終了)

結果はほぼ同じでしたが、データがわずかながら1000行を超えたときでも、最終的にはTVPアプローチの方が平均的に良い結果を出しました。

このインポート処理は何千回も連続して実行されるので、すべてのマージを完了するのに何時間 (そう、時間) かを数えるだけで、平均時間を得るのは非常に簡単であることに留意してください。

元々、平均的なマージは、ほぼ正確に 8 秒で完了しました (通常の負荷の場合)。 NetBIOS クラッジを削除して SqlBulkCopy に切り替えると、ほぼ正確に 7 秒に短縮されました。 TVP に切り替えると、さらに時間が短縮され 5.2秒 に短縮されました。 これは 35% の改善 であり、決して悪いことではありません。 また、これは SqlBulkCopy .

私は、実際のところ、真の改善はこれよりもはるかに多かったと確信しています。代わりに、すべてのデータ処理を行う Web サービスが、送られてくる要求の数に耐え切れなくなり始めていたのです。 CPUもデータベースI/Oも最大値には達しておらず、大きなロック動作もありません。 場合によっては、連続したマージの間に数秒のアイドル時間が発生することもありました。 わずかな隙間はありましたが、これは SqlBulkCopy . しかし、これは別の日のお話になると思います。

おわりに テーブル値パラメータは本当に BULK INSERT 操作よりも優れたパフォーマンスを発揮します。

もう一点、ステージングテーブルに賛成している人たちの不安を解消するために付け加えたいと思います。 ある意味、このサービス全体が1つの巨大なステージング・プロセスなのです。 プロセスの各ステップは厳重に監査されています。 <必要ありません。 必要ありません。 を必要としません (実際にはほとんど発生しませんが)。 私たちがしなければならないのは、サービスにデバッグ フラグを設定することであり、デバッガーにブレークするか、データベースの代わりにファイルにデータをダンプすることです。

言い換えれば、私たちはすでにプロセスについて十分すぎるほど理解しており、ステージング テーブルの安全性を必要としません。そもそもステージング テーブルを用意した唯一の理由は、すべての INSERT と UPDATE ステートメントを使用する必要がありました。 元のプロセスでは、ステージング データはいずれにせよステージング テーブルに数分の 1 秒しか存在しないため、メンテナンス/保守性の観点からは何の価値もありません。

また、以下のように ではなく をひとつひとつ BULK INSERT 操作をTVPに置き換えたわけではありません。 より大きな量のデータを扱う、あるいはDBにデータを投げる以外に特別なことをする必要がないいくつかのオペレーションは、依然として SqlBulkCopy . を使用しています。 私はTVPがパフォーマンスの万能薬だと言っているのではありません。 SqlBulkCopy に対して成功したというだけです。

というわけで、これで終わりです。 最も関連性の高いリンクを見つけた TToni にポイントを与えますが、他の回答にも感謝します。 本当にありがとうございます。

どのように解決するのですか？

私はまだ TVP を使用した経験がありませんが、MSDN に BULK INSERT との素晴らしいパフォーマンス比較表が掲載されています。 ここに .

彼らは、BULK INSERT は起動コストが高いが、その後はより高速であると述べています。リモート クライアント シナリオでは、約 1000 行 (シンプルなサーバー ロジックの場合) で境界線を引いています。彼らの説明から判断すると、TVPの使用は問題ないでしょう。パフォーマンスへの影響は、あったとしてもごくわずかであり、アーキテクチャ上の利点は非常に優れているように思われます。

編集: 余談ですが、SqlBulkCopy オブジェクトを使用することで、サーバーローカルファイルを回避し、かつバルクコピーを使用することができます。DataTable にデータを入力し、それを SqlBulkCopy インスタンスの "WriteToServer"-Method に送り込むだけでよいのです。簡単に使用でき、非常に高速です。