勉強会
立て続けに勉強会でお話しさせて頂きます。
SQLWorld★大阪#7 2011/10/15(土) 13:00 to 17:00
http://club-windows7.net/
「ストアドを使った開発」
第12回関西IT勉強宴会 上流工程勉強会
2011/10/28(金) 19:00 to 22:00
http://atnd.org/events/20842
「データベース設計はプログラミングの後に行う」
同じ話をプログラマ向けと上流向けにアレンジして行います。
よろしければ是非ご参加下さい。
どこで切るか？
『全部ストアドプロシージャでしましょう』
というと、どこで切るのか？という質問がよく来る。
答えは「切らない」基本はDBアクセスが必要なユーザアクションに対し１つのストアドプロシージャ。データがDBにある以上、DB内で必ず処理が完結できます。
単純チェックまでがUI側。関連チェックからはストアドプロシージャの仕事です。
次の反応は「そんなことをしたら負荷が……」なるのですが、何度も書いている通り逆です。簡単なSQLだろうと、ユーザアクションに対し何度もDBにアクセスしたらそれだけ負荷が掛かる。
それが怖いと感じるのは、結局、SQLを理解できてないからです。
怖さが取れたら何の問題もありません。ダミーのストアドプロシージャを使えば、UI側の言語から見れば、ほぼすべてがダンプの出力と同じになり、それをどのように見せるかのプログラムになります。ダンプをマッピングすればUIができます。今のIDEやフレームワーク、O/Rマッパを使えば、ほとんどコーディングレスに近いでしょう。
割合を求める処理を考えてみる
http://d.hatena.ne.jp/Sikushima/20111008/1318037160 にある例をもう少し細かく見ていきましょう。
事前にサマリーデータを作っておくパターンを除いて（もちろん、BIツールを使うというパターンもなしね）ざっくりと、７パターンある。
１．全件取得して全部別言語で（割愛）
２．割合は別言語
--★ 顧客毎・商品毎の集計
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
GROUP BY
h.顧客ID, m.商品ID;
３．別言語で（ハッシュ）ジョイン
--★ 顧客毎・商品毎の集計
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
GROUP BY
h.顧客ID, m.商品ID;--★ 商品毎の集計
SELECT 商品ID, SUM(単価 * 数量) AS 売上合計 FROM 売上明細 GROUP BY 商品ID;
４．割合はネスティッドループで
--★ 顧客毎・商品毎の集計
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
GROUP BY
h.顧客ID, m.商品ID;--★ ループの中で
SELECT SUM(単価 * 数量) FROM 売上明細 WHERE 商品ID = ?
５．サブクエリーで計算Ａ
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
, SUM(m.単価 * m.数量)
/ (SELECT SUM(単価 * 数量) FROM 売上明細 sub
WHERE sub.商品ID = m.商品ID) AS 売上割合
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
GROUP BY
h.顧客ID, m.商品ID;
６．サブクエリーで計算Ｂ
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
, SUM(m.単価 * m.数量) / s.全体合計 AS 売上割合
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
INNER JOIN
(SELECT 商品ID, SUM(単価 * 数量) AS 売上合計
FROM 売上明細
GROUP BY 商品ID) s
ON m.商品ID = s.商品ID
GROUP BY
h.顧客ID, m.商品ID;
７．OLAP関数で計算
SELECT
h.顧客ID, m.商品ID
, SUM(m.単価 * m.数量) AS 売上合計
, SUM(m.単価 * m.数量)
/ SUM(m.単価 * m.数量) OVER(PARTITION BY m.商品ID) AS 売上割合
FROM
売上ヘッダ h
INNER JOIN 売上明細 m
ON h.売上ID = m.売上ID
GROUP BY
h.顧客ID, m.商品ID;
まあ、理論値として、SQLで書いた方が短くなって工数も少なくて済むのですが、それは手書きしていた場合の話で、上の様な単純なモノならSQLはツールが自動生成していることが多いでしょうから微妙なところ。
取りあえず、それは置いて個別に見ていくと、
１．（ジョインしないパターンも）RDBMSは完全にストアーするだけの箱。
２．SQLと別言語が渾然一体となった言語のスパゲッティ。
たまに見るけど、OOAでもDOAでも、SQLでもOO言語でもない複雑怪奇なもの。
３．サブクエリー禁止とか言われて仕方なくやるパターン。
４．これはあり得んだろう。センスがないのも甚だしいな。
顧客×商品（購入した種類）回SQLが飛び、売上明細への総アクセス回数は
売上明細×顧客数×平均購入商品種類／商品数 ぐらいになる。
５．４．をSQLでやっただけ……。
６．MySQLなら仕方ないね。
７．パフォーマンスも十分にでる。
許せるのは、１．３．６．７．ですが、オブジェクト指向っぽくというかツールを使う前提で考えると、Sumメソッドとか、Countメソッドとか予め用意されて、４．になるのでは？小さいのをこなしているとそれが当たり前になって、４．が一番しっくり来るんじゃないのかな？
実際BIツールでも４．５．のパターンが結構あって「ようこれ売りよるな〜」ってパンフレットを５回ぐらい見直すね……。
私の思考ルーチンでは２．４．５．は絶対に出て来ないのですが、本当によく見るパターンで、どんな風に考えたらこんなものが出てくるのか、正直分かっていません。よく見るから存在は知っているけど、そこに至る過程は理解不能なのです。
例えばこんなのも。
http://el.jibun.atmarkit.co.jp/g1sys/2009/01/sql-fcaf.html
そのサブクエリーは私の思考ルーチンでは絶対に出てこない。SQLに限らず、私は頭の中の小人に命令して、その命令した内容を言語に書き出して終わりなのですが、そんな無駄な処理をさせようとすると小人が暴動を起こして大変なことになる（笑）。
しかし、ベースまでをSQLとか、OO言語的な発想とか、文法から考えるとか、いずれかのパターンで普段から考えていると出てくるんでしょう。
SQLだけじゃなくコンピュータがよく分かってない人にとっては、３．は遅く感じるんじゃないかな？（なぜ、３が少ないのかも理解不能）それで、どうしても遅いから２．にしてスパゲッティのできあがりってパターンが多そうな気がする。
４．で作ってみて遅いから、２．にするのをパフォーマンスチューニングっていう人もいる……（苦笑）。４．から、６．７．にチューニングすることもあるけどね。
ツールを使っていて３．はかなり出にくい（私はあまり見た記憶がない）答えだと思う。
２．は言語のスパゲッティで、全面スパゲッティってのもよく見る。私はそれはメンテできないと思うし、４．→２．のチューニング（やり直し）が入る時点でツールを使っても工数はチャラ以下でしょう。
７．が流せるかどうか。
７．が許される処理かどうか、
SELECT 商品ID , SUM(単価 * m.数量) FROM 売上明細
WHERE 最大集計範囲 GROUP BY m.商品ID;
一番大きなテーブルが売上とすれば、ぶっちゃけてそのシステムの集計はこのSQLの２〜３倍は掛からない。
ですから、この単純なSQLのレスポンスの２〜３倍以上掛かる処理はチューニングポイントがあるし、そのレスポンスにユーザが耐えられないなら、どこかで非同期処理（バッチ処理）を挟む必要がある。
10分待ってくれる顧客もいれば、速いマシンも、大量のデータも存在するので、絶対値は何とも言えないが、日常的に４．を選んでいる人とは、非同期処理を選ぶポイントが１〜２桁違ってくる。非同期処理をどこに入れるかは、それでシステム全体のデザインが変わってくる、上流の最も重要な判断の一つですが、それが１〜２桁も違ったら仕事にならないでしょう。
というか、完全に何の見積もないまま、勘で非同期処理を入れたり、非同期処理が必要なところを入れなかったりする。そこで間違うと下流（後工程）はどうしようもない地獄が待っている。
上流は細かい言語は良いけれど、最低限SQLが高いレベルで（どこにバッチを挟むか判断できるレベル）できないとまずいです。
そんなお話は28日の方で。15日の方はいつものネタです。
よろしくお願いします。
