SQLの実行パフォーマンスについて 2010


@IT エンジニアライフのコメンテータ(だった)生島さんのコラムhttp://el.jibun.atmarkit.co.jp/g1sys/2010/05/post-2d1b.htmlのコメント欄に参加しました。

生島さんのコラムですが、過去に度々炎上してきましたが、炎上するたびに、

『SQLはオブジェクト指向言語の数十倍の効率』

という、この手の話が出てきます。この手の呪文は他にも幾つかあるのですが、これを出せば議論が終結するというある種の必殺技みたいに使われます。
が、それどころか、毎回、毎回、明確な結論が出ずにさらにコメント欄が荒れます。
私としては、本来はどうでもよい話なのですが、いきがかり上、私も思わず、

「私は、過去にSQLが遅いのでSQLを崩して、C言語でJOINをやらせて高速化しました。OO言語ではないですが、今だったらC++を使うでしょう(なぜってハッシュクラス があるから)。」
と発言しました。恐らく、多くの方は、

『いやいや、いくらなんでも、それはウソでしょう。』
とか、
『売り言葉に買い言葉でしょうが、それは良くないでしょう。』
とか、
『幾らC++が好きって言ったって、原理的にDBMS内で処理が閉じるSQLの方が速いでしょう。』
とか思われたことでしょう。

私も、そういうツッコミが来ることは重々承知していたのですが、現実に私は10年以上前になりますが、上記のような最適化を行ったことがありました。
以来、別にわざわざSQLを崩してCでJOINなんて事はしませんでしたが、逆にその後、さまざまなプロジェクトを通して、DBMSの動作をみる限り上記の最終手段は、まだ有効だなというのも実感としてあったのですが、あまりの共感の得られ無さにものすごい孤独感に襲われ、また生島さんの煽りも受け、このあたりで白黒はっきり付けたいと思います。


では、どのように白黒つけるのかですが、やはりベンチマークテストを行ってみるしかないかと思います。

つまり、生島さんが件のコラムのコメント欄に書かれた

TABLE_A a
INNER JOIN TABLE_B b
    a.KEY = b.KEY

をもとにしたSQLをC++で書き実際に実行させてその実行時間をみてみましょう。


■実験の環境

 <追記>この記事のコメント欄の指摘(サーバーとクライアントマシンが分かれていない)および環境が変わったので再現できない関係で、新しく環境を構築してテストをやり直しました。
 今回実験したテスト環境を示します。

■実験するSQLとプログラムの概要


以下のSQLを実行させ、カンマをセパレーターとして標準出力へ出力させます。
CSVファイルへの出力を想定したSQL、JOINの部分は生島さんがコメント欄で指摘したSQLそのものになっています。このSQLをもとにJOIN部分をC++でやらせてみます。
SELECT Price.CODE, RDATE, OPEN, CLOSE, NAME
FROM Price INNER JOIN Company ON (Price.CODE = Company.CODE)

■実験1 素直にSQL側でJOINをさせたものを実行


 以下のコードのとおり、実験するSQLをそのまま実行してみました。

#include <iostream>
#include <time.h>
#include "../kz_odbc.h"

using namespace std;

int main(void)
{
    kz_odbc db("DSN=Trade",true);
    kz_stmt stmt(&db);

    time_t  t = time(NULL);

    // テーブルからデータの取得
    stmt, "SELECT Price.CODE, RDATE, OPEN, CLOSE, NAME "
          " FROM Price INNER JOIN Company ON (Price.CODE=Company.CODE)"
          , endsql;
    kz_string_array result = stmt.next();
    int             cnt = 0;
    while ( !result.empty() ) {
        cout << result[0] << "," << result[1] << ","
             << result[2] << ","  << result[3] << ","
             << result[4] << "\n";
        result = stmt.next();
        cnt++;
    }

    cerr << "Execute time is " << time(0) - t << "sec." << endl;
    cerr << "Record count is " << cnt << "." << endl;
    return 0;
}

コードですが、Wordpressに合わせて編集してますので、変なところで改行が入っていますが御勘弁を。
 若干ですが、コードの説明を、
 stmt, "SELECT ・・・・
 とか
 result.empty()
 stmt.next()
の部分が私が作成したライブラリになります。といってもODBC APIを呼び出しているだけになります。そう特異なものでもないかと思います。
 実行結果ですが、
Execute time is 131sec.
Record count is 4671568.  
 となりました。プログラムは標準出力に出力していますが、実行に際しては標準出力をファイルにリダイレクトしています。その方が実行速度は速くなります。
 比較元のデータが無いので何とも言えませんが、1秒間に約3万5千件のデータがCSVファイルへ落とされているのでマシンスペックを考えますとまずまずでしょう。
ちなみに、実行ブランを確認しましたが、CompanyテーブルへのアクセスのTYPEはeq_refでユニークキーによるJOIN(最速のテーブルアクセス)が実行されていることを確認しました。

■実験2 C++側でネステッドループでJOINさせてみる

 ループループといっていたものですが、いわゆるネステッドスープのことだと推測します。

#include <iostream>
#include <time.h>
#include "../kz_odbc.h"

using namespace std;

int main(void)
{
    kz_odbc db("DSN=Trade",true);
    kz_stmt stmt(&db);
    time_t  t = time(NULL);

    // テーブルからデータの取得
    stmt, "SELECT CODE,RDATE,OPEN,CLOSE FROM Price", endsql;

    kz_string_array result = stmt.next();
    int                cnt = 0;
    while ( !result.empty() ) {
        // JOINの実行(ネステッドループ)
        kz_stmt    stmt2(&db);
        stmt2, "SELECT NAME FROM Company WHERE CODE = ? "
             ,  result[0].c_str(), endsql;
        kz_string_array result2 = stmt2.next();

        cout << result[0] << "," << result[1]  << ","
             << result[2] << ","  << result[3] << ","
             << result2[0] << "\n";
        result = stmt.next();
        cnt++;
    }

    cerr << "Execute time is " << time(0) - t << "sec." << endl;
    cerr << "Record count is " << cnt << "." << endl;
    return 0;
}

実行結果は以下のとおりです。
Execute time is 1714sec.
Record count is 4671568.

 これはものすごく遅いですね。生島さんが、
『SQLにすると数十倍速くなる』
といっていたのは、実験1のコードと実験2のコードを比べて言っていたと思われます。
では、これ以上に速くさせる方法はないのでしょうか?
生島さんの言うとおり、OO言語はSQLと比べて何十倍も遅いのでしょうか?

■実験3 C++側でハッシュJOINさせてみる

 件のコメント欄で生島さんが難しいとおっしゃっていた、ハッシュJOINですが、実は特段、難しいものではありません。
 以下のようにすっきりと実装できます。
 ちなみにコード中に出てきますmapというのはバイナリサーチを行います。なので、正確にはハッシュJOINではありません。
 C++でハッシュ検索を行うには、Boost等のライブラリを使う必要があります。
 つまり今回のコードはある意味、最適化の余地を残しているのですが、ここではテストの再現性(環境設定)の手間を考えてmapを使います。

#include <iostream>
#include <time.h>
#include "../kz_odbc.h"

using namespace std;

int main(void)
{
    kz_odbc db("DSN=Trade",true);
    kz_stmt stmt(&db);

    time_t  t = time(NULL);

    // マスターの取得・マップの作成
    map< string, string>    company;
    stmt, "SELECT CODE, NAME FROM Company  ",  endsql;
    kz_string_array result = stmt.next();
    while ( !result.empty() ) {
        company.insert( pair< string, string>( result[0], result[1]) );
        result = stmt.next();
    }

    // テーブルからデータの取得
    stmt, "SELECT CODE,RDATE,OPEN,CLOSE FROM Price ", endsql;
    result = stmt.next();
    int                cnt = 0;
    while ( !result.empty() ) {
        cout << result[0] << "," << result[1] << ","
             << result[2] << ","  << result[3] << ","
             << company[ result[0] ] << "\n";
        result = stmt.next();
        cnt++;
    }

    cerr << "Execute time is " << time(0) - t << "sec." << endl;
    cerr << "Record count is " << cnt << "." << endl;
    return 0;
}


 結果ですが、以下のとおり、実験1のコードよりも早くなっております。
Execute time is 108sec.
Record count is 4671568.


■結果

実行結果を再度以下に掲載します。
 
実験1(SQL) 131秒
実験2(C++側でネステッドループ) 1714秒
実験3(C++側でハッシュ) 108秒


 明確に結果が出ているかと思います。こんなに単純なテストの結果からでも
 「SQLをばらしてJOINをC++で行えば速くなる場合がある」
ということは理解していただけれるかと思います。
また、
『SQLはオブジェクト指向言語の数十倍の効率』
というのは、単純に
 「OO言語側の最適化が不十分である可能性がある」
ということも言えるでしょう。

ただ、実験3では、高々十数%しか速くなっていません。
ということであれば、通常はやはり実験1のようなコードの方がトータル(開発効率と実行効率を考えると)としては良いと思われる。実験3のような事実はあくまでも知識としてしておきたいところです。

追記、コメント欄の議論を踏まえて再度記事をアップしました。
追記、コメント欄の指摘(ローカルマシンで動かしている)を受けまして再度環境を作成して実験しました。
追記、まとめ記事を作成しました。

2010-05-31 | コメント:14件



鳥こまち飯田橋店

私の知り合いの方で、最近焼き鳥屋をはじめた方がいらっしゃいまして、先日食べに行きました。
焼き鳥やさんはいろいろありますが、焼き加減やちょっとした味付けが他の店とはちょっと違い、久しぶりにおいしい焼き鳥を食べさせてもらいました。

鳥こまち飯田橋店
この不況下で、飲食店を始めるのはなかなか大変かと思いますが、
いろいろご苦労があるようで、以下にブログを書いていらっしゃいます。
飯田橋で焼鳥屋を開いた団塊世代のブログ
2010-05-02 | コメント:0件



とあるOOオタクの2010年オブジェクト指向の旅

@ITさんのエンジニアライフのコラム実はオブジェクト指向ってしっくりこないんです!(http://el.jibun.atmarkit.co.jp/minagawa/2010/04/post-ebc4.html)のコメント欄に参加しました。
件のブログですが、実名と思われる名前まで出しているのでご本人はすごく真面目なのでしょうが、こちらは、「この不景気な時代に大丈夫か?」と真面目にブログ主の去就を心配したりしました。
あまりにもな文章にしかなっていないのでせめてコメント欄はましにという感じで書き込みました。
ただ、大変失礼なのですが、件のブログ主、やはり文章力に少し難があるのか、ご自身に都合のよい発言とも受け取れたり、レベルの低い発言とも受け取れる発言をされたり、いささか困りものです。好意的にみますと、頑張って書いているようですので、温かく見守る必要もあるかもしれませんが、恐らく読んでいる人たちにとっては不快感が残るのでしょう。未だに炎上してますね。

もともと内容のないブログでしたが、コメント欄にていくつか興味深い話ができましたので、私自身オブジェクト指向の現状の把握ということで整理します。


■はじめに、オブジェクト指向プログラミングと先のブログへのツッコミ
皆様オブジェクト指向プログラミングと聞いて何を連想するでしょうか?
・カプセル化
・継承
・多態性
などが、一般的でしょう。
私としてはこれに、多重ディスパッチ(マルチメソッド - Wikiを参照、書籍ではMore Effective C++の項目31)を入れたい。
こういう話をすると、『デザインパターン』とかも出てくるでしょう。
さらに『Mixinはどうした』とか・・・もう話はつきなくなるでしょう。

さて、では皆様、これらの技法って何時覚えたでしょうか? 10年以上の経験のあるエンジニアの方の恐らく多くの方が、10年前には基本的な技法は最低限の知識として身に付けていたでしょう。私はstaticを何時覚えたのか、もう忘れた位に昔(おそらく16,7年程前の23,4才の時)になります。当たり前ですが、JavaやC#は、C++を源流にしています。staticの用法はJavaやC#でも変わりありません。

なので、得意げに
 『staticってつけるとインスタンス作る必要がないって知ってた?』
みたいなブログを読むと
 「で?」
と返したくなる。

さらに、
 『新人の奴にはstaticを教えないとダメだろ?』
みたいなことを言われると、
 「そんなことより、他にもっと重要な事があるのでは?」
とか、

はたまた、
 『他人のプログラムを引き継いだらインスタンスばかり作っていて苦労した』
とか言われると
 「いや、理解力が足りないだけでしょう。だって、この程度の文章しか(ry」
とツッコたくなる。

最後には、
 『だってRYOって奴はオレの言うこと理解したよ。だからお前も理解しろよ』
のようなことを言われると
 「ご自身の説明能力の低さを棚に上げて、人の名前を出さないでほしい!」
と怒りたくなる。

なにより、
 「貴方の小さい経験でオブジェクト指向プログラムをなぜ否定できるのか?
というのが皆様のご意見だと思われます。


先のブログ主に「どうせ書くなら以下のようなな文章を!」と言っておきましょう。これは、7年前に私が書いたJAVA PESS 28の記事の中の一つのコラムの原稿です。念の為に言いますと記事自体は真面目な初心者向けのもので、息抜きの為に中に1つこういった曲がったコラムを掲載しておりました(どうでもいいけど関西弁って読みにくいわ~)。

--------------------------------------------------------------------------------
**なぜオブジェクト指向プログラムなのか?**
 10年前はあんまり耳にせえへんかったけど,ほんまに今やオブジェクト指向真っ盛りで,開発現場でも「クラスがぁ,オブジェクトがぁ」にはじまり「ここはシングルトンで言うたやろ!」とか,猫も杓子もオブジェクト指向って言いだしよった.ワシの職場でもオブジェクト指向プログラミングが進出してきよって,今や「オブジェクト指向技術を持っていなければプログラマにあらず」という雰囲気になってもうた.
 そんな訳で,多くのプログラマにとって無視でけへんようになったオブジェクト指向やけど,そもそも「なんでオブジェクト指向なん?」という疑問が沸いてきよる.
結果を言うたら「他に選択肢がない」というのが本音やろうな.これは後ろ向きな発言やけど,殆どの現場の人間にとってはしゃーないからオブジェクト指向を使こうてるみたいや.
もうちっと前向きに言うたら「今のところそれが一番いいと思われておる」ということかな,ミソは「思われておる」というところで,オブジェクト指向プログラミングがホンマにほかの開発方法より優れてとるっていう証拠はあんまり聞かへん.確かに「我がプロジェクトではオブジェクト指向開発をする事により30%開発期間を短縮できた」などという記事をたま~に見るけど,それ以上に現場のもんは,「難しぃ・解らへん・ついて行かれへん」と言いよる.
 「クラス」は,確かにオブジェクト指向独特の機能で,これを使こうたら複雑なプログラムもシンプルになることもある.せやけどクラスの使い方はいっぱいあって,細かいことになると専門家でも意見が分かれよる.古いところでは「多重継承の使用はいかん!」とか言ってみたり,最近では「継承そのものがいかん!」とか言い出すしまつや.一体どないせえちゅうねん.それを間に受けた現場のもんは「あーでもないこーでもない」ってそこらで議論しよる.そんなヒマあったら仕事上げろっちゅうねん.
まぁ,後10年もしたら,誰も「オブジェクト指向」って言わんようになるやろな.
--------------------------------------------------------------------------------

件のブログ主は、デビューまなしで、いきなり色々批判を受けて正直驚いたと思うが、ある程度の年齢のエンジニアは上記のような文章を過去に1万回程読んでいるので、ブログのような文章をみると本当に『時間を返せ!』という怒りがこみ上げると思われる。


■ネットバブル時代のオブジェクト指向の思い出
 若手の方はご存じないかと思いますが、おじさん連中にオブジェクト指向が否定的に受け取られることの一つに、『今から10年程前のネットバブルの時代に、破たんしたWEBアプリケーションのプロジェクトによくJAVAが使われていた』というのがあるでしょう。
もちろん全てのJAVAのプロジェクトが破たんした訳ではないですし、PHPのプロジェクトで破たんしたものもあるでしょう。が私の周りでは破たんしたプロジェクトとJAVAって結構相性が良かったりしてました。
このように書くと此処のコメント欄が炎上しそうですが、当時はJAVAのWEBアプリケーションというと結構なチャレンジだったと記憶してますです。もちろんその責任は、オブジェクト指向の考え方ではなく、充分な経験を積んだり勉強を行わないでWEBアプリケーションを作ったりした未熟な技術者にあったでしょう。人を憎んでOOPを憎まず(ってか)。

■オブジェクト指向プログラミングの現状について(私見)
 さて、先のコラムは7年前に書いたものですが、最近ではオブジェクト指向って案外普及しているなぁというのが正直な感想です。
私の最近の経験を書きますと一番印象に残っているのは、RoR(Ruby on Rails)でのWEBアプリケーションがあります。このプロジェクト自体は業務システムではなかったので件のブログのコメントではコメントしませんでしたが、これのプロジェクトが3年前になります。
ソースレビューも行われたりしたのですが、継承なんぞもみなさん普通にしてましたし、また、最近関わった他のプロジェクトでも普通に仮想関数が使われていて、まぁ隔世の感があります。

もちろん非オブジェクト指向のプロジェクトも今だにやっていますし、全てのエンジニアがオブジェクト指向を使いこなしているとはいえないのも事実です。が、先のブログのコメントに「空気のようにC++を使う」とか言われる人もいて、私も血が騒いだりします。ちなみにとりすけさん、本当にありがとうございました。私にとっては良い経験談を聞かせて頂いたのですが、あのブログのコメントではとりすけさんのせっかくの経験がものすごく汚された感がぬぐえませんです。すんません。

■それぞれ立場によるオブジェクト指向プログラムの現状
件のブログ主は、『実践ではオブジェクト指向は使えない』とおしゃっていましたが、このような発言の背景には、どうやら以下の事があるでしょう。以下、OOPはオブジェクト指向プログラムの略です。

・OOPをしなくてもおおよそ開発できる
 こういう言い方は嫌いな人もいらっしゃるでしょうが、CPUにとってはOOP言語も非OOP言語も同じになります。つまりほとんどの開発は非OOP言語でも出来ます。

・OOPが必要な程複雑なプログラムを作成していない
 件の主は、C#で作ったDBのサンプルコードをそのままコピペしてきたような開発の話をされいるようです(あくまでも私が受け取った印象です)。つまりライブラリの範囲内でプログラムをされているということと受け取りました。

・OOPを理解する技術力が足りない
 どんな道具でも使いこなせる人もいれば使いこなせない人もいるでしょう。

・OOPを使いこなすプログラミング経験がない
 OOPはやはり経験が必要だと思っています。ちなみに私の場合は解った気になるまで3年ほどかかりました。

・OOPするまでに業務知識がない
 件のコメントで私は『法人税の知識がある』と言いましたが、これはパッケージソフトが使える程度の知識です。比喩的に言いますとWindowsが使える程度の知識になります。ではWindowsを作るほどの知識となると実はこれはこれで大変だというのはお分かりだとおもいます。オブジェクト指向の教科書に動物の例があるのは背景の業務知識を動物の例に置きかえないと説明ができないからです。もちろん書籍等を書くにあたっては上手い題材を考える必要があるでしょう。ちなみに私がJAVA PRESSを書いた時は五目並べを題材にしました。

で、業務システムではなぜオブジェクト指向が流行っていないのか?
ということですが、これはSEの立ち位置によっても違うでしょう。概ね以下の理由になるのでは?と思います。

つまり、社内SEさんの傾向として(あくまでも傾向としてですが)

・OOPを使いこなすプログラミング経験がない

ことだと思います。社内SEさんはプログラミングだけでなく様々な業務があります。OOPをやる時間もあまり取れないでしょう。

で、次に受託開発を行うSEさんの傾向として

・OOPするまでに業務知識がない

となるかと思います。もちろん開発業務にあたっては業務知識は必要ですが、つまり設計する程業務知識を把握しているかどうかになります。受託開発では、概ね
 (1)仕様の決定
 (2)開発
という段取りを踏み、(1)仕様の決定では、いかにユーザさんのノウハウ(業務知識)を引き出すかがその後の修羅場を避けるうえでの重要な点になるでしょう。つまり、裏を返せば、開発初期段階では充分に分析出来るほど、業務知識が身についていないということになります。

受託開発での業務システム開発の範囲では、これらのジレンマがあって事例が少ないのでは?というのが私の意見でした。また件のブログでは、パッケージソフトを開発される方からご意見を頂き、確かにパッケージソフトの開発者ならOOPを取り入れやすいなと思いました。確かにパッケージソフトの開発であればこれらのジレンマは解消されるでしょう。

2010-05-02 | コメント:0件
Previous Page | Next Page