この記事はSC（非公式） Advent Calendar 2018の21日目です。
はじめに
タイトルは「爆速になった」などと仰々しいですが、
結合数を減らして一回の処理は小さくしたほうが何かとメリットは多いという体験談となります。
本編
現在の業務では、PL/SQLを使用して「ある領域のマスタ群」を「別領域で使用するマスタ」に編集する機能を作っています。
領域Aのマスタ群は顧客マスタのプライマリーキーである顧客コードによって紐づいています。
上図の 領域A にあるマスタは、「送信可否フラグ」を保持しており、
フラグが「１：送信可」となった場合に編集処理を介して 領域B に登録されるような作りです。
ただし領域Ａからは送信可否フラグが「０：送信不可」状態のレコードもどんどん連携してくるため、
編集対象外として弾かなければなりません。
また、業務によって「送信可否フラグ」のチェック対象マスタとチェック対象外マスタが分かれるため、
下記マトリクスに沿って、業務毎にチェックするマスタを切り替える必要があります。
| 業務A | 業務B | 業務C | 業務D | 業務E | 業務F | |
|---|---|---|---|---|---|---|
| 顧客マスタ | ○ | ○ | ○ | ○ | ○ | ○ |
| 子マスタ１ | ○ | ○ | - | ○ | ○ | ○ |
| 子マスタ２ | △ | △ | △ | △ | △ | - |
| 子マスタ３ | - | - | △ | △ | △ | ○ |
凡例：
○ フラグを保持しているためチェック対象
△ 顧客マスタの区分値によってチェックする場合としない場合がある
- チェック不要
上記要件を満たすにあたり、対象データの送信可否を判定する下記関数を用意しました。
create or replace function F_CHECK_TARGET (target_cd in char)
R_CD number(1);
/* 対象顧客コードが連携可能状態ならば１を返す */
select
case when count(*) > 0 then 1
else 0 end into R_CD
from
顧客マスタ 顧客
inner join 業務マスタ 業務
on 顧客.顧客コード = 業務.業務コード
left outer join 子マスタ１ 子１
on 子１.顧客コード = 顧客.顧客コード
left outer join 子マスタ２ 子２
on 子２.顧客コード = 顧客.顧客コード
left outer join 子マスタ３ 子３
on 子３.顧客コード = 顧客.顧客コード
where
顧客.顧客コード = target_cd
and 顧客.送信可否フラグ = 1
and
(
業務マスタ.業務コード in ( 1 , 2 )
and 子マスタ１.送信可否フラグ = 1
and
(
(顧客.債権フラグ = 1 and 子マスタ２.送信可否フラグ = 1)
or
(顧客.債権フラグ = 0)
)
～～～中略～～～
or
(
業務マスタ.業務コード = 6
and 子マスタ１.送信可否フラグ = 1
and 子マスタ３.送信可否フラグ = 1
)
);
return R_CD;
業務毎のマスタをチェックして、取得件数が「１」以上なら編集対象、「０」なら対象外を返す作りになっています。
抽出条件を見ていただければわかる通り、業務毎にチェックするマスタが変わることもあり、where句以下の見通しがかなり悪いです。
また、今後業務毎のチェック要件が増えた場合の対応箇所もパッと見で分かりにくいです。
メンテナンスしずらいコード自体よろしくないですが、一番の問題は処理速度でした。
上記それぞれの子マスタの親にあたる顧客マスタに３００万件、そのほか、子マスタ群に各２０～３０万件の移行データが入った状態で１００件のデータに対してチェック処理を実施してみたところ、結果出力までに２２３０秒かかりました。
約３７分間結果が返ってこないことになります。非常に遅いです。
Statspackで取得していただいた実行計画を見てみると、３００万件から対象の１行を結合先の行に１行ずつ突合せているため、１回のチェック処理で約４秒弱かかり、同様の処理をチェック対象に紐づくデータ分繰り返していることが原因だと判明しました。
上述したコードの見通しの悪さ解消と処理速度を合わせて解消するために、
一度に巨大SQLでチェックすることをやめて、業務毎の必要最小限のマスタのみをチェックするように書き換えました。
create or replace function F_CHECK_TARGET (target_cd in char, target_gym in number)
return as number
R_CD number(1);
S_FLG number (1); /* このフラグが「１」の場合、子マスタ２もチェック対象とする */
/* 顧客マスタチェック関数定義 */
function CHECK_CUSTOMER(target_cd in char) return number AS
begin
select
case when count(*) > 0 then 1
else 0 end into R_CD
,顧客.債権フラグ into S_FLG
from 顧客マスタ 顧客
where 顧客.顧客コード = target_cd
and 顧客.送信可否フラグ = '1';
return R_CD;
end ;
/* 以下、子マスタ１～Ｎチェック処理宣言部 */
function CHECK_CHILD_1(target_cd in char) return number AS
begin
/* 子マスタ１チェック */
select
case when count(*) > 0 then 1
else 0 end into R_CD
from 子マスタ 子１
where 子１.顧客コード = target_cd
and 子１.送信可否フラグ = '1';
return R_CD;
end ;
～～～中略～～～
/* 本処理 */
begin
R_CD := CHECK_CUSTOMER(target_cd);
if(R_CD = 0)then
/* 顧客マスタの送信可否フラグが「０」なら処理中断 */
return 2;
end if;
case
/* 業務Aチェック */
when target_gym = 1
R_CD := CHECK_CHILD_1(target_cd);
/*債権フラグが１なら下記マスタもチェック*/
if(S_FLG = 1) then
R_CD := CHECK_CHILD_2(target_cd);
end if;
/* 以下、業務毎のチェック処理（中身は業務Ａチェックと似たり寄ったりなので省略） */
when taget_gym = 2
when taget_gym = 3
when taget_gym = 4
when taget_gym = 5
when taget_gym = 6
/* 全チェックOKなら「1」を返す */
return R_CD;
end;
チェック対象のマスタ１つに付き、１つの関数を用意し、
業務毎にチェックする対象をcaseで分岐するように修正しました。
先ほどと同様に１００件のレコードで実行してみると２２秒という結果に。
先ほどは２２３０秒でしたので、処理にかかる時間は約１００分の１ほどになっています。
また、今後改修がはいり、「業務」や「チェック対象マスタ」が増える場合に、ＣＡＳＥ文、マスタ分のチェック関数を用意する箇所が明白なので対応が容易になりました。
おわりに
爆速になったというよりは、「処理を詰め込んで、見通しが悪く、遅く作ってしまったもの直した」のほうが表現としては適切だったかもしれません。
一回の大きな処理で完結するようなものを書きがちな自分への戒めとして投稿させていただきました。
・ひとつひとつの処理はできるだけ小さくシンプルに
・保守する人が自分とは限らないため、コードのわかりやすさを心掛ける
これらの意識は努々忘れないようにしたいです。