はじめに
下部に記したようなSQLがあります。そのままはコピペできないため、テーブル名等を加工しています。
これを2つの環境（以下、テスト環境・本番環境とする）で実行したときに、本番環境で実行した場合にのみ想定より時間がかかってしまうのですがその理屈がわからず、お力添えいただきたく存じます。
SQL
1SELECT2 SUB.SEQ_NO 3 ,SUB.ID 4 ,SUB.PROCESS_DATE 5 ,SUB.IMAGE_KEY 6FROM7 (8 SELECT9 /* 行番号を振る*/10 ROW_NUMBER() OVER (ORDER BY MAIN.ID) AS SEQ_NO 11 ,MAIN.ID AS ID 12 ,MAIN.PROCESS_DATE AS PROCESS_DATE 13 ,CASE WHEN MAIN.NO_IMAGE = '1' THEN ''14 WHEN MI.IMAGE_DATA IS NULL AND SI.IMAGE_DATA IS NULL AND BI.IMAGE_DATA IS NULL THEN ''15 ELSE MAIN.ID ||MAIN.SEQUENCE || PROCESS_DATE END AS IMAGE_KEY 16 17 FROM18 MAIN_TABLE MAIN 19 20 LEFT OUTER JOIN MAIN_IMAGE MI 21 ON MAIN.ID = MI.ID 22 LEFT OUTER JOIN SUB_IMAGE SI 23 ON MAIN.ID = SI.ID 24 LEFT OUTER JOIN BACKUP_IMAGE BI 25 ON MAIN.ID = BI.ID 26 )SUB 27WHERE28 SUB.SEQ_NO <= 100000
調べたこと
1. 実行計画
両環境において、全く同じSQLの実行計画を取得しました。すると、トータルコストに大きな差が出ました。SQLを加工している関係上実行計画をそのまま貼付する訳にもいかないのですが、具体的に記載しますと本番環境の方はソート対象行数がおおよそ6000億行ぐらいにまで膨れ上がっており、そこにかなりコストおよび時間がかかっているようでした。検証環境の実行計画は、ソート対象行数は10万行となっており想定通りでした。つまり、テーブルのレコード数に起因することがここでわかるかと思います。
2. SQL本体
実行計画から「どの記述が影響しているか」を特定できなかったため、SQLの結合条件やSELECT句などをコメントアウトしながら本番環境で都度実行し、「どの部分がコメントアウトされた時点でコストが増えるのか」という調査をしました。すると、下部に記載したSQLのうち、15行目の以下部分をコメントアウトするとコストは下がり、実行計画上のソート対象行数も10万行になりました。
AND BI.IMAGE_DATA IS NULL
確かに検証環境と比較すると、BACKUP_IMAGEには大量のレコードが登録されていましたので（検証環境：約100件、本番環境：約1500万件）、そこの件数が影響していることもここでわかるかと思います。
また、BACKUP_IMAGEテーブルは現状登録されているデータの件数から増減が一切ないため、統計情報の更新は行われていません。数年前に1500万件を一気に登録した時点で統計情報を取り、それ以降はデータの増減がないようです。ですのでインデックスが効いていない、というわけでもないかと思います。
不明点
・テーブル同士を結合しただけでは特にコストが増えないのに、CASE文の中で結合先のテーブルカラムを参照するとコストが爆増する理屈がわからない。実行計画を見るに、ソート対象行数が増えているため、たとえば結合条件が間違っていて取得行が増殖するようなSQLになっているとかなら納得はできるが、今回登場するMAINテーブルとBACKUP_IMAGEテーブルの関係は1対1なのでそれも起きえないと考えている。
補足情報
・MAIN_IMAGE、SUB_IMAGE、BACKUP_IMAGEのいずれも、IMAGE_DATAカラムに画像ファイルをBLOB形式で保持しています（平均100KBほど）。
・本文に記載済みですが、MAINと各種IMAGEについては1対1になります。
MAINテーブル
| ID | PROCESS_DATE |
|---|---|
| 001 | 20231123 |
MAIN_IMAGE/SUB_IMAGE/BACKUPテーブル
| ID | IMAGE_DATA |
|---|---|
| 001 | (BLOB) |
長文になりましたが、ご助力願えればと思います。
宜しくお願い致します。
0 コメント