※検証にはSQLServerを使用しています。
プロダクション環境で1分ほどかかってしまうクエリを実行する機能があったため、1秒程度までチューニングしたときのお話です。
該当のSQLは、20個ほどのテーブルをJOINして結果をSELECTする複雑なSQLでした。
クエリの中にはサブクエリのJOINも含まれており、調査の結果このサブクエリがボトルネックになっているようでした。
実行プランをみると、IndexScanの実際の行数が2億オーバーです。該当テーブルは本当は50万レコードくらいしかないので、最初はバグかな?と思いました。
統計情報は更新されているし、option(recompile)をつけて実行しても同じ実行プランで実行されてしまいます。
--問題のSQL概要（ざっくりしててすみません。。)
SELECT
いろんなカラム
FROM
いろんなテーブル
-- ↓ ここがボトルネック
JOIN (
サブクエリ
) A ON A.ColumnA = Column1
-- ↑ ここがボトルネック
WHERE
いろんな条件
実行プランの一部（テーブルサイズ50万レコードくらいなのに、Index Scanの行数がすごいことになってる）
調べていくうちに、「実際の行数」というのは、「GetNext()関数がコールされた回数」のことだということが分かりました。
参考： http://stackoverflow.com/questions/1006385/sql-execution-plan-shows-an-actual-number-of-rows-which-is-larger-than-the-tab?rq=1
ただ、なぜGetNext()関数が大量にコールされてしまったのか、そこまではつきとめることができませんでした。
（心当たりがある方は是非教えてください！）
サブクエリの中身はこんな感じでした
-- ボトルネックとなっていたサブクエリ
SELECT
ColumnA
FROM TableA
WHERE ColumnB IN (
SELECT MAX(ColumnB)
FROM TableB
JOIN TableC ON TableB.ColumnC = TableC.ColumnC
GROUP BY ColumnD
)
ためしに、このサブクエリの中身だけ単体で実行すると1秒もかからずに結果が返ってきます。
このことから、該当のSQLが遅い原因は「複雑なSQL文の解析時に、本当はもっと高速なプランがあるのに、効率の悪いプランが採用されてしまった」ということだと判断しました。
調査結果から、単体で実行する分には問題ないサブクエリを切り出すようにクエリを修正しました。
-- 問題のサブクエリだけ切り出して結果を一時テーブルに保存
SELECT
ColumnA
INTO #TmpTable
FROM TableA
WHERE ColumnB IN (
SELECT MAX(ColumnB)
FROM TableB
JOIN TableC ON TableB.ColumnC = TableC.ColumnC
GROUP BY ColumnD
)
-- 一時テーブル使用
SELECT
いろんなカラム
FROM
いろんなテーブル
-- ↓ 一時テーブルに書き換え
JOIN #TmpTable A ON A.ColumnA = Column1
-- ↑ 一時テーブルに書き換え
WHERE
いろんな条件
-- 後処理
DROP Table #TmpTable
修正後のクエリは1,2秒で実行結果が返ってきました。
プランでも、「実際の行数」が332行となっており、ボトルネックとなっていたSQLが先ほどとは違うプランで実行されていることが分かります。
今回のチューニングをまとめますと、
・SQL文が複雑になると、最適なプランが生成されない可能性が上がる
特にSQLServerでは経験上、GROUPBYを含むサブクエリだと今回の現象が発生する可能性が高いです
・複雑なSQLの中ではボトルネックなのに、単体で実行すると早いサブクエリがある場合は、前もって結果を一時テーブルに保存しておくと効果的な場合がある
以上、クエリの書き換えによるチューニングの例でした。