e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第60回 「バルク・バインドのスピードを計測してみる」

2013.06.03

こんにちは。インストラクターの蓑島です。

さて前回は、バルクバインドの概要と簡単な構文について解説しました。

PL/SQLは、PL/SQLエンジンで実行されますが、ブロック内のSQL文は違うエンジン(SQLエンジン)で実行されるために、ループ処理でSQLを繰り返し実行するとエンジンの切り替えが何度も発生します。
その回数が多いと大きなオーバーヘッドとなります。
そこで、値の配列ごと、SQL文をSQLエンジンに送信し、そこで一括実行することで、エンジンの切り替えが1回で済みますので、実行速度が大幅に向上できるというものでした。

それでは今回は実際にその性能を計測してみたいと思います。
かなりの効果がありますよ。

では、前回も使ったTEST01表を使いますが、一旦削除してもう一度最初から行います。

SQL> DROP TABLE TEST01;

表が削除されました。

SQL> CREATE TABLE TEST01 ( A  NUMBER, B  VARCHAR2(10));

表が作成されました。

TEST01表はご覧のようにA列、B列だけの簡単な表です。

TEST01表にINSERTするための配列を保持するために、パッケージを作りそのパッケージにA列用の配列、B列用の配列を格納します。
パッケージに変数や配列を格納するとそれはセッションの間、値を保持しますので今回の検証に便利です。
(バックナンバー 第30回 「パッケージでできること 変数の永続性」を参照)

SQL> CREATE OR REPLACE PACKAGE PAC1
  2     /*******************************/
  3     -- パッケージを作る
  4     /*******************************/
  5  IS
  6      /******************************/
  7     -- A列用の配列
  8     /******************************/
  9      TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
 10      INDEX BY BINARY_INTEGER;
 11      A_TAB   A_TAB_TYPE;
 12      /******************************/
 13     --B列用の配列
 14     /******************************/
 15      TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
 16      INDEX BY BINARY_INTEGER;
 17      B_TAB   B_TAB_TYPE;
 18  END;
 19  /

パッケージが作成されました。

早速このパッケージの配列に値を50万個、格納します。

SQL> BEGIN
  2      /********************************/
  3       -- A列、B列の配列に500000個の値をセット
  4      /********************************/
  5       FOR I IN  1..500000 LOOP
  6          PAC1.A_TAB(I) := I;           -- A列用の配列には 1~500000まで格納
  7          PAC1.B_TAB(I) := 'BBBBBB';    -- B列用の配列には固定文字をセット
  8       END LOOP;
  9  END;
 10  /

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

これでパッケージの配列に500000個の値があります。

この配列を使って最初は普通のループ処理で、500000回のINSERTをして、私の環境でどれくらい時間がかかるか調べてみます。

時間を計れるようにして

SQL> SET TIMING ON

実行します。

SQL> BEGIN
  2       /****************************************/
  3       -- 普通のループ文で 500000行をINSERT
  4       /****************************************/
  5       FOR  I IN  1..500000  LOOP
  6           INSERT INTO TEST01 VALUES (PAC1.A_TAB(I), PAC1.B_TAB(I));
  7       END LOOP;
  8  END;
  9  /

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

経過: 00:00:14.53

50万回のループ処理でINSERTすると、約15秒かかりました。

では次はバルク・バインドです。

一旦、表を空にします。

SQL> TRUNCATE TABLE TEST01;

表が切り捨てられました。

ではバルク・バインドで配列ごと、50万行を一括でINSERTします。

実行します。

SQL> BEGIN
  2       /****************************************************/
  3       -- バルク・バインドで、配列ごと 500000行を一括でINSERT
  4       /****************************************************/
  5       FORALL  I IN  1..500000
  6           INSERT INTO TEST01 VALUES (PAC1.A_TAB(I), PAC1.B_TAB(I));
  7  END;
  8  /

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

経過: 00:00:00.60

バルク・バインドで同じ行数を一括でINSERTすると、わずかに 0.6秒です。

ループ処理なら15秒かかる処理がバルク・バインドならわずかに、0.6秒です。非常に効果的ですね。

このように、SQL文をループで繰り返し実行する回数が多いほど、バルク・バインドを使うメリットが大きいといえます。

配列を使ったSQLのループ処理を行っているとき、ぜひバルク・バインドを検討してみてください。

次回もバルク・バインドについてバリエーションを解説します。ご期待ください。

先頭へ戻る