引き継いだソースが遅くてまともに動かなかった。
前任者はSQL単体で動いたからよしとしたようだが、
画面に実装して様々な条件で動かしたところタイムアウト。
以下のようなSQLだった。
select
中略
from
v_xxx v
where
1 = 1
and col1 = 'x'
and col2 = 'y'
and exists(
select
1
from
table1 t1
where
1 = 1
and col3 in('a', 'b', 'c')
and col4 in('d', 'e', 'f')
union
select
1
from
table2 t2
where
1 = 1
and col3 in('a', 'b', 'c')
and col4 in('d', 'e', 'f')
union
select
1
from
table3 t3
where
1 = 1
and col3 in('a', 'b', 'c')
and col4 in('d', 'e', 'f')
;
タイムアウトする場合はexists句内のサブクエリに該当するレコード数が合計20万件とかだった。
修正案
SELECT 中略
FROM v_xxx v
INNER JOIN table1 t1 ON t1.some_column = t.some_column
INNER JOIN table2 t2 ON t2.some_column = t.some_column
INNER JOIN table3 t3 ON t3.some_column = t.some_column
WHERE 1=1
AND t.col1 = 'x'
AND t.col2 = 'y'
AND (
t1.col3 IN ('a', 'b', 'c') AND t1.col4 IN ('d', 'e', 'f')
OR
t2.col3 IN ('a', 'b', 'c') AND t2.col4 IN ('d', 'e', 'f')
OR
t3.col3 IN ('a', 'b', 'c') AND t3.col4 IN ('d', 'e', 'f')
);
こっちは巨大な表になりそうだから、
inner join内はサブクエリにして必要なカラムを抽出したほうがよさそう。
exists句は自分ではまず使わなくて動きのイメージができなかったが、chatgptが教えてくれた。
`EXISTS`句内のサブクエリに該当するレコード数が合計20万件である場合、`v_xxx`テーブルを20万回走査するわけではありません。
`EXISTS`句は、メインクエリの各行に対してサブクエリが1回以上の結果を返すかどうかを確認します。そのため、サブクエリが返すレコード数に依存せず、メインクエリの各行に対してサブクエリが実行されるだけです。
つまり、`v_xxx`テーブルの各行に対して、サブクエリが1回以上の結果を返すかどうかを確認するために走査されます。サブクエリの結果が1つ以上ある場合、`EXISTS`句は真（TRUE）を返し、その行は結果に含まれます。一方、サブクエリの結果が空の場合、その行は結果に含まれません。そのため、走査される回数は`v_xxx`テーブルの行数に等しいか、それ以下です。
したがって、`v_xxx`テーブルを20万回走査するのではなく、各行ごとにサブクエリが実行され、その結果に基づいて行が結果に含まれるかどうかが決定されます。
メインテーブルの各行を、20万行あるデータセットに存在するか走査するのとどっちが早いか。
inner joinが遅いならビューでも作るか。
EXISTSとSQLの高速化について - 猫好きモバイルアプリケーション開発者記録
どうやらwith句だと評価順序がwhere句の前に来るらしい。
withかjoinかは可読性で判断。
既存のSQL組み立てがいまいちなので、思案。
話が変わるが、前任者はdistinctを知らないレベルの素人だった。
もしも自分が採用面接するなら、出題してアンチパターンをリファクタリングしてもらう方式をとると思う。
これは例題としたい。
