SQL を使ったプログラムの改善 -「Refactoring SQL Applications」を読みながら-

SQL を使ったプログラムの今昔 -「Refactoring SQL Applications」を読みながら-」の続きとなる「Refactoring SQL Applications」の Chapter 1 からの例です。

いかにも導入部らしく、“よくある”SQL アプリケーションをサンプルとし、それを改善していくとどういう効果があるか、が書かれています。


改善前の SQL アプリケーション

まず、最初に Java で SQL を実行しているアプリケーションがサンプルとして挙げられています。こちらがそのソースコードですね。(DB2用に書き直しています)

メインとなる処理は、まず最初に areaid を指定し

SELECT accountid 
  FROM area_accounts 
WHERE areaid = ?

を実行、
その結果得られた accountid 毎に

SELECT txid, amount, curr 
  FROM transactions 
WHERE accountid = ?
  AND txdate >= (? – 30 days)
ORDER BY txdate 

を実行、
その結果得られた txid 毎に curr をセットして

SELECT threshold 
  FROM thresholds 
 WHERE iso=?

を実行して閾値をチェック、
チェックして超えたものについて

SELECT (? * rate) 
  FROM currency_rates 
 WHERE iso=?
   AND rate_date = ?

でレートを計算して

INSERT INTO check_log
         (txid, conv_amount)
VALUES (?, ?)

で挿入、
を繰り返すようになっています。

RPG の CHAIN や READE に読み替えればそれこそ“よくある”プログラムですよね。
SQL でも、昔は複雑な SQL 文のコンパイル等の負荷が高く、逆にデータ件数はそれほど多くなかったため、こうした簡単な SQL をループするプログラミングをされたケースは少なくないと思います。
今はそれが「命とり」なんですよねぇ。

DB2 for IBM i での実行例

この本の Chapter 1 は、これを FirstExample として、いろいろ改善を行って SixthExample に至る過程を書いています。

インデックスの追加などは行わず、コードの改善だけで↓のような結果になりました。

ある一回の実行例に過ぎませんが、157,358 → 161,492 → 36,468 → 4,438 → 1,165 → 1,224 と一番速いものを 1 とした場合、130倍近くもの差が生じています。
130倍速いハードウェアを買う、ということを考えたら、「プログラムに手を入れるのはちょっと…」なんて言ってられないのでは?!

FirstExample に比較して SecondExample がちょっと遅いのと、FifthExample と SixthExample との差は実行時の誤差と見るのが妥当だと思います。

C/S 型で実行すると

PC 上で C/S 型で実行するとさらに差が開きます。一番速いものを 1 とすると、実に 16,000 倍にもなります。どうでしょうか?!(ちなみにこちらはインデックスを追加した状態で実行しています)

FourthExample あたりから逆転しているのは、Java自体の実行速度がPCの方が速いから、でしょう。
C/S型では通信のやり取りがある分、SQLの実行回数の影響を受けやすいわけですね。

かかっている時間をグラフにしてみました。Y軸の値が高いほど、処理時間がかかっているわけです。

C/S 型の FirstExample と SecondExample があまりに遅いので ThirdExample 以降の差がよくわかりませんね。

ThirdExample 以降を取り出してグラフ化したものが↓です。

FifthExample と SixthExample が飛びぬけて速いことがわかりますね。

Host での実行と C/S型それぞれで、一番速いものを 1 とした実行時間の比を出してみました。

FirstExample と SecondExample は SixthExample の約 130倍もの時間がかかっています。

FirstExample と SecondExample は SixthExample の約 1万 6千倍近くもの時間がかかっています。

それぞれのデータベース・エンジン側での実行状況を見ていきましょう。

FirstExample

FirstExample 実行時の実行状況です。(インデックスなしでホストでの実行時のものです。以下同じ)

かなり、実行回数の多い SQL はあるが、「まあこんなものでは?」というのがたいていの DB チューニング担当者の感想なのでは、と思いますが…

SecondExample 実行時

FirstExample からのプログラムの変更点は二つあります。

ひとつは、ループ内で大量に実行されている SELECT 文から不要な ORDER BY をなくしてしまうこと、です。

Accountid 毎に txid を取ってくる SQL について、得られた結果セットは後続処理でいずれにせよ全件読んで処理しています。ORDER BY txdate が指定されていますが、後続処理は全件アトミックに処理してしまっているので、どういう順序で並んでいようが何の影響もありません。
元の SQL ではたまたま ORDER BY どおりの指定に従って並んでいた場合を除いてわざわざ並び替えを行ってしまいます。件数が多くなってきた場合はその負荷も無視できないほどになる可能性もあります。

もうひとつは、やはりループ内で実行している INSERT 文が毎回 COMMIT する設定になっているのですが、それを止めること、ですね。

INSERT に対しては AUTOCOMMIT がデフォルトでオンになっています。が、それぞれの INSERT ごとに COMMIT する必要はありませんね。INSERT が全部終わったところで COMMIT、の方がリカバリーの観点からも望ましいでしょう。

C/S では速くはなっていますが、HOST での実行ではかえって遅くなっています。↑のデータベース側での実行状況を見ても、それほど大きな差はないので、この「かえって遅くなっている」のは誤差によるバラつきと考えていいと思いますが、いずれにしてもあまり大きな効果はない、といってよさそうです。
これ、もともとデータベースエンジン側でバッファリングがうまくできていれば、それほど大きな効果はないはずのものなんですね。
実際、この本での実験例でも Oracle や SQL Server では1.2x程度のパフォーマンスアップにとどまっています。
ただし、MySQL では 3x ものパフォーマンスアップになっています。このあたりはやはり商用 DB との差になるのかもしれませんね。

ThirdExample 実行時

著者は、「簡単な SQL でも大量に繰り返されているもの」を処置することが得てして効果が大きい、という意味のことを言っています。

そこで、まず 176,951回も実行されている SQL(select threshold from thresholds where iso=?) を見てみます。

プログラムを見てみると、これは通貨毎の上限値を取得するだけの処理です。
さらに、このテーブルの内容を見てみると、実はデータが 20件しかありません。これほどテーブルへの問い合わせが行われるのは確かにあまり効率のよいやり方だとは考えられません。

なぜこんなに回数が多いかというと、結果セットの毎行に対してこの SQL で結果を確認しているようになっているからなんですね。SQL は一文毎にファイルのオープンをしに行きますので(RPG でいう“オープン“とは違います。相当軽いものですが)、実行回数が多いとそのオーバーヘッドも無視できません。
C/S の場合はさらに通信時間が入ってしまうので、影響は大きくなりますね。

通貨ごとの閾値は最初の一回だけ実行して hashmap に取っておく、
レートも日付と対象をチェックして最初の一回だけ実行して hashmap に取っておく、
というように変更することで、無駄な、重複した呼び出しを抑制するように変更したのが、この ThirdExample です。

SELECT threshold 
  FROM thresholds 
 WHERE iso=?

が 176,951回から 170回に、

SELECT (? * rate) 
  FROM currency_rates 
 WHERE iso=?
   AND rate_date = ?

SELECT rate 
  FROM currency_rates 
 WHERE iso=?
   AND rate_date = ?

に変更されていますが、343回が1回に、それぞれ激減しています。
「合計処理時間」も大きく減っているのがわかりますね。

176,951回も実行されている SQL と 343回実行されている SQL がそれぞれ一回になると、通信時間の影響がほとんどないはずの IBM i 上での実行でさえ実際に実行時間が一桁下がります。C/S 型で三桁も下がります。(この「三桁」にはインデックスを有効利用できているという効果もあります。つまりインデックス作成を併用するとさらに効果が上がる、というわけです)
両方とも「最も高価な時間」を見ても 0.0050 や0.0000 と表示されているもので、「チューニング」などといったときはおそらくまったく問題視はされないでしょう。メインの SQL については FirstExample からこの ThirdExample に至るまで、ORDER BY を取っただけ何もしていないので「最も高価な時間」も「合計処理時間」もほとんど変わっていません。誤差範囲の変動があるだけです。

著者の言うとおり、「簡単な SQL でも大量に繰り返されているもの」を処置するだけでこれだけの効果があるわけです。

FourthExample 実行時

255回呼ばれている SQL ステートメントをまとめて1つにしたのが FourthExample になります。

具体的に言うと、

255 x (select txid,amount,curr from transactions where accountid=?" and txdate >= (cast(? as timestamp) - 30 days)");)

1 x (select txid,amount,curr from transactions where accountid in (select accountid from area_accounts where areaid = ?) and txdate >= (cast(? as timestamp) - 30 days)");)

になっています。

上の SQL ステートメントは「最も高価な時間」で 0.2759 で下のものは 2.0878、と処理時間そのものはかえって多くかかるようになっているのですが、実行される回数の差から、「合計処理時間」は34.2107 → 2.0878 と大きく短くなっています。
その結果、HOST での実行は36468 が 4438 でかなりのパフォーマンスアップになっています。C/S 型の場合は、差があまり大きく見えませんが、5226 → 4228 と 20% のパフォーマンスアップになっています。

FifthExample 実行時

INSERT 文ひとつにまとめてしまったものですね。WHERE 句にもともとの SELECT を押し込んだようなかっこうのものです。

insert into check_log(txid,conv_amount)
select x.txid,x.amount*y.rate 
from(select a.txid, a.amount, a.curr
      from transactions a
      where a.accountid in
             (select accounted
              from area_accounts
              where areaid = ?)
        and a.txdate >= (cast(? as timestamp) - ? days)
        and exists (select ? from thresholds c
                    where c.iso = a.curr
                      and a.amount >= c.threshold)) x,
     currency_rates y
  where y.iso = x.curr  and y.rate_date=?

“昔“はこういう SQL は最適化にも実行にも時間がかかっていたのですが、やはりテクノロジーの進歩というのは凄いもので、FourthExample での SQL よりもさらに時間が短くなっています。

HOST での実行では4438 から 1165 と半分以下のスピードで処理が終了しています。

C/S 型ではさらに速くなっていますが、これは SQL をひとつにまとめたことで通信のやりとりが減ったことと、インデックスの効果ですね。

SixthExample 実行時

こちらもひとつの SQL にまとめるという考え方は同じです。
こちらは JOIN でまとめています。SQL では同じ結果を得るのに、複数の書き方ができる、ということの好例ですね。
だいたい Fifth と同じくらいの最適化/実行時間になっています。

insert into check_log(txid,conv_amount)
select x.txid,x.amount*y.rate 
from(select a.txid, a.amount, a.curr
      from transactions a
           inner join area_accounts b
                   on b.accountid = a.accountid
           inner join thresholds c
                   on c.iso = a.curr
      where b.areaid = ? and a.txdate >= (cast(? as timestamp) - ? days)
        and a.amount >= c.threshold) x
     inner join currency_rates y
             on y.iso = x.curr  where y.rate_date=?

HOST での実行では1165 から 1224 と FifthExample とだいたい同じような時間で処理が終了しています。
C/S 型ではさらに速くなっていますが、これはインデックスの存在によりさらにすすんだ最適化ができた効果ですね。

インデックスの効果

インデックスが存在する状態での IBM i 上での実行結果をいくつか紹介しましょう。

SecondExample

まず最初に SecondExample です。
インデックスがない状態での FirstExample よりは確かに処理時間が改善(157358 → 129350)されていますが、だいたい似たような改善度合いの印象ではないでしょうか。

SELECT txid, … が「最も高価な時間」が 0.2818 → 0.0028、「合計処理時間」が 37.1080 → 0.6634 と大きく減っているのがわかります。

FouthExample

↑での FouthExample と同等の状態( transaction テーブルに accountid をキーにしたインデックスを作成後)で、System i 上で実行してみた結果が↓になります。
インデックスなしとありで、ずいぶん実行時間が異なっている(4438 → 2900)のがわかりますね。

「最も高価な処理/合計処理時間」が 2.0878 → 0.4737、↑の SecondExample の「合計処理時間」と比較しても 0.6634 → 0.4737 と短くなっています。

FifthExample/SixthExample

インデックスを作成すると Fifth や Sixth はさらに速くなります。

FifthExample の実行時の状態です。一番時間がかかっているのは INSERT 文で、FourthExample の一番時間のかかっている SQL 文よりは長くなっているのですが、トータルの実行時間としては速くなっています。

SixthExample の実行時の状態です。FifthExample と同様な INSERT 文なのですが、こちらの方が速くなっています。

Sixth の方が速い理由は LPG

Fifth と Sixth、それぞれの Visual Explain を見てみると両方とも LPG が使用されていることがわかります。

こちらが FifthExample の Visual Explain の結果、

こちらが Sixth の Visual Explain の結果です。

緑色のところが LPG の適用範囲なので、Sixth の方がより広く適用されていることがわかりますね。

Fifth はいわば WHERE のネストですが、Sixth は JOIN です。どちらが使われるかといえば JOIN の方が使用頻度は高いでしょう。最適化する上で JOIN の方が効率がいいのは理解できます。

データの作成

データは GenerateData プログラムで作成しました。

[Top Pageに戻る]

Ads by TOK2