読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

キャストを使うなら覚悟を持って

SQL DB2

どのような言語にも、機能的には便利だが使いどころを誤ると大変なことになるというものが(恐らく)存在する。
何らかの数値を表すデータを、RDBに文字列で格納して検索時にキャストして比較するという、嘘のような本当の話があるらしい。詳しい状況は分からないので断言はできないが、そういったSQL文に悩まされるのは、たいていの場合は書いた本人(恐らくDDLも書いているだろう)ではなくその周りの開発者だったりテスト技術者だったり、はたまたプロジェクトに責任を持つ偉い人だったりする(というのは私の勝手な想像)。
たとえば、ちょっとしたことを検証しようと思っても動作が遅くて開発が思うように進まない、テストが思うように進まないとか。この時点で気づけばまだましかもしれない(納期間近の修正に追われるのは目に見えているが)。納品して大量のトランザクションが処理されるようになって発覚したらもう最悪。
‥と、前置きが長くなったが、そういうことが少しでも減るようにという願いを込めて、戒めの意味で、結果は分かりきっているが敢えて実験してまとめてみる。


聞いた話(何)を要約すると、「全部CHAR型で格納して」おり、「検索するときにキャストして」いるということは分かっている。
そこで、次のようなテーブルをまず定義する。

CREATE TABLE hoge(
    v_i INTEGER NOT NULL UNIQUE
  , v_c VARCHAR(8) NOT NULL UNIQUE
)

幸か不幸か(謎)、2つのカラムにはそれぞれユニークインデックスが作成されるだろう。
そして、このテーブルに20万件の行を追加する。

INSERT INTO hoge(v_i, v_c) VALUES(0, '0')
INSERT INTO hoge(v_i, v_c) VALUES(1, '1')
INSERT INTO hoge(v_i, v_c) VALUES(2, '2')
  :
INSERT INTO hoge(v_i, v_c) VALUES(199999, '199999')

このテーブルに対して、次のクエリを発行し、結果出力までの時間を計測してみる。

-- (1)
SELECT v_i, v_c FROM hoge WHERE v_i = ?

-- (2)
SELECT v_i, v_c FROM hoge WHERE v_c = ?

-- (3)
SELECT v_i, v_c FROM hoge WHERE CAST(v_c AS INTEGER) = ?

「?」の部分はパラメータで、「0」「199999」「200000」を渡したものをそれぞれ実行する(前2つはテーブル中に存在する値、最後の1つはテーブルには存在しない値)。
今回はとりあえずDB2のみだが、ほかのDBMSでも同じ条件で試してみたいということがあるので、次の方針で実行、計測を行った。

  • 計測対象はDB2 Express-C (v9.7.2)
  • 計測環境はいつものLet's Note CF-W5 (なので、計測値そのものではなく比率を見てください)
  • Javaプログラム内で「テーブル作成」「データ挿入」「計測」「テーブル削除」をすべて行う。
  • db2batchは使わず、Javaプログラム内で System.currentTimestampMillis() を使用して時間計測を行う。
  • 各クエリを10回ずつ実行し、各回の実行時間のトータルを、そのクエリの実行コスト(秒)とした。

その結果は次のとおり。

0 199999 200000 平均
(1) 0.053 0.033 0.029 0.0383
(2) 0.032 0.037 0.026 0.0317
(3) 1.016 0.981 0.979 0.9920

テーブル内のデータに対してキャストをしている(3)のケースが圧倒的に遅い。
(3)は(1)や(2)と比べて26〜31倍ですよ、お客さん(誰)。


SQLクエリの最適化はRDBMSの仕事、などといって現実から逃げるのは簡単だが、ちょっと立ち止まって考える勇気(何)を持ってほしいなぁと思う。
今回の(3)のクエリでは、カラム v_c をINTEGER型にキャストした結果得られる値がどうしても必要になる。ならば各値に対してキャスト処理(要はデータ変換だよ)を行わなければならない。よって、当然カラム v_c に対して作成されたインデックスはそもそも意味を成さない(キャストした結果をインデックス化してくれるDBMSなんて(恐らく)ないから)。
と、ここまで考えることができれば、(3)のクエリはスケールしないことが分かるでしょう。データ設計を見直さないといけないことが分かるでしょう。
また、そもそもこういうことを知識として知っていたら、データ設計の段階で正しい道を選ぶこともできるはず。
もちろん、そのほかの要因が絡んでくることが多々あるので、そうそう単純じゃないことも知っているが‥


不幸なITエンジニアが少しでも減ることを願って(謎)。

以上、おわりっ。


ちなみに、使用したJavaプログラムの全貌はこちら。