[ADP開発日誌]SQL(JOIN)の実行パフォーマンスについて2011


以前に書いたこの記事に関してコメントをもらいちょうど記事にしようかと思っていたところでしたので、ADPのキャッシュ機能を使い、この記事の実験をADPでやったらどうなるかみてみます。
 
SQLでjoin(結合)と言えばSQLに慣れた方にとっては馴染み深いものですが、初心者にとっては一種の登竜門のようで、joinを避けたコードを見かけたりすることがあります(まぁ私も十数年前にはこのような理由でjoinを避けたコードを書いた記憶があります)。また、O/Rマッパーではテーブル毎にクラスを対応させる関係で、joinの取扱がややこしかったりします。
それ以外でも、私の場合になりますが、過去にパフォーマンス上の理由からjoinを行わなかったことがあります。
今回は、前回の実験と同様に
・SQLでjoinさせる。
・ADPでjoinさせる。
でパフォーマンスの違いについていくつかの実験を行い計測します。

実験環境

JOINのパフォーマンス実験環境はこちらに記述しています。  

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

例により、SQLで素直にjoinさせてみます。以下のようなコードになります。
,$db = "DSN=Trade"
,$str = "SELECT Price.CODE, RDATE, OPEN, CLOSE, NAME FROM Price "
        "INNER JOIN Company ON (Price.CODE = Company.CODE)"
,sql@($db,$str,[]).csv.prtn,next;
 
少しコードの説明を、
1行目の、$db=~ の部分は、ODBCの接続文字列を指定します。上記のコードは、ODBCのデータソース名Tradeを指定している接続文字列になっています。
2,3行目の、$strの部分はSQL文を変数$strに代入しています。本来は1行で書けますが、wordpressで見やすいように2行で書いています。
4行目の
,sql@($db,$str,[]).csv.prtn,next;
sqlは組み込みの述語で、「ODBC-APIを使いsqlを実行し、結果を配列(@)で受け取り、csvに変換し、prtnで画面に出力し、nextで全ての結果を出力する」というコードになります。
自画自賛になりますが、必要最低限の情報だけで簡単にSQLが発行できているので、ADPの開発目標の一つである「SQLとの親和性が高い言語を目指す」を具現している例だと思います。
 
実行時間ですが、
D:\>adp -t sql_test_1.p > sql_test1.txt
time is 119192ms.
 
で、約119秒となりました。
 

実験2-A ADP側でjoin(ネステッドループ)

続いて、ADP側でネステッドループjoinさせてみましょう。
 
,$db = "DSN=Trade"
,$price = "SELECT CODE,RDATE,OPEN,CLOSE FROM Price"
,$company = "SELECT NAME FROM Company WHERE CODE = ?"
,sql( $db, $price, [], @rec)
 ,sql( $db,$company, [$rec[0]], $name)
  ,csv($rec,$name).prtn,next;
 
ADPのDBライブラリは、前に紹介しましたODBCライブラリがベースになっていますので、ODBCのパラメータクエリが使えます。
5行目のコードがパラメータクエリを使っています。

実行時間ですが、
D:\>adp -t sql_test_2.p > sql_test2.txt
time is 1717284ms.
 
で、約1717秒となりました。実験1と比べて約14倍の実行時間です。
 

実験2-B ADP側でjoin(ネステッドループ&キャッシュ)

さらに続いて、ネステッドループjoinをADPのキャッシュ機能を使って高速化をはかります。
 
,$db = "DSN=Trade"
,$price = "SELECT CODE,RDATE,OPEN,CLOSE FROM Price"
,$company = "SELECT NAME FROM Company WHERE CODE = ?"
,sql( $db, $price, [], @rec)
 ,sql$( $db,$company, [$rec[0]], $name)
  ,csv($rec,$name).prtn,next;
 
呼び出し述語名の後ろに$をつければキャッシュ機能がONになります。上記のコードでは5行目の sql$ がキャッシュ機能を使用しています。
では、実行時間をみてみましょう。
 
D:\>adp -t sql_test_2.p > sql_test2.txt
time is 116770ms.
 
で、約117秒となりました。
実験2-Aと比べるとかなり高速化がはかられたかと思います。キャッシュのこのような使い方は、かなり有効だとうことが解るかと思います。繰り返しになりますが、ADPならお手軽にキャッシュ機能を使うことができます。

実験3 ADP側でjoin(事前にマップ作成)

ちなみに、ADPでも事前にマップを作成し、joinを行うことができます。
以下、コード例です。
,$db = "DSN=Trade"
,@tbl = {}
,sql($db, "SELECT CODE,NAME FROM Company",[], @r)
 ,@tbl = @tbl + [ $r["CODE"] | $r["NAME"] ]
 ,next
,sql($db, "SELECT CODE,RDATE,OPEN,CLOSE FROM Price",[],@rec)
 ,$key == $rec["CODE"].str
 ,csv($rec,$tbl[$key]).printn,next;
 
前回の記事ではC++でハッシュjoinを行うと書いたので『ハッシュJOINを言語で再開発するのは非効率』とコメントをもらいました。
コードを良く読んで頂ければ解るかと思いますが、実はC++の例でもjoin自体はプログラミング言語(ライブラリ)の機能を使っており、取り立てて複雑なことはしていません。 
やっていることを説明しますと、マスターテーブル用のマップを事前に作成し、それを使ってjoinを行っています。慣れていない人にとっては難しいかもしれませんが、古くはperlの連想記憶、最近(これも古いが)の例ではVBScriptのディクショナリに相当します。DBMSを使わないで日常的にファイル処理を行っている方にとっては日常的なコードかと思います。
 
ちなみに、ADPのコード例ですが非常にすっきりとしているかと思います。C++の例と比べると本来やろうとしていることが明確になっているかと思います。
実行時間は、
 
D:\>adp -t sql_test_3.p > test3.txt
time is 110988ms.
 
で、約111秒とやはり実験1より速くなっていることが解ります。
こうしてみると、実験2-Bが思いのほか速くなっていないと思わるでしょう。
これはSQLの実行回数に関係しています。
 
各実験のSQLの実行回数を見てみましょう。
SQLの実行回数
実験11回
実験2-A約470万回(Priceテーブルの行数+1)
実験2-B約2000回(Companyテーブルの行数+1)
実験32回
 
になります。実験2のコードではテーブルの行数に比例した数だけSQLを実行することになります。実験2-Bが実験2-Aより速いのは、Priceテーブルの行数よりComapnyテーブルの行数が圧倒的に少ないから、つまり1対nの結合を行っているからで、仮に1対1の結合では速くならないということになります。
 
実験3がなぜ実験1より速いかですが、DBMS側から転送されるデータ量が違います。
以下、CSVファイルの先頭5行を表示します。
 
1717,2005-05-10 00:00:00.000,21251,3522,明豊ファシリティワークス(株)
1717,2005-05-11 00:00:00.000,21251,3522,明豊ファシリティワークス(株)
1717,2005-05-12 00:00:00.000,21251,3522,明豊ファシリティワークス(株)
1717,2005-05-13 00:00:00.000,21251,3522,明豊ファシリティワークス(株)
1717,2005-05-16 00:00:00.000,21251,3522,明豊ファシリティワークス(株)
 
企業名の『明豊ファシリティワークス(株)』が重複して余分なデータとなっています。実験1のコードではDBMSから言語側にこのように重複したデータが来ます。各実験で転送されるデータ量を見てみましょう。
 
結果データの転送量(CSVファイルベース)
実験1約256MB
実験2-A約256MB
実験2-B約184MB
実験3約184MB
 
実は、DBMSから言語側へ転送されるデータ量自体は、実験1より実験2-Bの方が少なくなります。そのような関係で、実験1より実験2の方が早くなっています。SQLの実行回数(実験1の方がよい)とデータ転送量(実験2の方がよい)になりますが、このあたりはハードウェアの環境やDBMSによって結果が変わってくるでしょう。
この2つのデータから実験3は、なるべく少ないSQLの実行回数で少ないデータ量を転送しているということが解るかと思います。

追記:コメント欄での指摘およびテスト再現性を考慮してテスト環境を整備して再度計測しています。
2011-02-01 | コメント:0件

コメントをどうぞ


『不適切なコメントへの対応』を一読下さい。
現在コメントは承認制となっております。

Previous Page | Next Page