スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

すべてストアードプロシージャのすゝめ

ちょっとした規模の開発でSQLの呼び出しをほとんどストアードプロシージャー(以下SP)で作りました。
まあ、基本的にはうまくいったと思いますが、色々問題点もあったので、その反省と今後のやり方について書いてみます。今後も改善していくので、この文章もどんどん変えていく予定です。
今回のシステムではメインがPHP5.4でYiiフレームワークを使っています。DBはPostgreSQL9.1です。

■良かったこと

1. 更新系のSQLを一つにまとめられる
いくつものテーブルを更新する場合、トランザクションにまとめて処理することになります。
このときトランザクションの開始から、SQLの実行、コミットまで何度もDBにアクセスする必要があります。
この通信コストがもったいないです。
必要な更新を一回のSPコールで済ませるのなら、呼び出し回数が減らせます。
試してはいませんが、オートコミットでもいけそうです。

何行もの更新が必要な場合もPostgreSQLなら配列が使えます。
数が多い行の更新では、分割して何度も呼んだ方がよいかもしれませんが、OLTPであまりそのような用途は無いと思います。

2. ビジネスロジックの集中
SQLはビジネスロジックが現れやすいところだと思います。
ある条件の情報が欲しい場合WHERE句で表現することになりますが、これってまんまビジネスロジックです。
となると、SQLにビジネスロジックを集中すればPHP側は必要なパラメーターを渡すだけになります。
PHPを作る人が画面の動きに集中できて、SQLを書く人はビジネスロジックに集中できます。

3. ぐるぐる回ししない
PHP脳でデータをとってくることを考えると、ループな考え方になりやすいです。
SQL脳でデータをとってくることを考えると、集合論的な考え方になりやすいです。
DBは集合論的なとりかたをすると、パフォーマンスが良いです。
まあ、どうしてもぐるぐる回しにしないとダメな場合もあるんで、その場合はSPの中でカーソルループさせた方がPHPとDB間と通信がないのでお得です。

■悪かったこと

1. 集約し過ぎ
複数の画面で呼ばれるSPを色々パラメーターを渡して、SQLの作り方を変えていたらSPのソースが読みにくくなりました。
画面ごとにそれぞれ別のSPにすればよかったです。
共通する部分は別のSPにしてSPからSPを呼ぶようにすれば、重複も抑えられると思います。

2. チューニングしにくい
1. の影響とも言えますが、ある画面で必要な項目は別の画面では必要なかったりします。
ID固定で多少時間がかかるSQLでも問題無いけど、一覧系でパフォーマンスを悪くしたところがありました。
結局、パフォーマンスチューニングの時に直接PHPにSQLを書いてしのいでしまったので、きちんと分けていれば完全にSPだけで構築できたと思います。

3. 検索と更新が名前で判断つかない
名前で検索と更新をわけなかったため、レプリケーションしているDBに検索だけ渡すようなことができなくなりました。
list_user_data
edit_user_data
のように検索だけならlist、更新系ならeditのようにprefixをつければpgpoolなどで選り分けできます。
ということで、今後は
名前空間_list_
名前空間_edit_
とします。

4. Dataprovider
Yiiの一覧系を表示する仕組みが勝手にORDER BYやLIMITをつけてくるので、チューニングしにくい。
自前のDataproviderを用意すればよかったです。

■ SPを作る時のポイント

1. 戻り値はtypeを定義する。
SPを入れ直す時にDROP TYPE CASCADEが使えるのが便利。

2. 動的SQLで構築する。
動的SQLにしないとインデックスが効きません。

3. SQLの結果を表示する。
デバッグとチューニング用におすすめ。

w_sql := 'SELECT * FROM t_user';
RAISE NOTICE '%', w_sql;

4. エラーは例外で返す。
U0001からエラーを定義しました。
PHP側でのエラーのハンドリングが楽になりました。

RAISE SQLSTATE 'U0003' USING MESSAGE = 'Not found user ID: ' || p_user_id;

5. パラメーター引数
PostgreSQLではSPの呼び出しにパラメーター引数が使えます。
引数の順番に依存するコードをPHPに書かない方が引数の数が増えた時にメンテナンスしやすいです。

select * from aaa_list_uesr(p_user_name := 'aaa', p_age := 10);

■ FAQ
Q1. SQLよりSPの方がパフォーマンスが悪いのではないか?
A1. 呼び出しコスト分嵩みますが、たいしたコストではないです。

Q2. SQLよりSPの方がチューニングがしにくいのではないか?
A2. できあがるSQLは同じような形になるので変わりません。

Q3. ビジネスロジックがSQLに集中するのが気分が悪い。
A3. SQLにビジネスロジックが集まりやすいです。いっそのこと全てをSQLにした方が楽です。
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。