SQL 書き換えのバリエーションと効果

The Art of SQL」という本に、SQL の書き換え例として出ていたものをちょっと書き直して DB2 for IBM i で実行してみました。

こんなふうにいろいろ書き換えられる、という例としても、またその結果どんな影響があるか、という例としても役立つかもしれないので、一応載せておくことにしました。

↓がその書き直した SQL です。

/* 1 */ 
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e join empprojact epa on e.empno = epa.empno
   join project p on epa.projno = p.projno
   join act a on epa.actno = a.actno
WHERE
    epa.emstdate >= '1982-06-01'
  and e.workdept =  'D11'
  and   a.actkwd =  'LOGIC';

/* 1a */ 
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e join empprojact epa on e.empno = epa.empno and epa.emstdate >= '1982-06-01' and e.workdept = 'D11'
   join project p on epa.projno = p.projno
   join act a on epa.actno = a.actno and a.actkwd = 'LOGIC';

/* 2 */ 
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e
WHERE
  e.workdept = 'D11'
  and EXISTS (
        select * 
        from empprojact epa join project p on epa.projno = p.projno
                            join act a on epa.actno = a.actno
        WHERE
          epa.emstdate >= '1982-06-01'
           and a.actkwd = 'LOGIC'
            and e.empno = epa.empno);


/* 3 */
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e
WHERE
   e.workdept = 'D11'
  and e.empno IN (select epa.empno
                  from empprojact epa join project p on epa.projno = p.projno
                                      join act a on epa.actno = a.actno
                  WHERE
                    epa.emstdate >= '1982-06-01'
                     and a.actkwd = 'LOGIC');


/* 4 */
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e
WHERE
   e.workdept = 'D11'
  and e.empno IN (select epa.empno
                    from empprojact epa
                    where
                      epa.emstdate >= '1982-06-01'
                        and EXISTS (select * 
                                    from project p join act a on epa.actno = a.actno
                                    WHERE
                                      a.actkwd = 'LOGIC'
                                and epa.projno = p.projno));


/* 5 */
SELECT
  E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT
FROM
  employee e
WHERE
  e.workdept = 'D11'
 and e.empno IN (select epa.empno
                 from empprojact epa
                 where epa.emstdate >= '1982-06-01'
                     and epa.projno IN (select p.projno
                                        from project p join act a on epa.actno = a.actno
                                        WHERE
                                          a.actkwd = 'LOGIC'));

ちなみに、IN か EXISTS で検査されるテーブルについて選択される行数によって最適な SQL は異なる、という文脈で↑のような SQL が例として挙げられているわけです。

実際の実行結果

SAMPLE データベースを作成したすぐ後に実行した時の所要時間を記録してみたものが↓になります。

最適化 オープン 取り出し クローズ estimated
1 72 34552 256 176 0.058
1a 60 22136 n/a n/a 0.058
2 71 29688 n/a n/a 0.458
3 90 32456 n/a n/a 0.058
4 107 32168 n/a n/a 0.057
5 55 34984 n/a n/a 0.057

最適化時間が速いのは 5 と 1a、オープン時間が速いのは 1a になります。オープン時間が速いものに 2 を入れてもいいかもしれません。

それぞれアドバイスされたインデックスをすべて作成した後に実行してみた時の所要時間が↓になります。

最適化 オープン 取り出し クローズ estimated
1 69 25688 n/a n/a 0.014
1a 56 16712 n/a n/a 0.014
2 63 19232 n/a n/a 0.059
3 75 20136 n/a n/a 0.014
4 50 22272 n/a n/a 0.059
5 84 24544 n/a n/a 0.059

それぞれ、最適化時間もオープン時間もインデックス作成前にくらべて短くなっています。

インデックス作成後の時間だけで比較すると、最適化時間が速いのは 4 と 1a、オープン時間が速いのは 1a と 2 になっています。

4 はインデックス作成前ではいちばん最適化時間がかかっていたのに、インデックス作成後ではいちばん速くなっています。
このあたりは、単なるブレなのか、意味があるのかはいろんなケースでテストをしてみる必要がありますね。

それぞれの相対的な順位が変わらないので、インデックスの有無によって SQL に時間の差がでてくる、といったことは今回はなかったといってよさそうです。

やはり SAMPLE データベースのごく少ないデータ量ではあまり大きな差は見られませんでしたが、
1a の WHERE 句をまったくなくしてしまったものが最適化時間、オープン時間ともに速そうです。

とりあえずのまとめ

The Art of SQL」には、データの内容に応じて選択性を考えながら SQL を書き換えていく過程として載っているのですが、今回はその例の主眼である後半の SQL (特に 4, 5) にそれほどの差が見出せませんでした。
あまり差がないのであれば、結局のところ読みやすい 1 か 1a で書いておくのが無難でしょう。

また、すべてのケースで、アドバイスされたインデックスを作成した方が最適化時間もオープン時間も速くなっている、というのも見逃せない点です。
インデックスのアドバイスはとりあえず受け入れてテストしてみる価値はありそうです。

[Top Pageに戻る]

Ads by TOK2