【SQL】WITHの使いまわしに 気を付けよう

WITH句はとても便利ですが、使いまわす際には注意が必要です。
そこで、WITHを使い回す際の注意点についてまとめました。

Transcript

1. 【SQL】 WITHの使い方に気を付けよう
2. 目次 1. WITHって何？ 2. 問題点（WITHの使いまわし） 3. 問題点の改善策 4. 改善前と改善後のコスト比較 5. WITHを使用する際のベストプラクティス 6. まとめ
3. WITHって何？ • 一時的な名前付きのサブクエリ。 • 複数のサブクエリをシンプルにまとめることができる。 • 見やすくなる。 • 同じ結果を繰り返し参照できる。
4. 記載方法の比較（サブクエリ） SELECT p.first_name, p.last_name, a.street, a.city FROM People p INNER JOIN ( SELECT person_id, street, city FROM Addresses WHERE city = 'New York' ) a ON p.person_id = a.person_id;
5. 記載方法の比較（WITH） WITH Address_CTE AS ( SELECT person_id, street, city FROM Addresses WHERE city = 'New York' ) SELECT p.first_name, p.last_name, a.street, a.city FROM People p INNER JOIN Address_CTE a ON p.person_id = a.person_id;
6. 問題点（WITHの使いまわし） • WITHの結果が一時的にディスクに保存される（マテリアライズ） →　ディスクアクセスを行うと処理が遅くなる場合がある。 • 再利用が非効率 　→　WITHの結果は一時的に固定化される。 　　　WITHで使用されたINDEXは、メインクエリで異なる条件で 　　　フィルタリングした際に適用されない場合があり遅延する。
7. 例（異なる条件でWITHを使いまわす） WITH ADR AS (SELECT person_id, birth_date FROM Addresses ) SELECT pep.first_name, adr1.birth_date, adr2.birth_date FROM People pep INNER JOIN ADR adr1 ON pep.person_id = adr1.person_id INNER JOIN ADR adr2 ON pep.birth_date = adr2.birth_date WHERE pep.person_id = 135233;
8. 改善策 1. WITHを使用せずサブクエリとして記載。 →　最適な手順書（実行計画）を選択し、効率よくSQLが実行される。 　　結果が固定されるWITHと比較し、サブクエリごとに実行計画 　　が選択されるため遅延が発生しにくい。 2. 一時テーブルを作成し、そこにWITHのデータをINSERT →　1　と同じ理由。 　　同じデータを複数回使用することによる 　　ディスクアクセスを減らすことができる。
9. 改善策 1 SELECT　 pep.first_name, adr1.address_id, adr2.birth_date FROM People pep INNER JOIN ( SELECT person_id, address_id FROM Addresses WHERE person_id = 135233) adr1 ON pep.person_id = adr1.person_id INNER JOIN ( SELECT person_id, birth_date FROM Addresses) adr2 ON pep.birth_date = adr2.birth_date WHERE pep.person_id = 135233;
10. 改善策 2 CREATE TEMP TABLE temp_addresses AS SELECT person_id, address_id, birth_date FROM Addresses WHERE person_id = 135233; SELECT pep.first_name, FROM People pep INNER JOIN temp_addresses adr1 ON pep.person_id = adr1.person_id INNER JOIN temp_addresses adr2 ON pep.birth_date = adr2.birth_date ORDER BY adr1_address_id, adr2_address_id;
11. 改善前と改善後のコスト比較 改善前：110,521 改善後：改善策１の場合　469 　　　：改善策２の場合　16,534 まだまだコストは高いが改善されている。
12. WITHを使用する際のベストプラクティス • 異なる条件で使いまわす際はサブクエリも検討。 • 適切なインデックスを適用する。 • マテリアライズの制御。 　→製品によってはマテリアライズを制御できるものもある。
13. まとめ • WITHはとても便利だが、適切な使い方が重要。 • データが増えることによってパフォーマンスが低下する。