C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[4]: SQLServerのMERGE文のパフォーマンスについて

分類:[データベース全般]　

SQL実行の負荷観点についての質問ですが。

SQLServerでは2008Versionより、MERGE文が提供され、いわゆるUPSERTが１文で実装できるようになりましたが、
SQL実行のパフォーマンス観点では、
①存在チェックSQL発行
②Insert(or UPDATE)SQL発行
と、服問い合わせSQLと同様に２個SQLが発行されている様に見えました。

パフォーマンスの比較ではこのMERGE文を使用するより、DELETE→INSERTでの一括挿入の方がDB負荷が軽いと思っているのですが、いかがでしょうか？
お分かりになる方いらっしゃいましたら宜しくお願い致します。

参考：
https://johobase.com/sqlserver-merge-insert-update-delete/
https://learn.microsoft.com/ja-jp/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

■No101484 (ぼぼさん) に返信

ケースバイケースだと思います
レコードの数やインデックスの数やwhereの条件やコミット間隔などでいくらでも変動しそうな気がします
実際のデータでベンチマークテストやったが良いです

■No101486 (伝説のカレーさん) に返信

ありがとうございます。確かに実際作ってみれば分かりますね。
ただ、物理的にSQLの発行回数で考えると１１回に対し２１回ですよね。。
確かにテーブルに何万件入ってるのかでまた変わって来そうですけど、それでも(INDEX効かしても)前者かなと思います。。

中間テーブルに１０件更新
◆DELETE→INSERT
①DELETE
②10件INSERT

◆MERGE
①全件UPDATE(削除フラグOFFとか)
②10件MERGE(実質x2)

■No101487 (ぼぼさん) に返信
すみません。Merge文ってそもそも２つのテーブルを比較して入れるんですね。
Mergeの言葉通りですが、Upsertと勘違いしてました。
結局、存在チェックのSQLはかけないといけないんですね。

2023/03/05(Sun) 14:38:19 編集(投稿者)

■No101487 (ぼぼさん) に返信
> 中間テーブルに１０件更新
> ◆DELETE→INSERT
> ①DELETE
> ②10件INSERT
>
> ◆MERGE
> ①全件UPDATE(削除フラグOFFとか)
> ②10件MERGE(実質x2)

比較するとしたら、

◆DELETE→INSERT
① 10件分のキーを指定して DELETE
② 10件INSERT

◆MERGE
① 10件MERGE

では？

■No101488 (ぼぼさん) に返信
> すみません。Merge文ってそもそも２つのテーブルを比較して入れるんですね。

CREATE TABLE HOGE(ITEM1 VARCHAR(2), ITEM2 VARCHAR(2))
というテーブルがあったとして

MERGE HOGE AS A
USING (SELECT 1 AS DUMMY) AS B ON A.ITEM1 = 'A'
WHEN MATCHED
THEN UPDATE SET ITEM2 = 'C'
WHEN NOT MATCHED BY TARGET
THEN INSERT (ITEM1, ITEM2) VALUES ('A', 'B')

のように書けます。

■No101484 (ぼぼさん) に返信
> パフォーマンスの比較ではこのMERGE文を使用するより、DELETE→INSERTでの一括挿入の方がDB負荷が軽いと思っているのですが、いかがでしょうか？
> お分かりになる方いらっしゃいましたら宜しくお願い致します。

レコード数やWhere句の複雑度にもよりますが、一般論としてはその通りと思います。
ただ、MERGEを採用するかどうかは性能観点よりSQLを発行するプログラム側でのIF分岐が必要なくなるため、単純にステップ数が減らせる→可読性が上がることの効果の方が大きいような気もします。

また、ログを詳細に取っていて、「登録」なのか「更新」なのかを区別して件数を書きだす必要がある開発現場では不向きですね。
あくまでMERGEを発行したことでの影響レコード数しかたしか取得できないので。

■No101492 (大谷刑部さん) に返信
> ■No101484 (ぼぼさん) に返信
>>パフォーマンスの比較ではこのMERGE文を使用するより、DELETE→INSERTでの一括挿入の方がDB負荷が軽いと思っているのですが、いかがでしょうか？
>>お分かりになる方いらっしゃいましたら宜しくお願い致します。
>
> レコード数やWhere句の複雑度にもよりますが、一般論としてはその通りと思います。
> ただ、MERGEを採用するかどうかは性能観点よりSQLを発行するプログラム側でのIF分岐が必要なくなるため、単純にステップ数が減らせる→可読性が上がることの効果の方が大きいような気もします。
>
> また、ログを詳細に取っていて、「登録」なのか「更新」なのかを区別して件数を書きだす必要がある開発現場では不向きですね。
> あくまでMERGEを発行したことでの影響レコード数しかたしか取得できないので。

すいません逆の認識だったのですね。
MERGEの方が本数が少ない分、トータルコストは多分低いです。
ただ、性能上、どっちが処理時間を要するかはケースバイケースでしょう。
DeleteもWhere句が複雑だとものすごく時間がかかるケースもあるのでInsertがUpdateよりかからないから、本数と逆転してDelete-Insertの方がかえって速くなるとも必ずしも言えないかと。

■No101490 (大谷刑部さん) に返信
＞比較するとしたら、
＞◆MERGE
＞① 10件MERGE
＞では？

ありがとうございます。
実際、実装のイメージで言うと
初回001,002,003と入っており、２回目では002,003,004とパラメータ受けした場合、
001が残ってしまうはずなので、DELETE→INSERT時と同様に一旦初期化する必要があると思います。
→論理削除の場合。

■No101492 (大谷刑部さん) に返信
＞MERGEを採用するかどうかは性能観点よりSQLを発行するプログラム側でのIF分岐が必要なくなるため、単純にステップ数が減らせる→可読性が上がることの効果の方が大きいような気もします。

なるほどです。そういう見方も確かにありますね。

2023/03/07(Tue) 07:57:18 編集(投稿者)

■No101495 (ぼぼさん) に返信
> 初回001,002,003と入っており、２回目では002,003,004とパラメータ受けした場合、
> 001が残ってしまうはずなので、DELETE→INSERT時と同様に一旦初期化する必要があると思います。
> →論理削除の場合。

違う更新を比較しても意味がありません。
全件 DELETE して INSERT という処理では MERGE の出番は無いように思います。
（更新データ用のテーブルを作れば WHEN NOT MATCHED BY SOURCE を使って書けないこともないですが）

■No101495 (ぼぼさん) に返信
> ■No101490 (大谷刑部さん) に返信
> ＞比較するとしたら、
> ＞◆MERGE
> ＞① 10件MERGE
> ＞では？
>
> ありがとうございます。
> 実際、実装のイメージで言うと
> 初回001,002,003と入っており、２回目では002,003,004とパラメータ受けした場合、
> 001が残ってしまうはずなので、DELETE→INSERT時と同様に一旦初期化する必要があると思います。
> →論理削除の場合。

そのケースで001を消さなきゃいけない仕様なんだとすると、Delete-Insert一択ですよね。多分。
MergeはあくまであればUpdateなければInsertを一つのSQLで記載できる構文なので、001を消しつつMergeを使いたいなら、2回目のレコードが存在しない(not Exist等)を条件にDeleteして
Merge発行となるのでそもそもSQL文の本数を減らせる効果が半減するので採用するメリットが低くなると思います。

■No101484 (ぼぼさん) に返信
> ①存在チェックSQL発行
> ②Insert(or UPDATE)SQL発行
> と、服問い合わせSQLと同様に２個SQLが発行されている様に見えました。
2 個発行されているように見えた、というのは、
実行計画などから判断されたということでしょうか?

仮にその方法だと、既存データの更新であろうと、新規データの追加であろうと、
常に 2 回の操作が必要になってしまうかと思います。DELETE → INSERT 法も同様ですよね。

さらに DELETE → INSERT 法に至っては、データ構成次第では
断片化をより招きやすくなる可能性もありそうです。
(といっても、DELETE 後のエクステントに収まるサイズの INSERT なら HWM は変わらないですが)

たとえば 1 件だけの更新の場合は、「存在チェック SQL」は発行せずに、
　(1) UPDATE を行ってみる
　(2) その更新件数が 0 件だったら INSERT を行う
という手法もあります。

この場合、既存レコードなら(2)の操作が不要になるので、DML 操作が 1 回だけで済みます。
新規レコードの場合は(1)と(2)の両方が必要になりますけれどね。

更新件数の取得は、Transact SQL ならば @@ROWCOUNT で得られます。
あるいは ADO.NET の DbCommand.ExecuteNonQuery の戻り値や、
ADO の Command.Execute の RecordsAffected パラメーターでも得られます。

とはいえ、一度に複数レコードを同時に INSERT/UPDATE するようなケースでは使えないので
その場合は MERGE を使った方が良いでしょう。母数が多い場合、Lookup アクセスされると
探索コストが高くなってしまうので、抽出条件は主キーのみで行えるようにするか、あるいは
インデックス以外の値も条件に必要なら、付加列インデックスを用いた設計にするといった対処をとります。

■No101495 (ぼぼさん) に返信
> 実際、実装のイメージで言うと
> 初回001,002,003と入っており、２回目では002,003,004とパラメータ受けした場合、
> 001が残ってしまうはずなので、DELETE→INSERT時と同様に一旦初期化する必要があると思います。
> →論理削除の場合。

論理削除の場合と物理削除の場合とで選択肢も変わってくるわけですが、
データベース製品やそのバージョンによっては、DELETE も同時に行えることがありますね。

SQL Server で、INSERT/UPDATE/DELETE を同時に行う MERGE 構文の例
https://johobase.com/sqlserver-merge-insert-update-delete/#DELETE

Oracle で、INSERT/UPDATE/DELETE を同時に行う MERGE 構文の例
https://www.ne.jp/asahi/hishidama/home/tech/oracle/merge.html

2023/03/08(Wed) 10:59:05 編集(投稿者)

操作するデータ件数が少ない場合は、DB操作自体に掛かる時間より通信時間の方がよほど長くなるので、
DBへSQLを投げる回数が減る方が有利な場合があります。
机上の空論より、伝説のカレーさんが言うようにベンチマークを取るべきです。

C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[4]: SQLServerのMERGE文のパフォーマンスについて

過去ログには書き込み不可