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

HHeLiBeXの日記 正道編

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

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

参考

クエリの書き方が問題となってパフォーマンスの超劣化を引き起こしたケース。