＠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++で書き実際に実行させてその実行時間をみてみましょう。
■実験の環境
今回テストを行いました環境は以下のとおりです。
◆マシン
・CPU Core i7-920
・メモリ 6GB（DDR3-1066 2GB × 3）
・OS Windows 7 Ulitimate (x64)
・DBMS SQL Server 2005 SP3 (x64) Developer Edition
・使用言語
Visual Studio 2008 Professional （C++)、
DBアクセス部分はODBC（以前紹介したラッパクラスを使用）
ODBCドライバは、SQL Native Clientを使用
32ビットモード、マルチバイトコードでコンパイル
◆テスト用データベース
株価情報DBを模したもの
以前趣味で収集したものから価格情報を乱数化しました。
なのでデータ自体は実用性はありません。
・データベース名：Trade
・テーブル定義：
価格（Price）テーブル
＊レコード件数は、4,671,568件
CREATE TABLE [dbo].[Price](
[ID] [int] NOT NULL,
[CODE] [nvarchar](10) NULL, — 会社コード
[RDate] [datetime] NULL, — 日付
[OPEN] [int] NULL, — 始値
[CLOSE] [int] NULL — 終値
) ON [PRIMARY]
会社情報テーブル
＊レコード件数は、1950件になります。
＊ CODEに一意のインデックスを張っています。
CREATE TABLE [dbo].[Company](
[ID] [int] NOT NULL,
[CODE] [nvarchar](10) NULL, — 会社コード
[NAME] [nvarchar](50) NULL, — 会社名
[NumberOfIssued] [float] NULL, — 発行済み株式数
[ClosingDate] [nvarchar](50) NULL, — 決算月
[UNIT] [int] NULL — 単位株数
) ON [PRIMARY]
■ダウンロード
データベースのバックアップファイル（ZIP圧縮 SQL Server 2005）
今回作成したプロジェクトファイル（ZIP圧縮 Visual Studio 2008）
■実験する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)
■実験１ 素直にSQL側でJOINをさせたものを実行
以下のコードのとおり、実験するSQLをそのまま実行してみました。
#include
#include
#include "../kz_odbc.h"
using namespace std;
int main(void)
{
kz_odbc db("DRIVER={SQL Native Client};SERVER=.;"
"Trusted_Connection=yes;Database=Trade;"
"MARS_Connection=yes",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 57sec. Record count is 4671568.
となりました。プログラムは標準出力に出力していますが、実行に際しては標準出力をファイルにリダイレクトしています。その方が実行速度は速くなります。
比較元のデータが無いので何とも言えませんが、1秒間に約8万件のデータがCSVファイルへ落とされているのでまずまずでしょう。
ちなみに、実行ブランを確認しましたが、ハッシュJOINを行っていました。
■実験２ C++側でネステッドループでJOINさせてみる
ループループといっていたものですが、いわゆるネステッドスープのことだと推測します。
#include
#include
#include "../kz_odbc.h"
using namespace std;
int main(void)
{
kz_odbc db("DRIVER={SQL Native Client};SERVER=.;"
"Trusted_Connection=yes;Database=Trade;"
"MARS_Connection=yes",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 940sec. Record count is 4671568.
これはものすごく遅いですね。生島さんが、
『SQLにすると数十倍速くなる』
といっていたのは、実験１のコードと実験２のコードを比べて言っていたと思われます。
では、これ以上に速くさせる方法はないのでしょうか？
生島さんの言うとおり、OO言語はSQLと比べて何十倍も遅いのでしょうか？
■実験３ C++側でハッシュJOINさせてみる
件のコメント欄で生島さんが難しいとおっしゃっていた、ハッシュJOINですが、実は特段、難しいものではありません。
以下のようにすっきりと実装できます。
ちなみにコード中に出てきますmapというのはバイナリサーチを行います。なので、正確にはハッシュJOINではありません。
C++でハッシュ検索を行うには、Boost等のライブラリを使う必要があります。
つまり今回のコードはある意味、最適化の余地を残しているのですが、ここではテストの再現性（環境設定）の手間を考えてmapを使います。
#include
#include
#include "../kz_odbc.h"
using namespace std;
int main(void)
{
kz_odbc db("DRIVER={SQL Native Client};SERVER=.;"
"Trusted_Connection=yes;Database=Trade;"
"MARS_Connection=yes",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;
}
結果ですが、以下のとおり、実験１のコードよりも早くなっております。
Execute time is 55sec. Record count is 4671568.
■結果
実行結果を再度以下に掲載します。
| 実験１（SQL) | 57秒 |
| 実験２（C++側でネステッドループ） | 940秒 |
| 実験３（C++側でハッシュ） | 55秒 |
明確に結果が出ているかと思います。こんなに単純なテストの結果からでも
「SQLをばらしてJOINをC++で行えば速くなる場合がある」
ということは理解していただけれるかと思います。
また、
『SQLはオブジェクト指向言語の数十倍の効率』
というのは、単純に
「OO言語側の最適化が不十分である可能性がある」
ということも言えるでしょう。
ただ、実験３では、3,4%しか速くなっていません。
ということであれば、通常はやはり実験１のようなコードの方がトータル（開発効率と実行効率を考えると）としては良いと思われる。実験３のような事実はあくまでも知識としてしておきたいところです。
当初、@ITのエンジニアライフへコメンテーターとして登録して、この記事を載せいようかと思っていましたが、運営のガイドラインが変わるとかで何やら面倒そうなのでまずはこちらに掲載しておきます。
では、また。