7,000万行超の大量UPSERTで、従来のMERGE（約25分）をUPDATE＋INSERT（未存在のみ追加）に置き換えたところ、開発／単体テストでは約12分まで短縮できたのに、夜間バッチでは計算リソースを増やしているはずなのに約18分かかる――。このズレは「SQLの書き方」よりも、実行計画・統計情報・インデックス・分散／競合・I/Oといった環境要因で起きることが多い。
結論：MERGEかLEFT JOIN方式かは「環境と実行計画」で決まる
最初に結論から言うと、「LEFT JOIN（UPDATE＋INSERT）の方が必ず速い」「MERGEは遅い」という絶対則はありません。大量UPSERTの性能は、SQLの見た目ではなく、DBが選ぶ実行計画（クエリプラン）と、そこで発生するI/O（読み書き）・ログ・ロック・メモリ・（分散環境なら）データ移動のバランスで決まります。
開発環境で約12分、夜間バッチで約18分というズレは、LEFT JOIN方式そのものの良し悪しというよりも、「その環境で何がボトルネックになっているか」が違うサインであることが多いです。スケールアップしても短縮しないのは、CPU以外（ストレージI/O、トランザクションログ、ネットワーク、ロック競合など）が詰まっている、あるいはスケール条件に合わせて実行計画が変わり“別の遅さ”が出ている可能性があります。
大量UPSERTで比較されがちな2パターン
MERGE（単一ステートメントで更新＋追加）
MERGEは「一致したらUPDATE、不一致ならINSERT」を1つの文で表現できます。要件を短く書ける反面、DB製品やバージョンによっては最適化が難しく、内部的にスプール（中間退避）や余計なソートが入りやすいケースもあります。
UPDATE（JOIN）＋INSERT（未存在のみ）
多くの現場で採用されるのが、更新と追加を分離するやり方です。INSERT側はLEFT JOINで「未存在」判定をする実装が多いですが、意味の明確さと重複耐性の観点では、NOT EXISTS（アンチジョイン）で書くほうが事故が少なくなります。
| 観点 | MERGE | UPDATE＋INSERT（LEFT JOIN / NOT EXISTS） |
|---|---|---|
| 可読性 | 1文でまとまる。要件を表現しやすい | 2文になるが意図が分かりやすい。ログや失敗箇所も切り分けやすい |
| 最適化のされやすさ | 製品・版によって差が大きい。スプール等が入ることがある | 単純なUPDATE/INSERTになり、意図したインデックスを使わせやすい |
| テーブルスキャン回数 | 1回で済むこともあるが、内部で複数パスになる場合も | 設計次第で2回以上になりがち（更新と追加で別々に探索） |
| 同時実行・競合 | 条件次第でロックが強くなったり、競合が増えることがある | 分割実行により競合を減らしやすいが、2文の間の整合性に注意 |
| スケール特性 | “計画が当たれば”強いが、外れると伸びない | “計画が当たりやすい”ことがあるが、I/O・ログが支配すると頭打ち |
まず把握すべき「UPSERTの内訳」：更新率・追加率・変更率
7,000万行のUPSERTと言っても、実際に更新される行が何％なのか、追加される行が何％なのかで最適解は変わります。さらに、更新対象の中でも値が変わらないのにUPDATEしている行が多いと、それだけでログ量とロックが増え、スケールが鈍ります。
| 指標 | 意味 | なぜ重要か |
|---|---|---|
| 更新率（マッチ率） | ステージングのうち、既存行と一致してUPDATE対象になる割合 | 高いほどログとロックが支配しやすい（CPUを増やしても伸びにくい） |
| 追加率（未存在率） | ステージングのうち、未存在でINSERTになる割合 | 高いほど索引更新とページ分割（行ストア）/マイクロパーティション（列ストア）の影響が出やすい |
| 変更率（真の更新率） | UPDATE対象のうち、実際に値が変わる行の割合 | 低いなら「差分がある行だけUPDATE」で大幅短縮できる可能性がある |
この内訳を先に数値化しておくと、チューニングの当たりが付けやすくなります（例えば「更新率99%」ならI/O/ログ対策が主戦場、「追加率が大きい」ならINSERT時の索引更新や分散設計が主戦場、という具合です）。
「スケールアップしたらもっと速くなるはず」という期待が外れる理由
計算リソース（CPU/ワーカー）を増やすと、すべての処理が同じ比率で速くなるわけではありません。大量UPSERTは、次のようなCPU以外の要素が支配しやすい処理です。
- トランザクションログの書き込み：UPDATEは書き換えログが増えがちで、ログ書き込み帯域が頭打ちになりやすい
- ストレージI/O：更新対象の探索、インデックス更新、チェックポイント、VACUUM/自動メンテなどで読み書きが増える
- ロック・ラッチ競合：同じキー範囲や同じページへの更新が集中すると待ちが増える
- メモリ不足によるスピル：ハッシュ結合やソートが一時領域に退避して急激に遅くなる
- 分散環境のデータ移動：MPP（分散DWH）ではシャッフルやブロードキャストが効くと伸びない
つまり、夜間バッチで18分かかるのは「CPUが足りない」のではなく、別の律速（ボトルネック）が支配している可能性が高い、ということです。開発環境ではCPUがボトルネックで12分、夜間はI/Oや競合がボトルネックで18分、という構図は珍しくありません。
最優先：単体テスト環境と夜間バッチ環境の“実行計画”を比べる
同じSQLでも、環境が違うと別の実行計画になり得ます。特に次の条件が変わると、最適化の判断が変わり、時間が大きく変動します。
- 統計情報の鮮度（ロード直後に未更新、サンプリング率が低い）
- テーブルサイズ・パーティションの増減、データ偏り
- キャッシュ状態（温かい／冷たい）
- 同時実行（夜間は他バッチが多い、オンライン処理が混ざる）
- 並列度設定やリソースクラス（メモリ上限、同時実行上限）
改善の近道は「SQLをさらにこねる」より、実行計画と実行時内訳（待機・I/O・メモリ）を可視化して差分を取ることです。
観測すると原因が一気に絞れる“4点セット”
| 観測項目 | 見るべき理由 | 遅いときの典型サイン |
|---|---|---|
| 実行計画（推定行数と実績行数） | 見積もりが外れると結合方式や並列化が崩れる | 推定が桁違い／ハッシュ結合のスピル／不要なソート |
| I/O量（読み取り・書き込み） | スキャンや一時領域が増えるとCPUを増やしても効かない | 読み取りが増える／Temp領域が膨らむ |
| 待機要因（ロック、ログ、I/O、ネットワーク） | “何待ちで止まっているか”が分かる | 例：SQL ServerならWRITELOG、PostgreSQLならロック待ち、分散DWHならデータ移動待ちが増える |
| メモリ（メモリグラント、スピル） | 並列化で必要メモリが増え、足りないと一気に遅くなる | ハッシュ/ソートがディスク退避、実行時間が跳ねる |
DB別：実行計画・プロファイルを取る“入口”
| 系統 | 代表例 | まず見る機能（例） |
|---|---|---|
| 行指向RDB | SQL Server / PostgreSQL / MySQL / Oracle | 実行計画（Actual Plan / EXPLAIN ANALYZE）、実行統計（I/O・時間）、ロック/待機 |
| 分散DWH（MPP） | クラウドDWH各種 | クエリプロファイル（ステージ時間、データ移動、スピル）、分散の偏り |
SQL例（代表的な書き方）
INSERT側はLEFT JOINでも書けますが、NULLの扱いと重複耐性を考えると、次のようなNOT EXISTSが扱いやすいことが多いです。
-- 更新：一致するキーだけ更新（DB方言に合わせて調整）
UPDATE tgt
SET
tgt.col1 = src.col1,
tgt.col2 = src.col2,
tgt.updated_at = CURRENT_TIMESTAMP
FROM target_table AS tgt
JOIN staging_table AS src
ON tgt.id = src.id;
-- 追加：存在しないキーだけ挿入
INSERT INTO target_table (id, col1, col2, created_at, updated_at)
SELECT src.id, src.col1, src.col2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM staging_table AS src
WHERE NOT EXISTS (
SELECT 1
FROM target_table AS tgt
WHERE tgt.id = src.id
);ただし、これは「書き方の優劣」を示すものではなく、あくまで実行計画が読みやすく、観測もしやすいという意味合いです。速度はインデックス・統計情報・分散や並列度で変わります。
2文方式の落とし穴：二重挿入（レース）と重複データ
UPDATE＋INSERTに切り分けると、「更新→追加」の間に別トランザクションが同じキーをINSERTしてしまうなど、同時実行時に二重挿入が起きる可能性があります。実運用では、次のガードを入れるのが現実的です。
| リスク | 起きる原因 | 実務でのガード |
|---|---|---|
| 二重挿入（同一キー重複） | INSERT判定後、実INSERTまでの間に他トランザクションがINSERT | JOINキーにユニーク制約を付ける（まずこれ）。必要なら隔離レベル/ロック設定も検討 |
| ステージング側の重複で更新が膨らむ | 同じidが複数行あり、UPDATEが複数回走る/不定になる | ステージング投入後にidで1行に正規化（最新行だけ残す、集約する） |
| NULLキーや不正キー | JOINできずINSERT扱いになって膨らむ | ロード前にバリデーション（NULL/型/範囲）を実施し、異常系を隔離 |
「速度」だけを追うとここを落としがちですが、7,000万行規模は失敗コストも大きいので、データ品質と制約で守ることが結果的に運用コストを下げます。
スケールアップしても伸びない“典型原因”と対策
夜間バッチで時間が伸びない／伸びが鈍いときは、次のどれかに当たっていることが多いです。表の「兆候」を実測できると、原因の特定が早くなります。
| 原因 | 起きること | 兆候 | 対策の方向性 |
|---|---|---|---|
| トランザクションログ律速 | 書き込みがログ待ちで詰まる | ログ関連の待機が多い／CPUは余っている | バッチ分割、更新列削減、インデックス見直し、ログ先ストレージ強化 |
| ストレージI/O律速 | 読み取りや一時領域アクセスで詰まる | I/O待ちが多い／読み取り量が異常 | 索引・パーティション、キャッシュ、スピル削減、データ量削減 |
| データ偏り（スキュー） | 一部キーに更新が集中し、並列が効かない | 一部ワーカーだけ長時間動く | 分散キー見直し、事前の集約・重複排除、ホットキー回避 |
| 統計情報が古い／不足 | 件数見積もりが外れ、非効率な結合・ソートになる | 推定行数と実績行数が大きく乖離 | ロード後に統計更新、サンプリング率調整、ヒストグラム確認 |
| インデックス不足・不適合 | 探索がスキャンになり、更新コストも増える | フルスキャン／キー探索が遅い | JOINキーに適切な索引、更新列の見直し、不要索引の整理 |
| 並列化の副作用 | 並列オーバーヘッドやメモリ不足で逆に遅くなる | スピル発生、メモリ不足、交換（Exchange）待ち | 並列度調整、メモリ上限/クラス調整、クエリ分割 |
| 夜間のリソース競合 | 他バッチと奪い合いになり伸びない | 同時刻だけ遅い／日によって揺れる | ジョブ順序・同時実行数調整、優先度（リソースクラス）設定 |
インデックス設計：JOINキーが“速さ”を決める
7,000万行規模のUPSERTでは、JOINキー（例：id）に対して「探すのが速い」ことと、更新時に「余計なインデックス更新が発生しない」ことが両方重要です。
最低限の考え方
- ターゲット側：JOINキーにユニーク（または高選択性）なインデックスがあるか
- ステージング側：JOINキーでソート／ハッシュが組みやすい状態か（必要ならインデックスやクラスタリング）
- 更新列：更新のたびに更新されるインデックス（含む）を増やし過ぎていないか
| 対象 | よく効く設計 | 注意点 |
|---|---|---|
| target_table | JOINキー（id）にユニークインデックス／パーティションキーと整合したクラスタ | インデックスが多すぎると更新コストが跳ねる（INSERT/UPDATEの両方で） |
| staging_table | ロード後に重複排除（idで1行に）／idで索引 or ソート済みを維持 | ステージングに重複があると更新・追加が膨らみ、計画も崩れる |
| 監査列（updated_at等） | 本当に必要なときだけ更新（差分がない行は更新しない） | 「値が変わっていないのにUPDATEする」だけでログ量とロックが増える |
差分がない行を更新しない（ログ削減に直結）
大量UPSERTで効きやすいのが、「変化がある行だけUPDATEする」工夫です。たとえばハッシュ値や更新対象列の比較で、変更のない行を除外すると、ログ量・ロック・インデックス更新が減り、スケールしやすくなることがあります。
-- 例：値が変わる行だけ更新する（比較列は要件に合わせて）
UPDATE tgt
SET
tgt.col1 = src.col1,
tgt.col2 = src.col2,
tgt.updated_at = CURRENT_TIMESTAMP
FROM target_table AS tgt
JOIN staging_table AS src
ON tgt.id = src.id
WHERE (tgt.col1 IS DISTINCT FROM src.col1)
OR (tgt.col2 IS DISTINCT FROM src.col2);※ IS DISTINCT FROM は方言依存です。対応していないDBではNULLを考慮した比較式に置き換えます。
統計情報：ロード直後に“更新されていない”が最も多い落とし穴
開発環境ではテーブルが小さかったり、たまたま統計が新しかったりして計画が当たりやすいのに、本番夜間はデータが増えた／偏ったのに統計が古いままで、結合方式が外れて時間が伸びる、というパターンは非常に多いです。
- ステージング投入後に統計を更新していない
- 自動統計があっても更新タイミングが遅い（しきい値）
- サンプリング率が低く、偏りを捉えられていない
統計の問題は、推定行数と実績行数の乖離として実行計画に現れます。乖離が大きい場合は、まず統計更新が第一候補です。
分散・偏り：MPP環境では“データ移動”がコストの主役になる
クラウドDWHや分散DB（MPP）では、計算リソースを増やすとワーカー数が増えますが、同時にネットワーク越しのデータ移動も増えます。特にUPSERTはターゲットとステージングのキー分布がズレると、シャッフル（再分散）が発生し、CPUよりネットワークとディスクが支配します。
偏り（スキュー）を疑うべきサイン
- 処理時間の大半が「一部のステージ」や「一部ワーカー」に偏る
- トップNのキーが極端に多い（同一idが大量、または特定日付に集中）
- スケールアップしても、ある一定以上はほぼ短縮しない
対策としては、分散キー（distribution key）やクラスタリングの見直しに加え、ステージング側で事前に重複排除・集約してからUPSERTするのが実務的です。UPSERTの前に「idごとに最終行だけ残す」「同一キーの更新を1回に畳む」だけで、更新量が何分の一にもなり得ます。
バッチ分割：7,000万行を“1トランザクションで一気に”が遅さを呼ぶ
一括で処理すると、ログ・ロック・一時領域が膨らみ、途中失敗時のリカバリも重くなります。そこで効果的なのが、キー範囲や日付でバッチ分割し、コミットを刻む方法です。
分割設計の例
- IDレンジ（1,000万件単位など）で区切る
- 日付（取込日、更新日）で区切る
- パーティション単位で区切る（可能ならパーティションスイッチも検討）
| 分割の狙い | 得られる効果 | 注意点 |
|---|---|---|
| ログ量の平準化 | ログ書き込み待ちを減らし、途中再実行が楽になる | 分割しすぎるとオーバーヘッドが増える（最適サイズを探す） |
| ロック競合の低減 | 同じ範囲への同時更新が減り待ちが減る | アプリ側の参照と干渉しない時間帯・範囲設計が必要 |
| スピルの抑制 | ハッシュ/ソートに必要なメモリが減り、ディスク退避を防げる | 範囲選択が偏ると逆効果（偏りチェックが前提） |
夜間バッチ特有の“見落とし”チェック
「開発では速いのに夜間だけ遅い」は、ほぼ必ず理由があります。SQL以外の要因を疑うチェックポイントを並べます。
- 同時実行ジョブ：同じテーブル／同じストレージ／同じログ領域を使う処理が並走していないか
- キューイング／スロットリング：夜間だけリソースクラスや同時実行上限で待たされていないか
- キャッシュ：開発は繰り返し実行でキャッシュが温かいが、本番はコールドスタートになっていないか
- メンテナンス：統計更新、バックアップ、クリーンアップ、再編成が同じ時間帯に走っていないか
- ネットワーク：分散環境でデータ移動が増えるタイミング（他負荷）と重なっていないか
MERGEをやめる／続ける判断基準
MERGEは便利ですが、次の観点で判断すると失敗しにくいです。
| 判断軸 | MERGEが向きやすい | UPDATE＋INSERTが向きやすい |
|---|---|---|
| 運用上の切り分け | 1文で完結させたい | 更新と追加の件数を別々に把握したい／失敗箇所を切り分けたい |
| 性能の安定性 | 実行計画が安定しており、統計・索引が整っている | 計画がブレやすい／統計更新が難しい／夜間の条件変動が大きい |
| 同時実行の安全性 | 適切な制約・隔離レベルで競合を制御できる | トランザクションを分割し、再実行しやすい設計にしたい |
どちらを選んでも、最終的には（1）適切な制約（ユニークキー）と（2）統計・索引と（3）観測に基づくチューニングが揃わないと、7,000万行規模では安定しません。
改善アクションチェックリスト（現場でそのまま使える）
最後に、今回のような「単体テストでは速いのに夜間バッチで伸びない」ケースで、効果が出やすい順にチェック項目を整理します。上から順に潰すと、遠回りせず原因に到達しやすくなります。
| 優先度 | チェック項目 | 具体的にやること | 狙い |
|---|---|---|---|
| 高 | 単体テスト／夜間バッチの実行計画を比較 | 推定行数と実績行数、結合方式（ハッシュ/マージ/ネスト）、スピル有無、一時領域の有無を並べて差分を見る | 「SQL差」か「環境差」かを最短で切り分ける |
| 高 | 統計情報の更新（特にステージング投入後） | ロード直後に統計更新・ANALYZE相当を実行。サンプリング率やヒストグラムも確認 | 件数見積もりの外れによる計画崩壊を防ぐ |
| 高 | JOINキーのインデックス／制約を点検 | ターゲットのJOINキーにユニーク制約＋適切な索引。ステージングの重複排除（idで1行に）もセットで実施 | 探索を速くし、二重挿入や無駄更新を防ぐ |
| 中 | データ偏り（スキュー）と分散を確認 | キーの頻度分布、パーティション/分散単位の行数差、ワーカー別処理時間の偏りを確認。偏りが大きいなら分散キー再検討 | スケールアップが効かない根本原因を潰す |
| 中 | UPDATE/INSERTのバッチ分割 | IDレンジや日付で分割し、コミットを刻む。失敗時の再実行が容易な単位にする | ログ・ロック・メモリ圧迫を避けて安定化する |
| 中 | 夜間の実行条件を固定して比較 | 同時実行ジョブを減らした時間帯で再計測、または同じリソースクラスで実行して揺れを抑える | 「夜間だけ遅い」要因（競合/スロットリング）を切り分ける |
| 低 | 差分がない行を更新しない | 変更率が低い場合、比較条件を追加してUPDATE対象を絞る（監査列の更新も要件を見直す） | ログ量とI/Oを減らし、スケールしやすくする |
最短で原因を特定するための実験設計
「どちらが速いか」を議論する前に、比較条件を揃えて“科学実験”の形にします。これをやるだけで、原因が環境依存なのかSQL依存なのかがはっきりします。
- 同じデータ（同じステージング内容、同じターゲット状態）で比較する
- 同じ実行条件（同時実行なし、同じリソースクラス、同じ並列度）で1回目・2回目を分けて測る
- 実行計画と実行時統計（行数、I/O、待機、スピル）を必ず保存する
- 結果は1回で決めず、揺れを見る（夜間は特に）
まとめ：この相談で見落としがちなポイント
- LEFT JOIN方式が速いのは“その環境・その計画”でそうだっただけで、常勝ではない
- スケールアップしても速くならないのは、CPU以外（ログ/I/O/競合/データ移動）が支配している合図
- 最優先は「単体テスト vs 夜間バッチ」の実行計画と待機/I/O/スピルの差分比較
- 統計更新、JOINキー索引、偏り対策、差分更新、バッチ分割で改善余地が大きい
最終的に“どちらが正解”ではなく、あなたのデータ分布・インデックス・統計・運用条件で最も安定して速い形を見つけることが正解です。観測→仮説→検証のサイクルで、夜間バッチの18分をさらに詰められる可能性は十分あります。
コメント