Tips9.SQLの発行回数を減らす

2021-10-24

SQLチューニングの方法として良くある方法に「SQLの発行回数を減らす」というのがあります。
簡単に言われますが、では具体的にどのようにしてSQLは纏められていくのでしょうか。

簡単なケースとしては、下記のような例が考えられます。
部門別に従業員の一覧を取得するSQLですが、深く考えなければ次のようなコーディングになるでしょう。

部門別のSQLと従業員のSQLを二つのカーソルとして定義し、部門別のSQLのループの中で従業員のSQLを呼び出します。

DECLARE
  CURSOR cur_dept
  IS    -- 部門別のSQL
  SELECT deptno, dname FROM dept ORDER BY deptno;
  --
  CURSOR cur_emp(p_deptno IN NUMBER)
  IS    -- 従業員別SQL
  SELECT empno, ename FROM emp
  WHERE  deptno = p_deptno
  ORDER BY empno;
  --
  rec_dept cur_dept%ROWTYPE;
  rec_emp cur_emp%ROWTYPE;
BEGIN
  -- 部門別のカーソル
  FOR rec_dept IN cur_dept LOOP
    -- 従業員別のカーソル
    FOR rec_emp IN cur_emp(rec_dept.deptno) LOOP
      dbms_output.put_line(rec_dept.dname || ' --  ' || rec_emp.empno || ':' ||rec_emp.ename);
    END LOOP;
  END LOOP;
END;

この場合、部門の種類数分だけ従業員別SQLを発行することになります。
しかし、次のように記述することで、SQLの発行回数を1回で済ますことが可能です。

DECLARE
  CURSOR cur_deptemp
  IS    -- 部門と従業員の表をJOINして一つのSQLにまとめた
  SELECT d.dname, e.empno, e.ename
  FROM   dept d INNER JOIN emp e
         ON d.deptno = e.deptno
  ORDER BY d.dname, e.empno;
  --
  rec_deptemp cur_deptemp%ROWTYPE;
BEGIN
  -- 一度のカーソル呼出で済む
  FOR rec_deptemp IN cur_deptemp LOOP
    dbms_output.put_line(rec_deptemp.dname || ' --  ' || rec_deptemp.empno || ':' ||rec_deptemp.ename);
  END LOOP;
END;

実際に、apex.oracle.comでそれぞれを実行してみると。。。
前者のカーソル2回ループするやり方では、

カーソルを１つにまとめたやり方では

と、処理時間は1.3秒から0.9秒弱に短縮されました。

これくらいなら、まだ当たり前に思いつく範囲かもしれません。
しかし、次のように条件が少し複雑になると、どうでしょう。

emp表のsal列を以下のように更新します。
・JOBがMANAGERならsalの1.1倍（小数点以下切捨て）、
・JOBがPRESIDENTは更新対象外。
・それ以外で、DEPTがACCOUNTING(deptno=10)ならsalの1.2倍（小数点以下切捨て）、
・DEPTがRESEARCH(deptno=20)ならsalの1.25倍（小数点以下切捨て）、SALES(deptno=30)ならsal+(comm/2)

これを一つのSQLで実装してみましょう。

UPDATE emp
SET sal = CASE WHEN job = 'MANAGER' THEN TRUNC(sal*1.1)
               ELSE CASE deptno
                    WHEN 10 THEN TRUNC(sal*1.2)
                    WHEN 20 THEN TRUNC(sal*1.25)
                    WHEN 30 THEN sal+(NVL(comm,0)/2)+100
                    END
          END
WHERE job <> 'PRESIDENT';

と記述することができます。

普通に考えると下記のようになるのかもしれませんが、性能面でも、コードの長さという意味でも、全くメリットはありません。

DECLARE
  CURSOR c1 IS
  SELECT e.empno, e.deptno, e.sal, e.comm, e.job
  FROM emp e
  WHERE  e.job <> 'PRESIDENT';
  r1 c1%ROWTYPE;
  ln_new_sal NUMBER; -- 算出したsalの値を一時的に保持する変数
BEGIN
  -- 全従業員を1件ずつループ
  FOR r1 IN c1 LOOP
    IF r1.job = 'MANAGER' THEN
      -- JOBがMANAGERならsalの1.1倍（小数点以下切捨て）
      ln_new_sal := TRUNC(r1.sal*1.1);
    ELSIF r1.deptno = 10 THEN
      :
      以下、下記の内容をそれぞれ実装
      -- JOBがPRESIDENTは更新対象外。
      -- それ以外で、DEPTがACCOUNTING(deptno=10)ならsalの1.2倍（小数点以下切捨て）、
      -- DEPTがRESEARCH(deptno=20)ならsalの1.25倍（小数点以下切捨て）
      -- SALES(deptno=30)ならsal+(comm/2)
      :
    END IF;
--
    -- 1件ずつUPDATE
    UPDATE emp SET sal = ln_new_sal
    WHERE  empno = r1.emp;
--
  END LOOP;
END;

 

・・・途中で断念してしまいましたが、どちらが良いかは言うまでも無いでしょう。