SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

ストアドプロシージャでシステムを構築するとDBサーバの負荷が増えるか

システム設計 SQL概論

　結論から書くとストアドプロシージャでシステムを構築するとDBサーバの負荷は減ります。WEBシステムと仮定してDBサーバとAPサーバの関係で書きますが、C/Sも同じになるので、C/Sで考える人は、APサーバをクライアントと置き換えて読んでください。

その理由は単純

　極めて単純な話です。APサーバで処理しても、DBサーバで処理しても結果は同じになります。

　つまり、システム全体で最低限行わなければならない処理量は同じなわけです。APサーバで処理してDBサーバの処理が減るならば、APサーバがDBサーバの処理を肩代わりしなければいけません。

　APサーバが肩代わりできる処理
　　　　　・四則演算
　　　　　・ソート処理
　　　　　・マスタ類のキャッシュ（すればの話）

　APサーバが肩代わりさせるために増える処理
　　　　　・SQL文を大量に受け取るネットワークのコスト（AP/DB双方）
　　　　　・SQL文を実行するためのオーバーヘッドの繰り返し
　　　　　・APサーバが処理するための（最終結果には不要なデータの転送）
　　　　　・APサーバが処理中に利用するサーバカーソル用のメモリー
　　　　　　　　　などなど

サーバの負荷は 使用量×使用時間 ≠ ピークの使用量

　ストアドプロシージャで行うと、DBサーバのリソース使用量のピークは高くなります。しかし、APサーバで処理を行う場合に比べ短い時間で終わります。

図1 ストアドプロシージャ（DBサーバ）で処理とAPサーバで処理したときの差

　単純化していますが、棒グラフの面積（体積）の合計がリソースの使用量です。ストアドプロシージャを使ってDBサーバで処理した場合の方が小さくなります。

　結果として、ストアドプロシージャを使ってDBサーバで処理した方が、DBサーバの負荷も、APサーバの負荷も減ります。複数ユーザであっても、処理が短ければ短いほど重なりにくくなるので安全なのですが、特にCPUなど目に見えやすい負荷が、ストアドプロシージャを使ってDBサーバで処理したときに高くなりますから、そのピークを見て怖くなる人は多いです。しかし、大きな勘違いです。

　シングルポイントのDBサーバにできる限り処理させたくないから、DBサーバに処理をさせるストアドプロシージャは使わない。というのは、技術的になんの根拠もないというか、完全に間違った意見です。

　マスタをAPサーバにキャッシュするのも悪くはないのですが、費用対効果として考えれば、余程、トラフィックが多いシステムでないと効果はないと思います。（APサーバのパンクを早めるだけの可能性もある）

ストアドプロシージャは調査がし易い

　SQLはトレースを採ることができますし、単純にはSQL文のキャッシュを見ることで直近の問題点を見ることができます。

　SQLServer
　　　　　sys.dm_exec_sql_text
　　　　　　　　　詳しくはこちら。

　Oracle
　　　　　SELECT * FROM v$sql
　　　　　　　　　詳しくはこちら。

　このキャッシュを見ることも RDBMS の理解を深めることに繋がるので、是非、実際にやって欲しい。処理の前にこのキャッシュを確認しているし（プリペアドステートメントが速い理由）処理が終わるたびに、結果（CPUやメモリーの使用量）をキャッシュに書き込んだりしている。つまり、オーバーヘッドが大きいということです。

　すべての処理がストアドプロシージャになっていれば、このキャッシュから、CPUをよく使う処理、ディスクリードをよくしている処理、メモリーを大量に使っている処理、などなどを簡単に抜き出すことができます。しかし、システムのパフォーマンスが悪い理由が、単純なSQLの繰り返し、つまり、SQLのオーバーヘッドにある場合には、DBサーバを見るだけでは分かりません。

　また、ストアドプロシージャを使わない場合、すべてのSQLは実行されるまではAPサーバから見ても、DBサーバから見ても、ただのプレーンなテキストです。SQL文内で使われているDBオブジェクトとの依存関係は簡単には分かりません。しかし、ストアドプロシージャなら、コンパイルされて保存されていますから、すべてのDBオブジェクトとの依存関係がはっきり分かります。

例）SI Object Browser のクロスリファレンス（守秘義務があるので実際のは出せない）

もちろん、当たり前と言えば当たり前ですが、ストアドプロシージャの中で動的SQLを使っていたら出ませんけどね。

ストアドプロシージャを使った開発のデメリット

　開発環境が脆弱
　　　　　Visual Studio ではステップ実行もできますし、SI Object Browserなど
　　　　　優れたツールもありますけれど。

　ベンダー間で記述の差が大きく乗り換えにくい。
　　　　　JavaをRubyに簡単に乗り換えれる訳でもないのに、そんな批判は意味ない。

　嫌がる人が多い・できない人が多い
　　　　　これはどうにかしたいですね……。