SQL プロシージャーのパフォーマンスの改善
SQL PL とインライン SQL PL のコンパイルの概要
SQL プロシージャーのパフォーマンスを向上させる方法について説明する前に、CREATE PROCEDURE ステートメントの実行時に SQL プロシージャーがどのようにコンパイルされるかについて説明する必要があります。
SQL プロシージャーを作成するときに、プロシージャー本文に含まれている SQL 照会がプロシージャーのロジックから切り離されます。 SQL 照会については、パフォーマンスの最大化のために、 パッケージ内のセクションに静的にコンパイルします。 静的にコンパイルした照会のセクションの主な中身は、 オプティマイザーがその照会のために選択したアクセス・プランです。パッケージとは、そのようなセクションの集合です。 パッケージとセクションについて詳しくは、 「 SQL 解説書」を参照してください。一方、プロシージャー・ロジックは、 ダイナミック・リンク・ライブラリーにコンパイルします。
プロシージャーの実行中は、プロシージャーのロジックから SQL ステートメントに制御が移るたびに、DLL とデータベース・エンジンとの間でコンテキストの切り替え が発生します。 SQL プロシージャーは、unfenced モード で実行されます。つまり、データベース・エンジンと同じアドレッシング・スペースで実行されます。 したがって、ここで言う「コンテキストの切り替え」とは、オペレーティング・システム・レベルで発生する完全な「コンテキストの切り替え」ではなく、データベース・エンジン内の層の変更を指しています。 頻繁に呼び出されるプロシージャー (OLTP アプリケーション内のプロシージャーなど) や、 多数の行を処理するプロシージャー (データ・クレンジングを実行するプロシージャーなど) でコンテキストの切り替えの数を減らせば、 パフォーマンスにかなりの影響を与えることができます。
SQL PL を含んだ SQL プロシージャーは、 個々の SQL 照会をパッケージ内の各セクションに静的にコンパイルすることによってインプリメントするのに対し、 インライン SQL PL 関数は、 その名が示すとおり、 関数の本体を、関数を使用する照会の中にインライン化することによってインプリメントします。 SQL 関数内の各照会は、 あたかも関数本体が 1 つの照会であるかのように一緒にコンパイルされます。 このコンパイルは、 その関数を使用するステートメントのコンパイルが行われるたびに発生します。 ただし、SQL プロシージャーの場合とは異なり、 SQL 関数内のプロシージャー・ステートメントは、 データ・フロー・ステートメントとは別の層で実行されるわけではありません。 したがって、 プロシージャー・ステートメントとデータ・フロー・ステートメントの間で制御が移るたびに、 コンテキストの切り替えが発生するわけではないということです。
ロジック内に副作用がなければ SQL 関数を使用する
このように、 プロシージャー内の SQL PL と関数内のインライン SQL PL とではコンパイルの方法が違うので、 プロシージャー・コードが SQL データを照会するだけでデータを変更しない限り、 つまり、データベース内外のデータに関する副作用がない限り、 プロシージャー・コードは、 プロシージャー内よりも関数内にあったほうが実行速度が上がると考えられます。
ただし、このようなメリットを生かせるのは、 実行する必要のあるすべてのステートメントが SQL 関数内でサポートされている場合に限られます。 SQL 関数には、 データベースを変更する SQL ステートメントを組み込めません。 また、 関数のインライン SQL PL として使用できるのは、 SQL PL のサブセットにすぎません。 例えば、 CALL ステートメントの実行、カーソルの宣言、 SQL 関数による結果セットの生成などは実行できません。
以下に示すのは、 パフォーマンスを最大化する目的で SQL 関数に変換するのに適している SQL PL を含んだ SQL プロシージャーの一例です。
CREATE PROCEDURE GetPrice (IN Vendor CHAR&(20&),
IN Pid INT, OUT price DECIMAL(10,3))
LANGUAGE SQL
BEGIN
IF Vendor eq; ssq;Vendor 1ssq;
THEN SET price eq; (SELECT ProdPrice
FROM V1Table
WHERE Id = Pid);
ELSE IF Vendor eq; ssq;Vendor 2ssq;
THEN SET price eq; (SELECT Price FROM V2Table
WHERE Pid eq; GetPrice.Pid);
END IF;
ENDこれを SQL 関数として記述すると、以下のようになります。
CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)
RETURNS DECIMAL(10,3)
LANGUAGE SQL
BEGIN
DECLARE price DECIMAL(10,3);
IF Vendor = 'Vendor 1'
THEN SET price = (SELECT ProdPrice
FROM V1Table
WHERE Id = Pid);
ELSE IF Vendor = 'Vendor 2'
THEN SET price = (SELECT Price FROM V2Table
WHERE Pid = GetPrice.Pid);
END IF;
RETURN price;
END関数の呼び出しは、プロシージャーの呼び出しとは異なることも覚えておく必要があります。 関数を呼び出すには、 VALUES ステートメントを使用するか、 SELECT ステートメントや SET ステートメントなどの中で式が有効な場所に関数を記述して呼び出します。 以下はいずれも、 この新しい関数を呼び出す方法として有効です。
VALUES (GetPrice('IBM', 324))
SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 10
SET price = GetPrice(Vname, Pid) SQL PL プロシージャー内で 1 つのステートメントを使用すれば十分な場合に複数のステートメントを使用しない
基本的に SQL は簡潔に記述するほうが良いのですが、 実際には簡潔でない SQL を記述してしまうこともよくあります。 例えば、次のような SQL ステートメントがあるとしましょう。
INSERT INTO tab_comp VALUES (item1, price1, qty1);
INSERT INTO tab_comp VALUES (item2, price2, qty2);
INSERT INTO tab_comp VALUES (item3, price3, qty3);
これは、以下の 1 つのステートメントとして記述できます。
INSERT INTO tab_comp VALUES (item1, price1, qty1),
(item2, price2, qty2),
(item3, price3, qty3);この複数行の挿入ステートメントの実行にかかる時間は、 元の 3 つのステートメントの実行にかかる時間のほぼ 3 分の 1 です。 これだけを取り出したコードであれば、 パフォーマンスの改善はごくわずかでしょうが、 ループやトリガー本体などの中でこのコード断片を繰り返し実行する場合は、 かなりの改善が期待できます。
同じように、以下のような一連の SET ステートメントがあるとしましょう。
SET A = expr1;
SET B = expr2;
SET C = expr3;これは、以下の 1 つの VALUES ステートメントとして記述できます。
VALUES expr1, expr2, expr3 INTO A, B, C;この書き換えでは、 元の一連のステートメントのセマンティクスをそのまま保持しています。 ただし、元のいずれか 2 つのステートメントの間に依存関係が存在する場合は別です。 この点を示す以下の例について考えてみましょう。
SET A = monthly_avg * 12;
SET B = (A / 2) * correction_factor;この 2 つのステートメントを以下のように書き換えるとしましょう。
VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;この場合は、元のセマンティクスがそのまま保持されていません。INTO キーワードの前の両方の式は並列的に評価されるからです。つまり、B に代入される値は A に代入される値に基づくというのが、 元のステートメントで意図されているセマンティクスですが、 書き換え後のコードにはそれが反映されていないということです。
複数の SQL ステートメントを 1 つの SQL 式にまとめる
SQL 言語には、 他のプログラム言語と同じように、 2 種類の条件構造体が用意されています。 つまり、プロシージャー型の構造体 (IF ステートメント、CASE ステートメント) と関数型の構造体 (CASE 式) です。 1 つの計算処理を表すためにどちらのタイプの構造体でも使用できる状況では、 ほとんどの場合、どちらを使用するかは好みの問題です。 ただし、 CASE 式によって記述したロジックは、 CASE ステートメントや IF ステートメントによって記述したロジックよりもコンパクトであり、 効率的でもあります。
以下の SQL PL コード断片について考えてみましょう。
IF (Price <= MaxPrice) THEN
INSERT INTO tab_comp(Id, Val) VALUES(Oid, Price);
ELSE
INSERT INTO tab_comp(Id, Val) VALUES(Oid, MaxPrice);
END IF;この IF 節の条件は、 tab_comp.Val 列に挿入する値を決定するという目的のためだけに使用しています。 プロシージャー層とデータ・フロー層の間のコンテキストの切り替えを避けるために、 この同じロジックを CASE 式付きの 1 つの INSERT で記述すれば、以下のようになります。
INSERT INTO tab_comp(Id, Val)
VALUES(Oid,
CASE
WHEN (Price <= MaxPrice) THEN Price
ELSE MaxPrice
END);CASE 式は、 スカラー値が有効な場所であればどんなコンテキストでも使用できるというのは注目に値します。 特に便利なのは、代入の右辺で使用できるということです。 以下に例を示します。
IF (Name IS NOT NULL) THEN
SET ProdName = Name;
ELSEIF (NameStr IS NOT NULL) THEN
SET ProdName = NameStr;
ELSE
SET ProdName = DefaultName;
END IF;これは、以下のように記述できます。
SET ProdName = (CASE
WHEN (Name IS NOT NULL) THEN Name
WHEN (NameStr IS NOT NULL) THEN NameStr
ELSE DefaultName
END);実際に、この例の場合はさらに優れた解決策があります。
SET ProdName = COALESCE(Name, NameStr, DefaultName);SQL を分析し、その書き換えを検討するために時間を取ることのメリットを過小評価しないでください。 パフォーマンス上のメリットは、 プロシージャーの分析と書き換えにかけた時間の何倍もの価値があるはずです。
SQL の一括設定のセマンティクスを活用する
ループ、代入、カーソルなどのプロシージャー型の構造体を使用すれば、 SQL DML ステートメントだけでは記述できない計算処理を記述できます。 その一方で、プロシージャー・ステートメントが手元にあると、 実際には SQL DML ステートメントだけで計算処理を記述できる場合でも、 プロシージャー・ステートメントに頼ってしまう危険があります。 すでに見たとおり、 プロシージャーによる計算処理は、 DML ステートメントによって記述した等価の計算処理よりもパフォーマンスが桁違いに落ちることがあります。 以下のコード断片について考えてみましょう。
DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
IF (v1 > 20) THEN
INSERT INTO tab_sel VALUES (20, v2);
ELSE
INSERT INTO tab_sel VALUES (v1, v2);
END IF;
FETCH cur1 INTO v1, v2;
END WHILE;まずループ本体は、『複数の SQL ステートメントを 1 つの SQL 式にまとめる』の項で取り上げた書き換えを適用することによって改善できます。
DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
INSERT INTO tab_sel VALUES (CASE
WHEN v1 > 20 THEN 20
ELSE v1
END, v2);
FETCH cur1 INTO v1, v2;
END WHILE;しかし、よく見ると、 このコード・ブロック全体は、サブ SELECT 付きの 1 つの INSERT として記述できます。
INSERT INTO tab_sel (SELECT (CASE
WHEN col1 > 20 THEN 20
ELSE col1
END),
col2
FROM tab_comp);元のコードでは、 SELECT ステートメントの各行で、 プロシージャー層とデータ・フロー層の間のコンテキストの切り替えが発生します。 一方、書き換えた後のコードでは、 コンテキストの切り替えがまったく発生しないので、 オプティマイザーは計算処理全体をグローバルに最適化できます。
ただし、 次の例に示すように各 INSERT ステートメントの対象になっている表がそれぞれ異なる場合、 これほど劇的な単純化は不可能です。
DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
IF (v1 > 20) THEN
INSERT INTO tab_default VALUES (20, v2);
ELSE
INSERT INTO tab_sel VALUES (v1, v2);
END IF;
FETCH cur1 INTO v1, v2;
END WHILE;それでも、以下のようにすれば、SQL の一括設定の機能を活用できます。
INSERT INTO tab_sel (SELECT col1, col2
FROM tab_comp
WHERE col1 <= 20);
INSERT INTO tab_default (SELECT col1, col2
FROM tab_comp
WHERE col1 > 20);このようにカーソル・ループを除去するには時間がかかりますが、 既存のプロシージャー・ロジックのパフォーマンスを改善できることを考えれば、 そのための価値は十分にあると言えます。
オプティマイザーに常に最新の情報を提供する
プロシージャーの作成時に、 個々の SQL 照会は、 パッケージ内の各セクションにコンパイルされます。 オプティマイザーが照会の実行プランを選択するための基礎になるのは、特に表の統計 (表のサイズや、列内のデータ値の相対度数など) と、照会のコンパイルの時点で使用できる索引です。表にかなりの変更があった場合は、その表に関する統計を収集するべきです。また、統計を更新した場合や、新しい索引を作成した場合も、それらの表を使用する SQL プロシージャーに関連するパッケージを再バインドして、最新の統計と索引を活用したプランを作成するようにしてください。
表の統計を更新するには、RUNSTATS コマンドを使用します。 SQL プロシージャーに関連するパッケージを再バインドするには、REBIND_ROUTINE_PACKAGE 組み込みプロシージャーを使用します。 例えば、 プロシージャー MYSCHEMA.MYPROC のパッケージを再バインドするには、 以下のコマンドを使用できます。
CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'MYSCHEMA.MYPROC', 'ANY')P は、このパッケージがプロシージャーに対応していることを示し、 ANY は、関数とタイプの解決時に SQL パス内のすべての関数とタイプを対象にすることを示します。詳細については、 「コマンド解説書」の『REBIND コマンド』の項目を参照してください。
配列の使用
配列を使用して、アプリケーションとストアード・プロシージャーの間でデータの集合を効果的に受け渡しし、リレーショナル表を使用せずに SQL プロシージャー内でデータの一時的な集合を格納し取り扱うことができます。 SQL プロシージャー内で使用可能な配列の演算子を使って、データの保管と取り出しを効率的に行うことができます。 アプリケーションが適度なサイズの配列を作成するなら、巨大な配列 (数メガバイト規模) を作成するよりも、はるかに良いパフォーマンスを得ることができます。これは配列全体がメイン・メモリーに格納されるためです。