ストアドプロシージャの利便性と注意点
MySQLやSQLServerで使用できる「ストアドプロシージャ」。
パフォーマンスやセキュリティの面で多大なメリットがあるこの機能ですが、一方で一定のルールを守らなければそのメリットを潰してしまいかねない落とし穴があります。
今回はそんなストアドプロシージャのメリットと注意点を私の経験と合わせてご紹介させていただきます。
ストアドプロシージャとは？
まずは軽くストアドプロシージャとは何かについてご説明しましょう。
ストアドプロシージャとは、「一連の手続きをコンパイルして一つにまとめ、DBサーバに保存したオブジェクト」のことを指します。
この手続きの中には通常のSQLクエリの他にも、普通にクエリを作成した場合には扱えないようなパラメータや条件分岐、繰り返しなどのプログラミング機能も含まれます。
ストアドプロシージャの利点
ストアドプロシージャを利用するメリットは下記のようにいくつかあります。
●パフォーマンスの向上
例えば、「データ検索」と「データの判定」と「データ更新」を行うとします。
埋め込みSQLの場合、「データ検索」と「データ更新」を行う際に計2回のDBサーバとの通信を行う必要があります。対してストアドプロシージャは埋め込みSQLと異なり、検索・判定・更新をひとまとめにしているため、DBサーバへの通信が1度で完了します。
●保守性の向上
ストアドプロシージャはデータベースに対する処理をアプリケーションから隔離することができます。
これにより、ビジネスロジックへ変更を行う場合は関連するストアドプロシージャのみを更新するだけで対応が可能なため、アプリケーションの保守性が向上します。
●セキュリティの向上
ユーザーのDBへのアクセスをストアドプロシージャのみに制限することにより、不用意なデータへの操作などを防ぐことが可能になります。
ストアドプロシージャの注意点
様々な面でメリットのあるストアドプロシージャですが、作成する際にいくつか気にしなければならない点があります。
●トランザクションの管理
ストアドプロシージャの役割は先述した通りクエリの実行です。
実行するSQLの種類によっては当然ながらトランザクションが発生します。ですが、ストアドプロシージャはプログラムのように処理を組むことが可能なため、処理内部に別のプロシージャを呼び出すように構成することもできます。
既存のストアドプロシージャを複数流用して新規のバッチを構築するという案件がありまして、その時は深く考えずに「既存で動作しているものなのだから」と流用したところ、「上位のストアドプロシージャでトランザクションを保持したまま、別のストアドプロシージャを呼び出し、その内部でも更新処理を行う」といった構成になっている部分がありました。
これの何が悪いのかというと、呼び出された側のトランザクションでもしExceptionが起きRoll Backなどの処理をした場合、その場で処理が強制終了になるのですが、呼び出した側のトランザクションは残ってしまいます。それによって想定されていないエラーが発生しまう、ということがありました。
上記の障害に関して私がいたチームでは調査を行い、処理の都合上絶対にその処理を通過することがないという確証を得るためにそこそこの工数を消費することになりました。
深すぎるVIEWやストアドプロシージャの呼び出し
上記にも記載した通りストアドプロシージャは処理内部で別のストアドプロシージャを呼び出すことが可能です。
それと同様にVIEWと呼ばれる、仮想的なテーブルを呼び出して処理することも可能です。これを利用すると普通のプログラムのようにストアドプロシージャのみで値の判定などを行わせることが可能なのですが、やはり本職はデータベースへの処理の為、あまりに深すぎるVIEWや複雑すぎるストアドプロシージャにしてしまうとせっかくの高いパフォーマンスが目も当てられないくらい低速になってしまいます。
実際に私が参加した案件では、VIEWの内部で20や30の別のVIEWを呼び出していたために、処理結果が返却されてくるまでにかなり時間がかかってしまうものが存在しました。案件の日程の関係でこの処理のリファクタリングを行っている暇がなかったため、別案件で対応することとなり、その時はやむなく放置することになってしましました。
まとめ
私の実体験を交えてストアドプロシージャを解説させていただきましたが、いかがでしたでしょうか？
様々な面で多大なメリットがあるストアドプロシージャですが、その便利さの裏には、せっかくの利点を潰してしまいかねないという可能性が隠れていました。
皆さんもストアドプロシージャを作成することがある場合は、「ストアドプロシージャはあくまでDBへの処理が本職である」ということを忘れないようにしながら、製造しましょう。
