ORACLE PL/SQL 変数の使い方の違いによるパフォーマンス確認

こんにちは、石川さんです。

最近ORACLEのPL/SQLでストアドプログラムを作っています。どっちのパフォーマンスが良いのか、ちょっと気になったので調べてみました。

作っていたのは、ファイルを１行ずつ読み込んで、諸々チェックして、問題なければテーブルにINSERTする、というよくある単純な処理です。このとき、呼び出し側からOUTパラメータを指定して、登録件数とチェックしたエラー件数をもどす、という要件があるときの変数の使い方として、以下の二通りを考えました。どっちが早いのでしょうね？

1. OUTパラメータを直接インクリメントする
2. 別途定義したローカル変数をインクリメントして、最後にOUTパラメータへ結果をセットする

実験

わからないことは、すぐに実験しましょう。と、いうことで以下のスクリプトを作ってみました。

set serveroutput on

DECLARE
  counter NUMBER := 0;
  s1 TIMESTAMP;
  s2 TIMESTAMP;
  e1 TIMESTAMP;
  e2 TIMESTAMP;
  PROCEDURE inner_procedure1(an_counter OUT NUMBER) IS
  BEGIN
    an_counter := 0;
    LOOP
      an_counter := an_counter + 1;
      EXIT WHEN an_counter > 10000000;
    END LOOP;
  END;
  PROCEDURE inner_procedure2(an_counter OUT NUMBER) IS
    counter NUMBER := 0;
  BEGIN
    an_counter := 0;
    LOOP
      counter := counter + 1;
      EXIT WHEN counter > 10000000;
    END LOOP;
    an_counter := counter;
  END;
BEGIN
  s1 := SYSTIMESTAMP;
  inner_procedure1(counter);
  e1 := SYSTIMESTAMP;
  s2 := SYSTIMESTAMP;
  inner_procedure2(counter);
  e2 := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('START:'||TO_CHAR(s1,'YYYY-MM-DD HH24:MI:SS.FF'));
  DBMS_OUTPUT.PUT_LINE('END  :'||TO_CHAR(e1,'YYYY-MM-DD HH24:MI:SS.FF'));
  DBMS_OUTPUT.PUT_LINE(e1 - s1);
  DBMS_OUTPUT.PUT_LINE('START:'||TO_CHAR(s2,'YYYY-MM-DD HH24:MI:SS.FF'));
  DBMS_OUTPUT.PUT_LINE('END  :'||TO_CHAR(e2,'YYYY-MM-DD HH24:MI:SS.FF'));
  DBMS_OUTPUT.PUT_LINE(e2 - s2);
END;
/

inner_procedure1が、OUTパラメータをインクリメントする「１．」のケースで、inner_procedure2が、ローカル変数をインクリメントする「２．」のケースになります。

では何回か実行します。そして、結果は、、、

（・・・略・・・）
12:41:11  39  /
START:2022-11-30 12:41:11.901000000
END  :2022-11-30 12:41:12.665000000
+000000000 00:00:00.764000000
START:2022-11-30 12:41:12.665000000
END  :2022-11-30 12:41:13.149000000
+000000000 00:00:00.484000000

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.26
12:41:13 SQL> /
START:2022-11-30 12:41:18.841000000
END  :2022-11-30 12:41:19.492000000
+000000000 00:00:00.651000000
START:2022-11-30 12:41:19.492000000
END  :2022-11-30 12:41:19.872000000
+000000000 00:00:00.380000000

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.03
12:41:19 SQL> /
START:2022-11-30 12:41:23.800000000
END  :2022-11-30 12:41:24.439000000
+000000000 00:00:00.639000000
START:2022-11-30 12:41:24.439000000
END  :2022-11-30 12:41:24.818000000
+000000000 00:00:00.379000000

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:01.02
12:41:24 SQL>

「２．ローカル変数をインクリメントして、最後にOUTパラメータへ結果をセットする」方がおよそ1.5倍ほど、はやい！ということがわかりました。

と、いうことで今後はOUTパラメータへのアクセスは最後の一回のみ、ということにしたいと思います。

結論

ローカル変数をインクリメントして、最後にOUTパラメータへ結果をセットした方がはやい、というのは結果からみるとそのとおりなのですが、実は、10000000回（1千万回）ループしているのですよね。その上で、早くなったのは、0.3秒未満なので、大量データを何度も扱わない限りは気にしなくても良さそうです。億超えのデータをしょっちゅう扱うような人は、ちょっとずつ効いてきますので、気にしましょう！
ぼくはとっても気になります！！！