Hatena::ブログ(Diary)

SQLer 生島勘富 の日記 このページをアンテナに追加 RSSフィード Twitter

2013-08-26

RDBMSを使う以上、SQLを使いこなさなければいけない

 何度も何度も言ってるけれど、「処理を分割した方がDBサーバの負荷が減る」と感じるのは勘違い。

http://d.hatena.ne.jp/Sikushima/20110809/

http://d.hatena.ne.jp/Sikushima/20110810/

http://d.hatena.ne.jp/Sikushima/20110811/

http://d.hatena.ne.jp/Sikushima/20110815/

落ちるのはピークしかない

 http://d.hatena.ne.jp/Sikushima/20110815/ にある通り、SQLでDBサーバでできることをAPサーバで行ってもDBサーバの全体の処理は減らない訳です。

 サーバに掛かる負荷というのはピークではなく面積で見なければいけないけれど、処理が減らない以上、DBサーバの1回の処理の面積は分割した方が大きくなる。

f:id:Sikushima:20130826170141p:image

 この状態でアクセスが輻輳すれば下のようになる。

f:id:Sikushima:20130826170140p:image

 SQLの処理を避ければ、DBサーバの負荷は高くなるわけです。

 もっと重要なことは、SQLで処理を行ったときのAPサーバの負荷に比べ、SQLを避けて処理したときのAPサーバの負荷は何倍も高くなります。

 ですから、APサーバが複数あったとしても、パンクするのは大抵APサーバの方でしょう。それで、「もし、DBサーバで処理してたら……」って言い訳してる場面を何度も見てきました。

 ところが、本来はDBサーバで処理していたら、APサーバがパンクする時期も随分ズラせてた訳です。(もちろん、Webサーバのパンクはどうにも出来ないけどね)

 すぐパンクするAPサーバをパンクさせないためにも、レスポンスを早くするためにも、DBサーバのパンクを防ぐためにも、SQLを使った方が良いわけですから、何度も言ってるけど、SQLを避けて得られるものは、「下手くそに合わせることができる」の一点しかないのです。

APサーバで行った方がよい処理もある

 とはいえ、もちろん例外は存在する。

 例えばこれとか。元々、メモリー上で処理が完結するようなモノを、わざわざDBサーバを使う必要がないのは当たり前。

 もう一つ、パターンがあって、前回の勉強会で出た問題。

f:id:Sikushima:20130629183215j:image

というテーブルから、以下の様に一番多い連勝と連敗の数を出力する。

f:id:Sikushima:20130629183137j:image

 何となくできそうな気はするんだけれど、一晩考えて(といっても徹夜するわけではなく夢の中で整理するんですが……)やっぱり答えが出なかった。

 勉強会で出た答えはこれ。

SELECT   勝敗値,
         max(cnt)
FROM     (SELECT   勝敗値,
                   count(*) AS cnt
          FROM     (SELECT 勝敗値,
                           row_number() OVER (ORDER BY 日付) - row_number() OVER (PARTITION BY 勝敗値 ORDER BY 日付) AS cnt
                    FROM   試合結果 AS s) AS a
          GROUP BY 勝敗値, cnt) AS a
GROUP BY 勝敗値
ORDER BY 勝敗値;

 まあ、正しいのですが応用は効かないし、どう考えてもAPサーバでやった方が効率的。

 再帰SQLでやるこちらはすぐに思いついたけれど、内部的にレコード数回SQLを発行するというとんでもないSQLで、こちらも、私としては答えとは認められない。

WITH
m(ID, 日付, 勝敗値)
AS(
	SELECT 
		ROW_NUMBER()OVER(ORDER BY 日付), 日付, 勝敗値
	FROM 試合結果
)
, cte(ID ,開始日付, 日付, 勝敗値, 連勝数, 連敗数)
AS(
	SELECT
		ID 
		, 日付 AS 開始日付
		, 日付
		, 勝敗値
		, CASE WHEN 勝敗値 = 1 THEN 1 ELSE 0 END AS 連勝数
		, CASE WHEN 勝敗値 = 0 THEN 1 ELSE 0 END AS 連敗数
	FROM m 
	WHERE ID = 1
 	UNION ALL
	SELECT 
		m.ID
		, CASE WHEN cte.勝敗値 = m.勝敗値 THEN cte.開始日付 ELSE m.日付 END AS 開始日付
		, m.日付
		, m.勝敗値
		, CASE WHEN m.勝敗値 = 1 THEN cte.連勝数 + 1 ELSE 0 END AS 連勝数
		, CASE WHEN cte.勝敗値 = 0 THEN cte.連敗数 + 1 ELSE 0 END AS 連敗数
	FROM m INNER JOIN cte ON m.ID = cte.ID + 1
)
SELECT * FROM cte; -- 後は好きにサマリーを取る

 要するに RDBMS はシーケンシャルにデータを保たない。という考えから、シーケンシャルな処理を非常に苦手にしている。

    # 分析関数でLAGなどを使えばできるけどSQLServerは未実装なので……。

 このような処理は、必要なデータを転送して素直にAPサーバでやるか、ストアドプロシージャを組んだ方がよろしい。

 何でも例外はあるけれど、それはあくまで例外であって基本は理解するべき。

明日、勉強会の席が空いてます!

 まあ、何が言いたかったかというと宣伝。

 明日、勉強会の席が空いていますので、お時間がある方はいらっしゃってください。

http://sqlworld.org/event/20130827/

SQLWorld★大阪#16 開催情報

【日時】

2013年8月27日(火曜日) 19:00~21:00

【イベント概要】

SQLWorld 3回目の平日夜開催〜。今回も、みんなで SQL を書いてみようというハンズオン企画です!ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。

【会場】

フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/

〒530-0001 大阪府大阪市北区梅田 2-4-9 ブリーゼタワー 12F

http://info.fenrir-inc.com/jp/profile/overview.html

JR大阪駅、JR北新地駅、地下鉄梅田駅・東梅田駅・西梅田駅と各駅からアクセス可能です

【参加費】

無料

【持ち物】

パソコン/タブレット (DB のインストールは不要です。)

【参加可能人数】

13 人

【Twitterハッシュタグ】

#sqlworld