☰

複数のINSERTステートメントと複数のVALUESを持つ単一のINSERT 質問する

tag-icon • tag-icon sql sql-server performance sql-server-2008 t-sql
複数のINSERTステートメントと複数のVALUESを持つ単一のINSERT 質問する

1000 個の INSERT ステートメントを使用した場合のパフォーマンス比較を実行しています。

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0)
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1)
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999)

1000 個の値を持つ単一の INSERT ステートメントを使用する場合との比較:

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
VALUES 
('db72b358-e9b5-4101-8d11-7d7ea3a0ae7d', 'First 0', 'Last 0', 0),
('6a4874ab-b6a3-4aa4-8ed4-a167ab21dd3d', 'First 1', 'Last 1', 1),
...
('9d7f2a58-7e57-4ed4-ba54-5e9e335fb56c', 'First 999', 'Last 999', 999)

驚いたことに、結果は私が考えていたものとは逆でした。

  • 1000 INSERT ステートメント:290ミリ秒。
  • 1000 個の値を持つ 1 つの INSERT ステートメント:2800ミリ秒。

テストは、測定に SQL Server Profiler を使用して MSSQL Management Studio で直接実行されます (SqlClient を使用して C# コードから実行しても同様の結果が得られました。これは、すべての DAL レイヤーのラウンドトリップを考慮するとさらに驚くべきことです)。

これは合理的、あるいは何らかの形で説明できるでしょうか? より高速なはずの方法が、なぜ 10 倍 (!) もかかるのでしょうか?悪いパフォーマンス？

ありがとう。

編集: 両方の実行プランを添付します:実行計画

ベストアンサー1

追加：SQL Server 2012ではこの分野でパフォーマンスが若干改善されていますが、以下に挙げる特定の問題には対処していないようです。どうやら修正されるようだ次のメジャーバージョンでは後SQL Server 2012!

計画では、単一の挿入がパラメーター化されたプロシージャ (おそらく自動パラメーター化) を使用していることが示されているため、これらの解析/コンパイル時間は最小限になるはずです。

これについてもう少し調べてみようと思ったので、ループを設定しました（脚本) を実行し、節の数を調整しVALUESてコンパイル時間を記録してみました。

次に、コンパイル時間を行数で割って、句ごとの平均コンパイル時間を算出しました。結果は以下のとおりです。

グラフ

250 個の節が存在するまではVALUES、コンパイル時間/節数はわずかに増加する傾向がありますが、それほど劇的な増加ではありません。

グラフ

しかし、突然の変化が起こります。

そのデータのセクションを以下に示します。

+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
|  245 |            528 |          41 |          2400 | 0.167346939   |
|  246 |            528 |          40 |          2416 | 0.162601626   |
|  247 |            528 |          38 |          2416 | 0.153846154   |
|  248 |            528 |          39 |          2432 | 0.157258065   |
|  249 |            528 |          39 |          2432 | 0.156626506   |
|  250 |            528 |          40 |          2448 | 0.16          |
|  251 |            400 |         273 |          3488 | 1.087649402   |
|  252 |            400 |         274 |          3496 | 1.087301587   |
|  253 |            400 |         282 |          3520 | 1.114624506   |
|  254 |            408 |         279 |          3544 | 1.098425197   |
|  255 |            408 |         290 |          3552 | 1.137254902   |
+------+----------------+-------------+---------------+---------------+

直線的に増加していたキャッシュされたプランのサイズが突然減少しましたが、CompileTime は 7 倍に増加し、CompileMemory は急増しました。これは、プランが自動パラメータ化されたプラン (1,000 個のパラメータを持つ) からパラメータ化されていないプランへのカットオフ ポイントです。その後は、直線的に効率が低下しているように見えます (特定の時間に処理される値句の数に関して)。

なぜそうなるのかはわかりません。おそらく、特定のリテラル値のプランをコンパイルするときに、線形にスケールしないアクティビティ (並べ替えなど) を実行する必要があると考えられます。

完全に重複行で構成されるクエリを試したとき、キャッシュされたクエリ プランのサイズには影響しないようです。また、定数のテーブルの出力順序にも影響しません (ヒープに挿入しているため、たとえ影響があったとしても、ソートに費やされる時間は意味がありません)。

さらに、クラスター化インデックスがテーブルに追加された場合、プランには明示的なソート手順が表示されるため、実行時のソートを回避するためにコンパイル時にソートが行われていないように見えます。

プラン

これをデバッガーで確認しようとしましたが、私のバージョンの SQL Server 2008 のパブリック シンボルが利用できないようだったので、代わりにUNION ALLSQL Server 2005 の同等の構造を確認する必要がありました。

典型的なスタックトレースは以下のとおりです。

sqlservr.exe!FastDBCSToUnicode()  + 0xac bytes  
sqlservr.exe!nls_sqlhilo()  + 0x35 bytes    
sqlservr.exe!CXVariant::CmpCompareStr()  + 0x2b bytes   
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare()  + 0x18 bytes  
sqlservr.exe!CXVariant::CmpCompare()  + 0x11f67d bytes  
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion()  + 0xe2 bytes   
sqlservr.exe!CConstraintProp::PcnstrUnion()  + 0x35e bytes  
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive()  + 0x11a bytes    
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler()  + 0x18f bytes    
sqlservr.exe!CLogOpArg::DeriveGroupProperties()  + 0xa9 bytes   
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties()  + 0x40 bytes    
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x18a bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!CQuery::PqoBuild()  + 0x3cb bytes  
sqlservr.exe!CStmtQuery::InitQuery()  + 0x167 bytes 
sqlservr.exe!CStmtDML::InitNormal()  + 0xf0 bytes   
sqlservr.exe!CStmtDML::Init()  + 0x1b bytes 
sqlservr.exe!CCompPlan::FCompileStep()  + 0x176 bytes   
sqlservr.exe!CSQLSource::FCompile()  + 0x741 bytes  
sqlservr.exe!CSQLSource::FCompWrapper()  + 0x922be bytes    
sqlservr.exe!CSQLSource::Transform()  + 0x120431 bytes  
sqlservr.exe!CSQLSource::Compile()  + 0x2ff bytes

したがって、スタック トレース内の名前を見ると、文字列の比較に多くの時間がかかっていることがわかります。

このKB記事DeriveNormalizedGroupPropertiesは、かつて呼ばれていたものと関連していることを示しています正規化クエリ処理の段階

この段階は現在、バインディングまたは代数化と呼ばれ、前の解析段階から出力された式解析ツリーを受け取り、代数化された式ツリー（クエリプロセッサツリー）を出力して最適化（この場合は単純なプラン最適化）に進みます。[参照]。

もう1つ実験してみました（脚本) は、元のテストを再実行しますが、3 つの異なるケースを調べます。

  1. 名と姓は重複のない 10 文字の長さの文字列です。
  2. 名と姓の重複のない 50 文字の長さの文字列。
  3. すべて重複する長さ 10 文字の名と姓の文字列。

グラフ

文字列が長くなるほど状況は悪くなり、逆に重複が増えるほど状況は良くなることがはっきりとわかります。前述のように、重複はキャッシュされたプランのサイズには影響しないため、代数化された式ツリー自体を構築するときに重複を識別するプロセスが存在するはずだと私は推測します。

編集

この情報が活用される場所の一つは@Lieven がここで示した

SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID)

コンパイル時にName列に重複がないことが判断できるため1/ (ID - ID)、実行時にセカンダリ式による並べ替えがスキップされ (プラン内の並べ替えには 1 つのORDER BY列のみが含まれる)、ゼロ除算エラーは発生しません。重複がテーブルに追加されると、並べ替え演算子によって 2 つの並べ替え列が表示され、予期されるエラーが発生します。

おすすめ記事