もうちょっと「艦これ」からSQLを考えてみる3
前回のパターンであれば、全体を(パフォーマンス・サーバの負荷・開発工数・メンテナンス性)を鑑みて、IF文と四則演算は、DBサーバより、APサーバ。APサーバより、クライアントにある程度キャッシュさせて、処理もクライアントで行った方が良いです。
そう判断するには、設計段階でSQLで処理したときどうなるか、SQLで処理しなかったときどうなるか。が正確に分かってないと判断できません。
例えば、「艦これ」からは随分と離れますが、あるテストの男子と女子、全体の平均と最高点・最低点を求めたいとする。(面倒なので性別は非正規化されているとする)
テーブルは以下の通り
成績テーブル
テストID
生徒番号
性別(1:男子、2:女子)
得点
1.SQLで処理する
SELECT テストID , AVG(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子平均 , AVG(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子平均 , AVG(得点) AS 全体平均 , MAX(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最高点 , MAX(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最高点 , MAX(得点) AS 全体最高点 , MIN(CASE WHEN 性別 = 1 THEN 得点 ELSE NULL END) AS 男子最低点 , MIN(CASE WHEN 性別 = 2 THEN 得点 ELSE NULL END) AS 女子最低点 , MIN(得点) AS 全体最低点 FROM 成績テーブル WHERE テストID = ? GROUP BY テストID
実行計画をとれば分かりますが、図の様に全ての集計が1回のループ内で処理されます。
2.単純な集計のみSQLで行う
SELECT テストID , AVG(得点) AS 平均 , MAX(得点) AS 最高点 , MIN(得点) AS 最低点 FROM 成績テーブル WHERE テストID = ? AND (性別 = ? OR ? = 0) GROUP BY テストID
上のSQLを0〜2までループするか、SQL文を動的生成し、全体を取得するときは
AND 性別 = ?
を付けない形もあるかと思いますが、こういうプログラムを書く人は多いでしょう。
この処理は、DBサーバ、APサーバ共に余分な処理が増えているだけで、最終的な処理時間も長くなりますから、メモリーを掴んでいる時間も長くなります。
1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。
3.全件送信してAPサーバで計算
集計関数が分からないという人は、以下の様な処理を選ぶかも知れません。
SELECT テストID ,性別 , 得点 FROM 成績テーブル WHERE テストID = ?
DBサーバの処理はIF文と四則演算が減っただけ(誤差)。その代わりに大量のデータ送信が行われます。当然、最終的な処理時間も長くなりますから、メモリーも長時間掴んだままになります。
こちらも、1.のSQLが掛けない人に配慮する以外にメリットはあり得ません。
ソースのキレイさで選んではいけない
APサーバ側のソースのキレイさで選んでしまう人にとっては、SQLを使いきる1.が一番違和感があって汚いと感じるでしょう。3.が一番キレイに感じるかも知れません。
全体の処理フローから考えれば、2.3.の
ネットワークを挟んでループする。
無駄なデータを転送して、APサーバで四則演算する。
という処理はあり得ないほど非効率ですから、もちろん、2.3.で「DBサーバの負荷が下がる」と考えるのはプロと呼べないレベルです。馬鹿のカスのと言うけど、それでは足りないほどお粗末なレベルです。
SQLでなくオブジェクト指向言語で書くプログラムをDBサーバに置くなら 2.3.の処理を書く馬鹿は少ないし、もし、いても設計担当からハズされるでしょう。
2.3.はシステムが複雑になっただけで、依存関係が強くなり、言語のスパゲティ(SQLとOO言語)になっている。DBサーバの負荷も、APサーバの負荷も減らず、とてもプロが設計したシステムとは思えません。
しかし、こんなお粗末なレベルのモノも現場で普通に存在します。それが平気なのは処理フローが全く見えてない、DBサーバをブラックボックスとして考えているのでしょう。ですから、SQLになれば突然、ほとんどの人が理解できなくなって、「2.3.の処理が正しい」と主張し出してしまう。SQLとオブジェクト指向言語が等価と考えてない(何度も繰り返しますが、SQLをコンパイルした後は等価)等価と考えてないということは、SQLを魔法か何かと勘違いしている訳です。
全体の処理フローを突き詰めて考えていけば、APサーバでDBサーバの処理を肩代わりして負荷を下げることができるのは、最後のソートと、IF文、四則演算しかありません。
ソート以外は誤差で、ほとんどの場合その誤差を減らすために返って処理量が増えますから、「RDBMSを使う以上、SQLで書くしかない」という結論になります。
※ ただ、SQLが苦手なシーケンシャルな処理は(ストアドプロシージャか)APサーバでやるべきですが。
分かってなくてもできるから良いか?
結論的には、業務システムで今のサーバなら気にしないでもいいと思う。下手糞とは思うけど、下手糞しか居ないんだから仕方ない。ハードが良くなりすぎて、業務上トラブルになるほどの負荷、パフォーマンスになることはほとんどないでしょう。取りあえず下手糞が設計しても動きます。今のサーバは止めようと思っても、そう簡単には止めるほどヘボいプログラムは作れない(苦笑)
まあ、スラスラSQLが書ければ工数も下がるのですが、できない馬鹿が多すぎてそうも言ってられないからね。
しかし、ソーシャル系はユーザの増加は予測困難ですから、「取りあえず動く」は拙い。
APサーバのプログラムとDBサーバのプログラム(SQL)を機能単位に同じ人に割り振っている現状の人員配置、開発手法では、全員にオブジェクト指向言語からSQLまで分かっていなければ、真っ当なものは出来ないということになります。
ぶっちゃけ、それだけ人員を集めるのは難しいでしょう。
ですから、APサーバのプログラムを担当する人、DBサーバのプログラムを担当する人を完全に分けるべき。ほとんどの人はできているつもりで、RDBMS(SQL)をブラックぼっくとして中途半端に扱っているのですから、本当にブラックボックスにしてしまえば良いわけです。
「馬鹿な考え休むに似たり」「馬鹿は考えるな」ってことです。
そのためにはSQLはストアドプロシージャでラップするのが一番良いのです。後からどうとでもできます。普段から「1.しかあり得ない」という人が「ストアドプロシージャなんて」というなら聞く意味はありますが、「2.や3.で何が悪い!」っていう馬鹿が「ストアドプロシージャなんて」という意見には何の価値もない。基礎も分かってない人間が応用部分のメリットが理解できるわけがないからね。(ここが悩ましいところ)
「艦これ」は、カタログスペックの1000分の1以下で止まっている訳ですから、どんな失敗が重なった設計なのかは私には見当もつかない。余程失敗が重ならないと、1000分の1が出せないってことはないでしょう。
WEBサーバ、APサーバが何台で運用されているかは分かりませんから、その台数によってはシステムは簡単に落ちるでしょうけど、DBサーバが原因になるのは、設計に余程の問題を抱えている(いた)はずで、それはSQLの無理解から来ているはずです。