firebird01

$ ./test002.pl "WAMEI=センダイムシクイ"
via view to join ---Select:   1 rows Elapsed:  18.796 msec
fetch by program ---Select:   1 rows Elapsed:   7.855 msec
use procedure ------Select:   1 rows Elapsed:   1.966 msec

改良バージョン

$ ./test002.pl "WAMEI=センダイムシクイ"
via view to join ---Select:   1 rows Elapsed:   2.940 msec
fetch by program ---Select:   1 rows Elapsed:   7.909 msec
use procedure ------Select:   1 rows Elapsed:   2.001 msec

4.1.4. 結果の検証

最初のバージョン

最初にやってみて、愕然としました。
少しというか、かなりというか、焦りました。

JOIN 方式は PROCEDURE 方式に比べて 10倍の時間がかかっています。

これが 2.4.2. 森の妖精の項で述べた話です。主キー項目に UNIQUE 制約を書いていなかったのです。

改良バージョン

UNIQUE 制約を付けてやり直したものです。

これでも、PROCEDURE 方式の方がかなり速いです。

もちろん、この差を有意と見るか無視するかは用途と目的次第です。
今回の目的ではまったくどっちでもいいのですが、これがもし大量の検索要求を短時間で捌き続けなくてはならない WEB システム等だったらどうでしょうか？
運用中のシステムの約 40% の性能向上をハードウェア増強でやろうとしたら、どれだけ大変なことか？

こういうバグを作り込んでしまった場合、マクロな総合テストでは、まず分かりません。
一つ一つのミクロな処理に関して検証しないと見えてきません。
そして、時間が経ちデータ量が増大していよいよシステムが力を発揮するべき時に、思わぬ事態になります。

4.1.5. イスカンダルの鳥

バグだなんて思っていません

改良前の最初の (バグ持ち) バージョンで、データ量を変化させてみます。

実際のシステム開発では、プログラマにはごく少量のテストデータしか与えられないことがよくあります。
(それすらなくて、プログラマが自分で数行のデータを打ち込んでいたりする光景も見かけます)

そもそも、末端のプログラマに渡された仕様書に運用時のデータ量が書いてあるのか、テスト指示書にデータ量が指定されていたのか？すら怪しい。
少量データの場合は、INDEX を使わない方が速い場合もあるわけですから。

こんなしょぼいテストで

もう一度、O( logN ) と O( N ) あるいは O( NlogN ) の違いを思い出してください。

前項の例では、10,000 件のデータを使用したので、注意深く見ると違いが見えました。

では、1,000 件のテストデータでやってみましょう。

$ ./test002v.pl WAMEI=センダイムシクイ
via view to join ---Select:   1 rows Elapsed:   3.005 msec
$ ./test002p.pl WAMEI=センダイムシクイ
use procedure ------Select:   1 rows Elapsed:   2.042 msec

これでは、熟練したテスターでさえ、問題を見落とすでしょう。

そして、そのまま本番稼働、次第にデータが増えてきました。

ある日、未知の惑星イスカンダルに旅した生物学者が 100万種類の鳥を発見してきてリストに加え、データベースは一挙に 100万件に増加しました。ビーグル号の航海によるダーウィンのガラパゴス以来の大発見です。

壊滅的な破綻

1,000,000件のデータでテスト。バグ持ちビューの壊滅の様子。

$ ./test002v.pl WAMEI=イスカンダルセンダイムシクイ
via view to join ---Select:   1 rows Elapsed:5651.025 msec
$ ./test002p.pl WAMEI=イスカンダルセンダイムシクイ
use procedure ------Select:   1 rows Elapsed:   1.936 msec

実際には FireBird では、INDEX の再作成を行うと、データ記述に UNIQUE 制約が抜けていても、実態を見て UNIQUE キーであると認識することが多いようで、これほど酷い事態にはならないことが多いです。

が、100% いつでも成功するわけではありません。この例は、全件検索して O( N ) の時間がかかってしまったケース。

INDEX 再作成後

$ ./test002v.pl WAMEI=イスカンダルセンダイムシクイ
via view to join ---Select:   1 rows Elapsed:   3.283 msec
$ ./test002p.pl WAMEI=イスカンダルセンダイムシクイ
use procedure ------Select:   1 rows Elapsed:   2.012 msec

でも、これは賢いパーサが気を効かせてくれただけの幸運に過ぎないので、
むしろ、INSERT と CREATE INDEX その他の順番・タイミング、データ量等さまざまな条件で変化するため、余計に見逃す可能性が増えます。

データ量は怖くない

そして、もう一つ重要なこと。正しく設計されたシステムでは 1,000件でも 100万件でも、測定誤差程度の違いしかないことを『実感・体感！』してください。

さらに、10倍。1,000 万件のデータを生成してみました。それでも、殆ど変わりません。

$ ./test002v.pl WAMEI=イスカンダルセンダイムシクイ
via view to join ---Select:   1 rows Elapsed:   3.070 msec
$ ./test002p.pl WAMEI=イスカンダルセンダイムシクイ
use procedure ------Select:   1 rows Elapsed:   2.134 msec

データベースシステムにとっては 1,000万件なんて『屁』でもないんですよ、実のところは。

自分で手を動かして「実感して、体で感じる」って、大事です。
理屈だけ知っていても、いざ開発の修羅場になると、「データが増えると検索が遅くなる」という日常的感覚に引きずられて正確な判断ができなくなります。
そう、エクセルだって行数が増えたら遅くなりますものね、そう考える方が自然。
でも、自然な日常感覚だけで事が済むなら、コンピュータなんでイラネ！

4.3. 種の英名からの検索

4.3.1. 検索の条件

結構、いろんなパターンがあります。ワードの数も不定です。
アルファベット以外にアポストロフィがあったりするので、エスケープ処理をしっかりやらないとはまります。
また、大文字小文字の使い方に法則がないので、case insesitive な検索を必要とするという前 2例と違う条件が加わります。

4.3.2. テストプログラム

名前の正規化の部分

英名の正規化・プログラムのコード

ルーチン(1) VIEW 経由 JOIN で読んでみるテスト

JOIN して読むプログラムのコード

ルーチン(2) プログラムが自前で4つのテーブルを読むテスト

個別にテーブルを読むプログラムのコード

ルーチン(3) ストアードプロシジャを使うテスト

PROCEDURE を使うプログラムのコード

4.3.3. 実行結果

$ ./test004.pl "ENAME=Black-headed Gull"
via view to join ---Select:   1 rows Elapsed:  38.727 msec
fetch by program ---Select:   1 rows Elapsed:  28.660 msec
use procedure ------Select:   1 rows Elapsed:  22.362 msec

4.3.4. 結果の検証

おやおや？遅いぞ

前の2例に比べて凄く遅いです。何が違うんでしょう？
そうです。Case insensitive な検索のために UPPER() 関数を使用して Case 変換しているところが違います。

「なるほど。納得。」ですか？
ちょっ～と待った！

そんなことで納得しないでください。
アセンブラや C を知っている方なら「そんな馬鹿な」と思うでしょう。
Case 変換なんて bit 演算 1命令に過ぎません。処理時間に影響するはずがないと。でも、現実は大きな差が出ました。

察しのよい方はもうお気づきだと思います。ちょっと実験してみましょう。

SQL> SHOW INDEX SPSE;
SPSE INDEX ON SPECIES(SPECIESENG)
SQL> DROP INDEX SPSE;
SQL> COMMIT;
SQL> QUIT;
$ ./test004.pl "ENAME=Black-headed Gull"
via view to join ---Select:   1 rows Elapsed:  37.476 msec
fetch by program ---Select:   1 rows Elapsed:  27.526 msec
use procedure ------Select:   1 rows Elapsed:  22.629 msec

結果は変わりません。そう、INDEX は使われていなかったのです。

よく考えると当たり前なんですが、キー比較に UPPER() 関数を使うことで INDEX が使えなくなります。
CASE 変換する前の値で INDEX は作成されていますから。
全件サーチしていたわけです。

種の英名に付けた INDEX は結局使い途がありません。無駄だったと言うことです。

速くしてみる

まぁ、そういうもんだと諦めるものいいですが、速度向上や負荷軽減を求められる場合もあります。
そんなときは、UPPER-CASE に変更した検索専用の列を作るというのがよくある常套手段です。

新しい列を作り、そちらのキーで検索するように直したプログラムを実行。

SQL> ALTER TABLE SPECIES ADD SPECIESENGCAP VARCHAR(40);
SQL> UPDATE SPECIES SET SPECIESENGCAP=UPPER(SPECIESENG);
SQL> CREATE INDEX SPSC ON SPECIES(SPECIESENGCAP);
SQL> COMMIT;
SQL> QUIT;
$ ./test005.pl "ENAME=Black-headed Gull"
via view to join ---Select:   1 rows Elapsed:   3.205 msec
fetch by program ---Select:   1 rows Elapsed:   7.905 msec
use procedure ------Select:   1 rows Elapsed:   1.969 msec

期待通り、速くなりました。
しかし、これは意味的には冗長なデータで、データの正規型を崩していますので、目的をよく考えた上で、使いましょう。

4.4. 複数行の検索

4.4.1. 検索の条件

こんどは、「属名」「科名」「目名」などをキーにして検索するパターンです。
複数行の結果を受け取らなくてはなりません。

学名は、正規表現的には/^[A-Z][a-z]*$/です。すなわち、先頭1文字のみがアルファベット大文字、残りはアルファベット小文字で 1ワードのみからなります。空白は含まれません。

和名は、種の和名と同じでカタカナが原則です。

4.4.2. テストプログラム

処理としてはみな同じような物なので、「科の学名」から検索する例で検証してみます。

ルーチン(1) VIEW 経由 JOIN で読んでみるテスト

JOIN で複数行読むプログラムのコード

ルーチン(2) プログラムでループを回して読んでみるテスト

明示的にループ回すプログラムのコード

ルーチン(3) ストアードプロシジャを使ってみるテスト

複数行の結果を返すプロシジャを使うプログラムのコード

4.4.3. 実行結果

科といっても、1属1種しか含まれないものから、全体の1割を超える1015種を含む Fringillidae (アトリ科) まで、さまざまです。

個別に見てもバラツキが大きく分かりにくいので、傾向を掴みやすいようにグラフ化してみました。
X 軸に抽出したデータ件数、Y 軸に処理時間 (msec) をとってプロットしています。
3種の方式についてX軸にデータ量Y軸に処理時間をとってプロットしたグラフ

4.4.4. 結果の検証

大まかな検証

全体に、PROCEDURE の方が JOIN より速い傾向があります。
いずれにしても差は少ないですし、諸々の条件により逆転することもあるので参考程度に見てください。

面白いのは、抽出行数 30 と 100 の付近に不連続点があって、本来遅いはずの「プログラム自前ループ方式」に PROCEDURE や JOIN がこの区間では負けることです。FireBird 内のデータ格納の物理構造やロジックが、何か最適化の切り替えなどやっているのでしょう。

不思議な不連続点

不思議なことに、この不連続点は localhost の FireBird DBMS にアクセスしたときだけに現れ、ネットワーク経由だと現れません。
localhost であろうと、他のホストであろうと、tcp/ip ソケットを経由して等価なはずなのですが、なぜでしょう？
また、この現象が見られるのは Version-1.5 系列のみで、Version-2.0 以降の FireBird では現れませんでした。
興味ある現象なのですが、どうせ近いうちに UTF-8 化のために Version-2.1 系列に変える予定でもあり、深く追求はしていません。

少し細かな部分を

上の例では、"ORDER BY"句を指定していません。
実際には、SEQNO 順に読みたいことが多いので、"ORDER BY"指定でやり直してみました。
3種の方式についてX軸にデータ量Y軸に処理時間をとってプロットしたグラフ

あらら、PROCEDURE と JOIN の順位が逆転しちゃいました。
たぶん、FireBird の PROCEDURE の実行時の ORDER BY の扱いが賢くないんでしょう。

健全なプログラマのために

これぐらい、気にするほどの差ではないよ。というのが一般的には健康だと思います。この程度でプログラムの価値は変わりません。
でも、この差を「あぁ、ORDER BYのせいね。」で済ますプログラマと、直感的に「おかしい。これは異常値だ」と思うプログラマでは、プログラマの価値としては大違いだと思います。

プログラマのセンス

「ソートにこれほど時間がかかるはずはない。アプリケーションプログラム側でソートした方が速いぞ。」
と、直感で思ったあなた。よいセンスです。
標準 C ライブラリの qsort() 関数が (Perl は普通、裏で C ライブラリを呼んでいます) どれぐらいの処理コストでどれほどの負荷で済むのか、ということを実感として把握できていて、プログラミング時に意識できている人です。

他の 2 方式にはORDER BYを指定、プロシジャの実行時にはORDE BYを指定せずにアプリケーション側で一旦配列に格納してからその配列をソートする時間まで含めて測定してみたのが以下のグラフです。
3種の方式についてX軸にデータ量Y軸に処理時間をとってプロットしたグラフ