パフォーマンスの改善 一時ファイルをSQL文で保持（SQL_SCRIPT編）

2024年3月23日テクニカルメモ

SQL_SCRIPTとは

WebFOCUSでは出力形式「SQL_SCRIPT」を使用することで、データを取得するためのSQL文として一時ファイルをを生成することが可能です。
一時ファイルを用いた再検索や、一時ファイルを用いた結合を行う場合にもWebFOCUSサーバ側に物理的なデータを保持するのではなくSQL文として保持し、WebFOCUSサーバ側にHOLDファイルでデータを作成せずにデータベース側で処理をすることで、レスポンスの改善を見込める場合があります。

物理ファイル（.ftm）と出力形式「SQL_SCRIPT」との処理の違い

一般的に活用する物理ファイル（.ftm）と出力形式「SQL_SCRIPT」との処理を説明し、それぞれの処理の違いを説明します。

物理ファイル（.ftm）で一時ファイル作成の場合

①WebFOCUSからDBのデータを検索します。

②検索結果のデータを[.ftm]形式の物理ファイルとして各リクエストごとに、WebFOCUSサーバ上に生成します。

③WebFOCUSサーバー上にて結合処置を行います

④レポートを生成・出力します

プロシジャ記述例（物理ファイル）

Oracle の EMP 表を利用した場合のサンプルです

TABLE FILE EMP
SUM
    SAL
BY  DEPTNO
WHERE ENAME EQ 'SMITH';
ON TABLE HOLD FORMAT BINARY
END

SQL_SCRIPTで一時ファイル作成の場合

①プロシジャ実行時に、データ取得のSQL文が記載された[.sql]形式の物理ファイルを
　WebFOCUSサーバ上に生成します

②[.sql]ファイルを使用して、WebFOCUSからDBのデータを検索します
※結合処理はWebFOCUSから発行されるクエリ内に記載されます

③DB上で結合処理を行い、結果をWebFOCUSへ返します④WebFOCUSにてレポートを生成・出力します

④WebFOCUSにてレポートを生成・出力します

プロシジャ記述例（SQL_SCRIPT）

Oracle の EMP 表を利用した場合のサンプルです

TABLE FILE EMP
PRINT JOB
WHERE ENAME EQ 'SMITH';
ON TABLE HOLD FORMAT SQL_SCRIPT
END

上記のサンプルを実行することでHOLDという名称の一時ファイル（.sql）とシノニム（.acx、.mas）を作成します。
上記サンプルプロシジャの場合、hold.sqlには以下のSQL文が格納されます。

SELECT T1."JOB" AS "VB001_PRN_JOB"
FROM SCOTT.EMP T1
WHERE (T1."ENAME" = 'SMITH');

物理ファイルのHOLDファイルとSQL_SCRIPTのHOLDの違い

出力形式出力形式保存内容
物理ファイル（.ftm）WebFOCUSサーバに保持WebFOCUSサーバに物理ファイルとして保持
SAME_DBWebFOCUSサーバに保持 データ取得のSQL文
ポイント

出力形式BINARYやALPHAなど一般的なHOLDファイルの出力形式の場合にはWebFOCUSサーバにデータが含まれた一時ファイルを作成します。出力形式SQL_SCRIPTではデータを取得するためのSQL文として作成します。作成されたSQL文を発行しての処理のためWebFOCUSサーバの負荷が軽減し、効率的にデータを取得するためデータベース一時ファイルの再検索や一時ファイルを用いた結合でパフォーマンスの違いがでます。

構文

ON TABLE HOLD [AS filename] FORMAT SQL_SCRIPT

・filename

　HOLD ファイル名を指定します。SQL文を保持する物理ファイル名称となります。

　省略すると、一時テーブルの名前はデフォルトの「HOLD」になります。

使用例

Oracle の EMP 表とDEPT 表を利用した場合のサンプルです 。

-*EMP表からSMITH（ENAME）の職種（JOB）を求めるリクエスト
TABLE FILE EMP
PRINT JOB
WHERE ENAME EQ 'SMITH';
ON TABLE HOLD FORMAT SQL_SCRIPT                                             
END

-*HOLDファイルに存在するJOB列の値と一致するレコードのみを抽出するリクエスト
TABLE FILE EMP
PRINT ENAME
BY JOB
BY DEPTNO
WHERE DB_INFILE(HOLD,JOB,JOB); 
END

DB_INFILE関数は、スクリプトファイルの実行結果（ターゲットファイル）と検索ファイル
（ソースファイル）の項目値を比較する関数です。

上記のサンプルで指定した「DB_INFILE(HOLD,JOB,JOB)」は以下の意味になります。

DB_INFILE(SQL_SCRIPTで生成したHOLDファイル名, EMP表のJOB列, HOLDファイルのJOB列)

発行されるSQLの例

 SELECT
 T1."ENAME",
 T1."JOB",
 T1."DEPTNO"
 FROM
 SCOTT.EMP T1
 WHERE
   (T1."JOB" IN ( SELECT
  T1."JOB" AS "VB001_PRN_JOB"
    FROM
   SCOTT.EMP T1
    WHERE
   (T1."ENAME" = 'SMITH')))
   ORDER BY
  T1."JOB",
  T1."DEPTNO";

注意点

　・SQL文での処理のため、SQLで矛盾が発生するような結合した際にうまく結合されない可能性があります

テクニカルメモ