XMLデータのスキーマ設計は計画的に
「扱うデータをXMLにしておくと冗長性がなんたらかんたら‥」、「とりあえず全部放り込んでおいて、必要な部分を問い合わせ時に取り出すようにすればうんたらかんたら‥」。
まぁ、ある意味妥当な意見ではあるのだけど、それで問題がないという裏づけはきちんととっておこうよ、という事例。
発端
次のようなクエリが原因でアプリケーションのパフォーマンスが悪いという状況に出くわした。(なんかどこかで見たような書き出し(謎))
xquery count( for $doc in db2-fn:sqlquery(" select xmlquery('for $doc in $root/hoge return $doc' passing DOCUMENT.DATA as ""root"") from DOCUMENT where ( xmlexists('$root/hoge[head/head_nnnn = ""aaaa""]' passing data as ""root"") ) ") return <hoge>{$doc/head}{$doc/foot}</hoge>)
データ構造は次のような感じ。(中身は実はどうでもよく、ルート要素("hoge")の直下に3つの要素("head"、"body"、"foot")があるという構造があるというところがポイント)
<hoge> <head> <head_nnnn id_nnnn="AAAA">aaaa</head_nnnn> ： <head_nnnn id_nnnn="AAAA">aaaa</head_nnnn> </head> <body> <body_nnnn id_nnnn="AAAA">aaaa</body_nnnn> ： <body_nnnn id_nnnn="AAAA">aaaa</body_nnnn> </body> <foot> <foot_nnnn id_nnnn="AAAA">aaaa</foot_nnnn> ： <foot_nnnn id_nnnn="AAAA">aaaa</foot_nnnn> </foot> </hoge>
先のクエリを発行するケースではhead、footだけが必要(bodyは不要)なので、クエリの結果としては必要なものだけを返している。
検証
XMLEXISTSでの条件の書き方も悪いところはなく、また「return $doc」とした以前のケースではそれなりに早いことがわかっているので、return句が次のような8種類のクエリを比較してみる。(単に全組み合わせを並べてみただけともいう)
(1) return <hoge>{$doc/head}</hoge>
(2) return <hoge>{$doc/body}</hoge>
(3) return <hoge>{$doc/foot}</hoge>
(4) return <hoge>{$doc/head}{$doc/body}</hoge>
(5) return <hoge>{$doc/head}{$doc/foot}</hoge>
(6) return <hoge>{$doc/body}{$doc/foot}</hoge>
(7) return <hoge>{$doc/head}{$doc/body}{$doc/foot}</hoge>
(8) return $doc
(9) return $doc/headデータ構造は次のとおり。
<?xml version="1.0" encoding="UTF-8" ?> <hoge> <head> <head_0000 id_0000="0000">0000</head_0000> <head_0001 id_0001="0001">0001</head_0001> <!-- 中略 --> <head_0499 id_0499="0499">0499</head_0499> </head> <body> <body_0000 id_0000="0000">0000</body_0000> <body_0001 id_0001="0001">0001</body_0001> <!-- 中略 --> <body_0499 id_0499="0499">0499</body_0499> </body> <foot> <foot_0000 id_0000="0000">0000</foot_0000> <foot_0001 id_0001="0001">0001</foot_0001> <!-- 中略 --> <foot_0499 id_0499="0499">0499</foot_0499> </foot>
このデータを含む行を999行、値だけをちょっと変えた行を1行含むテーブルを作成。(値が異なる1行は、条件が誤っていないことを確認するためのデータ。)
で、クエリは、冒頭のクエリの"nnnn"と"aaaa"の部分を"0499"に置き換え、return句を8種類それぞれに設定したクエリを実行する。
実行は、db2batchを使用し、すべてのクエリを順に実行する処理を5回繰り返した。
結果
環境が貧弱なので、実行時間の比だけを見てほしい。
DB2 Express-C v9.1.2
タイプ 番号 反復回数 合計回数 最小回数 最大回数 算術平均 幾何平均 行が取り出されました 行が出力されました -------------- ----------- ----------- -------------- -------------- -------------- -------------- -------------- -------------------- ------------------ ステートメント 1 5 149.331740 29.153512 31.011920 29.866348 29.859392 1 1 ステートメント 2 5 148.971794 28.271250 31.616140 29.794359 29.762262 1 1 ステートメント 3 5 150.701198 28.632795 31.147782 30.140240 30.125730 1 1 ステートメント 4 5 272.031452 50.015365 59.396425 54.406290 54.306863 1 1 ステートメント 5 5 297.358146 50.927166 74.759690 59.471629 58.958606 1 1 ステートメント 6 5 295.796845 47.375023 87.408514 59.159369 57.382057 1 1 ステートメント 7 5 350.061164 67.452432 74.412940 70.012233 69.972739 1 1 ステートメント 8 5 12.953805 2.218610 2.844323 2.590761 2.581721 1 1 ステートメント 9 5 12.425808 2.205140 2.751628 2.485162 2.478537 1 1
DB2 Express-C v9.5.2
タイプ 番号 反復回数 合計回数 最小回数 最大回数 算術平均 幾何平均 行が取り出されました 行が出力されました -------------- ----------- ----------- -------------- -------------- -------------- -------------- -------------- -------------------- ------------------ ステートメント 1 5 32.664277 6.338880 7.198762 6.532855 6.524740 1 1 ステートメント 2 5 32.092672 6.327961 6.678250 6.418534 6.417185 1 1 ステートメント 3 5 31.696069 6.303805 6.437707 6.339214 6.339016 1 1 ステートメント 4 5 62.111532 12.331233 12.705064 12.422306 12.421501 1 1 ステートメント 5 5 61.820313 12.331115 12.399905 12.364063 12.364043 1 1 ステートメント 6 5 61.845095 12.330221 12.435906 12.369019 12.368961 1 1 ステートメント 7 5 108.500750 18.642864 27.323000 21.700150 21.390568 1 1 ステートメント 8 5 7.508053 0.503102 3.342799 1.501611 1.034632 1 1 ステートメント 9 5 2.650947 0.522084 0.543898 0.530189 0.530132 1 1
DB2 Express-C v9.7.1
タイプ 番号 反復回数 合計回数 最小回数 最大回数 算術平均 幾何平均 行が取り出されました 行が出力されました -------------- ----------- ----------- -------------- -------------- -------------- -------------- -------------- -------------------- ------------------ ステートメント 1 5 23.874916 4.670542 4.933571 4.774983 4.773877 1 1 ステートメント 2 5 23.444323 4.658684 4.764661 4.688865 4.688706 1 1 ステートメント 3 5 23.460546 4.666434 4.769542 4.692109 4.691948 1 1 ステートメント 4 5 47.858534 9.068959 11.422957 9.571707 9.530636 1 1 ステートメント 5 5 45.611118 9.066741 9.290298 9.122224 9.121824 1 1 ステートメント 6 5 45.331757 9.039090 9.116351 9.066351 9.066312 1 1 ステートメント 7 5 67.528199 13.425187 13.712791 13.505640 13.505237 1 1 ステートメント 8 5 1.130042 0.195741 0.324411 0.226008 0.221497 1 1 ステートメント 9 5 1.251687 0.244833 0.258668 0.250337 0.250289 1 1
いずれも、ステートメント1〜9がreturn句を(1)〜(9)に設定したクエリに対応する。
詳細なことを断じるにはもっと検証が必要だろうけど、これだけでも、もともとのXMLデータから結果のXMLデータを再構築するよりは、もともとのXMLデータをそのまま返すほうが格段に早いということは見えてくる。XMLデータを取り出すコストは同じはずだから、単純にXMLデータを構築する処理の問題だろう。
実はこれ、たいした検証もせずにとりあえずひとつのXML列にデータを詰め込み、また当初は(9)のケースで返していたのが、都合により"foot"も返す必要が生じたために(5)の形式にクエリを変更したという実話。
この話では、"foot"の中身は"head"の中でいいじゃん、というタイプのものだが、そういうことも含めて、スキーマ設計は慎重に行うべきというお話。
検証用データとクエリ
まず、データを生成するためのシェルスクリプト。
#! /bin/bash prefix=import_data mkdir generated # generate target data ( printf "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" printf "<hoge>\n" for t in head body foot ; do printf "<%s>\n" ${t} for ((i = 0; i < 500; ++i)); do printf "<%s_%04d id_%04d=\"%04d\">%04d</%s_%04d>\n" ${t} ${i} ${i} ${i} ${i} ${t} ${i} done printf "</%s>\n" ${t} done printf "</hoge>\n" ) > generated/"${prefix}".001.xml # generate dummy data ( printf "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" printf "<hoge>\n" for t in head body foot ; do printf "<%s>\n" ${t} for ((i = 0; i < 500; ++i)); do ii=$((${i}+1000)) printf "<%s_%04d id_%04d=\"%04d\">%04d</%s_%04d>\n" ${t} ${i} ${i} ${ii} ${ii} ${t} ${i} done printf "</%s>\n" ${t} done printf "</hoge>\n" ) > generated/"${prefix}".002.xml # generate DEL file ( for ((i = 0; i < 1000; ++i)); do if [ ${i} -eq 10 ]; then printf "%d,\"<XDS FIL='%s.002.xml' />\"\n" ${i} "${prefix}" else printf "%d,\"<XDS FIL='%s.001.xml' />\"\n" ${i} "${prefix}" fi done ) > generated/"${prefix}"
データベースやテーブルを生成するためのスクリプト。
CREATE DATABASE HOGE USING CODESET UTF-8 TERRITORY JP COLLATE USING SYSTEM PAGESIZE 32 K ; CONNECT TO HOGE USER db2admin USING admin ; DROP TABLE DOCUMENT ; CREATE TABLE DOCUMENT ( ID VARCHAR(8) NOT NULL, DATA XML NOT NULL, CONSTRAINT HOGE_DOC_PKEY PRIMARY KEY (ID)) ; CREATE INDEX DOC_DATA_HEAD_0499 ON DOCUMENT(DATA) GENERATE KEY USING XMLPATTERN '/hoge/head/head_0499' AS SQL VARCHAR(8) ; CREATE INDEX DOC_DATA_BODY_0499 ON DOCUMENT(DATA) GENERATE KEY USING XMLPATTERN '/hoge/body/body_0499' AS SQL VARCHAR(8) ; CREATE INDEX DOC_DATA_FOOT_0499 ON DOCUMENT(DATA) GENERATE KEY USING XMLPATTERN '/hoge/foot/foot_0499' AS SQL VARCHAR(8) ; IMPORT FROM import_data OF DEL MODIFIED BY LOBSINFILE COMMITCOUNT 100 INSERT_UPDATE INTO DOCUMENT ; RUNSTATS ON TABLE db2admin.DOCUMENT ; RUNSTATS ON TABLE db2admin.DOCUMENT FOR INDEX ALL ; REORG TABLE DOCUMENT ; REORG INDEXES ALL FOR TABLE DOCUMENT ; CONNECT RESET ;
そして、検証用クエリを生成するためのスクリプト。
#! /bin/bash function gen_query() { local return="${1}" local n=499 printf "xquery count(\n" printf " for \$doc in db2-fn:sqlquery(\"\n" printf " select xmlquery('for \$doc in \$root/hoge return \$doc' passing DOCUMENT.DATA as \"\"root\"\")\n" printf " from DOCUMENT\n" printf " where (\n" printf " xmlexists('\$root/hoge[head/head_%04d = \"\"%04d\"\"]' passing data as \"\"root\"\")\n" ${n} ${n} printf " )\n" printf " \")\n" printf " return %s\n" ${return} printf ")\n" printf ";\n" } ( printf "%s\n" "--#BGBLK 5" gen_query '<hoge>{$doc/head}</hoge>' gen_query '<hoge>{$doc/body}</hoge>' gen_query '<hoge>{$doc/foot}</hoge>' gen_query '<hoge>{$doc/head}{$doc/body}</hoge>' gen_query '<hoge>{$doc/head}{$doc/foot}</hoge>' gen_query '<hoge>{$doc/body}{$doc/foot}</hoge>' gen_query '<hoge>{$doc/head}{$doc/body}{$doc/foot}</hoge>' gen_query '$doc' gen_query '$doc/head' printf "%s\n" "--#EOBLK" ) > generated/query.sql
参考
クエリの書き方が問題となってパフォーマンスの超劣化を引き起こしたケース。
