なぜ構文のチェックを行うのか?
「別に動けばいいのでは?」「速度は遅くなってないので今のままでもいいのでは?」そんな声があるかと思います。
確かに今はいいのですが、元のデータが多くなった場合や実施回数が多くなった場合など
に少しずつ遅くなる可能性があります。
私が経験したのは、プロジェクトで1つのシステムで機能が増えて大きくなった場合や複数の会社が開発に携わった場合、処理速度差が発生し、全部のSQLを見直すようなことが時々ありました。
ハード増強による解決もいいのですが、まずは実行計画の見直しなどを行うかと思います。
それ以外にもSQLの記載の積み重ねによって速度が変わってきたりもします。
私も過去にプロジェクト全体で以下のような項目をチェックした結果速度を改善したことがあります。
備忘録的にも私がチェックした内容を記載致します。
このような部分になっている箇所がないか、見てみるのもいいかもしれません。
(保守しやすい書き方も含めてチェックシートを作成しておくのいいかもしれません。)
チェック項目
■共通
- SQLのキーワードは大文字で書かれているか。
- 極値関数(MAX / MIN)でインデックスを活用しているか。
(データの重複を気にする必要がない・発生しない場合の)UNION、INTERSECT、EXCEPT には「ALL」を付けているか。
◇SELECT句
- 不要な項目を指定していないか。
(*:ワイルドカードを使用せず、明示的に項目名を記述しているか) - DISTINCTを使用していないか。
- 行数を数えるときは「COUNT(*)」ではなく「COUNT(列名)」を使っているか。
- 複数のテーブルをJOINしている場合、CASE式の検討をしているか。
(CASE式を使用し、最大、最小を取得したテーブル作成して、JOINする。) - カラム番号で取得項目の指定をしてしていにか。
◇FROM句
- SQL中で使用されないテーブルを記述していないか。
- JOINの子表を結合する条件はPKもしくはインデックスを付与した項目を使用しているか。
- JOINするテーブルが6個以上になる場合、回避方法の検討しているか。
- LEFT OUTER JOINとINNER JOINで同じ結果が得られる場合、INNER JOINを使用しているか。
- 直積(結合条件のないテーブルを複数指定)を使用していないか。
- テーブルに別名をつけるいるか。
◇GROUP BY句
- GROUP BY句で指定されている項目にインデックスが付与されているか。
- GROUP BYの利用で、可能な限り、HAVINGではなくWHEREで絞り込んでいるか。
- GROUP BY句を用いた検索結果をソートする場合に、ORDER BYを指定しているか。
◇ORDER BY句
- ORDER BY句で指定されている項目にインデックスが付与されているか。
- レコードの返却順を保証する必要がない場合に、ORDER BYを利用していないか。
- 不要な項目がORDER BY句に指定されていないか。
◇INSERT句
- 連番を使用する場合、採番テーブルなどを使用していないか。
(連番であればシーケンスを使用する。採番テーブルを利用した場合、使用されるSQLはきわめて単純な構文になる為、改善する方法がない。) - 大量データのINSERTを1件ずつ行っていないか。
◇UPDATE句
- 大量データのUPDATEを1件ずつ行っていないか。
- MERGE文の検討をしているか。
(OracleやDB2などMERGE文が利用できる場合、条件をON句にまとめることができるMERGE文を利用したほうがメンテナンス性、データ増加によるパフォーマンスの低下が少ない。) - 主キー項目に対する更新がされていないか。
- 少ない回数で更新できる検討されているか。
(関数サブクエリなどを使用することでSQL実行回数を減らすことでパフォーマンスは上がる。)
◇DELETE句
- TRUNCATEの使用を検討したか。
(全てのデータを削除する場合は、利用を検討)
◇WHERE句
- 検索条件には、必ず主キーまたはインデックスが定義されている列(第1索引列は必須)を指定しているか。
- 副問合せ内の抽出条件はPKもしくはインデックスを付与した項目を使用しているか。
- 条件式の左辺には関数を指定していないか。(項目名を左辺に記述することが前提)
- 条件式の左辺には算術演算等の式を含めていないか。(項目名を左辺に記述することが前提)
- 条件式の左辺には文字列操作を行っていないか。(項目名を左辺に記述することが前提)
- 条件式の項目は、同一のデータ型を使用して比較しているか。
- 検索条件が複数ある場合、絞り込み結果がより少なくなる(データ分布が粗い)条件から先に記述しているか。
- 検索条件でIN句を使用する場合、より見つかりやすい(データ分布が粗い)条件から先に記述しているか。
- サブクエリの引数は「EXISTS述語」を使用することを検討しているか。(IN述語を使用する場合は、EXISTS述語で同じ結果を取得することができる)
- 副問い合わせを行う場合、EXISTSの中に副問合せを書くのではなく、JOINの利用を検討しているか。
(ただし、JOINの利用は、EXISTS利用時と問合せ結果が異ならない場合に限る。) - NOT INの代替として、NOT EXISTSの利用を検討しているか。
- 異なる列に対する条件の場合、可能な限りORではなくUNIONを利用しているか。
- Like文では、前方一致検索(LIKE 'XXX%')のみを使用しているか。
- WHERE句の範囲指定は、不等号ではなくBETWEENを使用しているか。
- 複数の固定値を条件とする場合、可能な限りLIKE述語ではなく、IN述語またはBETWEEN述語を使用しているか。
- "パーティション分割するテーブルを検索する場合、必要なパーティションのみにアクセスさせるために、
検索条件にパーティション分割キーを指定しているか。" - テーブル設計でNULL状態の項目を持たせない、列値と条件比較する等の対策を実施し、IS NULLは利用されていないか。
- 「<>」「!=」「NOT ~」などの否定演算子を利用せず、「=」「IN」「BETWEEN」などの肯定形で比較しているか。
- 複数のSQL文がDECODE関数を使用して一つにまとめることができるか検討しているか。
- 副問合せは原則2つまでとしているか。
- サブクエリにてネストは原則使用していないか。
- サブクエリにて相関サブクエリーは原則使用していないか。
- 暗黙の型変換は回避しているか。
- IN述語の引数は「最もありそうなキー」を左に記述しているか。
◇その他
- 選択する行に重複行を含んでも影響のない場合には、UNION ALLを指定しているか。
- 暗黙的なソート処理が発生するSQL文
(DISTINCT、UNION、INTERSECT、MINUS、EXCEPT)を使用していないか。