フロントエンドを100000000倍速くした(ﾟ∀ﾟ) - Oracle10g・DB2 V8.2時代の記錄

[フロントエンドを100倍速くした（ ＾ω＾）](https://qiita.com/nanocloudx/items/ed53c1cdb90f8d0f120c) を読んで私むかし100000000倍早くしたな、と思ったので2007, 2008年くらいの話の雑記です。先に **Oracleは10g、DB2はV8.2だった** 頃、と書いておきます。

## データベースマイグレーションの思い出
その昔データベースのマイグレーションのお仕事をしました。
Oracle製プログラムをDB2に対応させるのです。
そのプログラムはなんとも関数、プロシージャの巧みな使い手で数100はくだらない数のオブジェクトがありました。結果何が起こったのか。

## 10カラム、ストアド関数をSELECT実行
(サンプルです)
<pre>
db2 => select
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0)
db2 (続き) => from dual
db2 (続き) =>
db2 (続き) => /

1                    2                    3                    4
    5                    6                    7                    8
        9                    10
-------------------- -------------------- -------------------- -----------------
--- -------------------- -------------------- -------------------- -------------
------- -------------------- --------------------
SQL0437W  この複合照会のパフォーマンスが最適ではない可能性があります。理由コード

: "1"。  SQLSTATE=01602

         73.00000000          73.00000000          73.00000000          73.00000
000          73.00000000          73.00000000          73.00000000          73.0
0000000          73.00000000          73.00000000

  選択された 1 レコード (1 警告メッセージ) が表示されました。
</pre>

> 説明:
> 照会が複雑なために、使用できないリソースを要求しているか、または最
適化境界条件が見つかったために、ステートメントのパフォーマンスが次
善のパフォーマンスになる可能性があります。以下が理由コードのリスト
です。

> sqlcode : +437
> sqlstate : 01602

データベース構成ファイルのステートメント・ヒープ (stmtheap)の大きさを増やしてください。と言われた。

**実行にかかる時間、およそ30分………** 
Oracleではもともと、3秒ほどで結果がかえっていたもの。


## そのストアドの中身（サンプル）

```stored.sql
CREATE FUNCTION OBJECT_PKG.GET_CONTENT (p_id DECIMAL(10,0), 
                                        p_date TIMESTAMP, 
                                        p_sts DECIMAL(3,0)) 
RETURNS DECIMAL(18,8)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
    DECLARE out_num DECIMAL(18,8);
    IF p_sts=0 THEN
        SET (out_num)
               = (SELECT id
                  FROM TABLE
                  WHERE status = p_sts 
                  );
    ELSE
        SET (out_num)
               = (SELECT id
                  FROM TABLE
                  WHERE status = p_sts 
                  AND date <= TO_DATE('2010/12/31','YYYY/MM/DD')
                  AND date >= TO_DATE('2010/12/31','YYYY/MM/DD'));
    END IF;
    RETURN out_num;
END
```
まあ、こんな関数を大量生産していて。コストは掛かる。遅い。


## 解決策

使い物にならなかったので、DB2データベースでは、下のようにJava製埋め込みに再作成しました。

```stored.java
package jp.co.db2udf.getcontent;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;

public class GetContent extends DBUtil {

    public static BigDecimal get_content(BigDecimal p_id , Timestamp p_date , BigDecimal p_sts ) throws SQLException{    	
        BigDecimal out_num = BigDecimal.valueOf(99) ;
        StringBuffer Sql = new StringBuffer();
        String ret = null;
        DBConnect();
        if (p_sts.intValue() == 0) {
            //out_num
        	Sql.append("SELECT id ");
        	Sql.append("FROM table ");
        	Sql.append("WHERE status = "+ p_sts  );
        } else {
            //out_num
        	Sql.append("SELECT id ");
        	Sql.append("FROM table ");
        	Sql.append("WHERE status = " + p_sts );
  		Sql.append("  AND sdate <= TIMESTAMP ('2010-12-31-00.00.00.000000') ");
  		Sql.append("  AND edate >= TIMESTAMP ('2010-12-31-00.00.00.000000') ");
        };
		ps = con.prepareStatement(Sql.toString());
		rs = ps.executeQuery();
		if(rs.next()){ 
			ret = rs.getString(1);
		} else {
			ret = "0";
		}
		if (ret.equals("") || (ret.equals(null))) {
			ret = "0" ;
		};
        DBDisConnect();
        out_num = new BigDecimal(ret); 
        return out_num;
    }
}
```
何をやっている関数なのかは、業務上のことなので置いておいて。


```execute.sql
select 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0), 
 OBJECT_PKG.GET_CONTENT(1, current timestamp, 0)
from dual 
/
```
改めて測定！

## Java化後、測定結果
### Before
<pre>
Access Plan:
-----------
	Total Cost: 		29600.3
</pre>
### After：（大雑把に100000000分の1！）
<pre>
Access Plan:
-----------
	Total Cost: 		0.000333947
</pre>


## 10カラム実行時
<pre>
db2 => select
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0)
db2 (続き) => from dual
db2 (続き) => /

1                    2                    3                    4
    5                    6                    7                    8
        9                    10
-------------------- -------------------- -------------------- ----------------
--- -------------------- -------------------- -------------------- ------------
------- -------------------- --------------------
          0.00000000           0.00000000           0.00000000           0.0000
000           0.00000000           0.00000000           0.00000000           0.
0000000           0.00000000           0.00000000

  1 レコードが選択されました。
</pre>

ワーニングが出力されなくなった！
（そんな当たり前のことに **感涙**...というか、これがデータベースの差か、と当時衝撃を受けたプロジェクトでした。）


## 解説
**Oracleは10g、DB2はV8.2だった**、古き良き当時の思い出話です（計測した数値は実際のものです）。少しだけ解説すると、OracleのSQLは独自に拡張した「PL/SQL」ですが，DB2 UDBは標準SQLだったり。方言の許容度合いがかなり違った。参考：[OracleをDB2に変更する際の注意点を知りたい](https://xtech.nikkei.com/it/members/NOS/TROUBLE/20020731/1/)

[DB2逆引きWiki](http://db2watch.com/wiki/index.php?title=%E3%83%A1%E3%82%A4%E3%83%B3%E3%83%9A%E3%83%BC%E3%82%B8) 非常にお世話になりました。 
[移行資料](https://www.ibm.com/developerworks/jp/data/library/db2/j_d-oracleikou/)、すでに、だいぶ状況は変わっていますのでご注意をば。あと別にフロントエンドの話じゃなかったですね...

思い出話でした。というか元のプログラム... という話なので自慢でもなんでもないなと読み返す。以上です。