なぜ、人はSQLをループさせたがるのか

なぜ、人はSQLをループさせたがるのか | @IT

すげーびっくり、恥ずかしい...けど、すごく綺麗な
文章でまとめて頂いて本当にありがとうございます。
まえにブログに書いたやつです。
 -> CLUB DB2で5分だけDBFluteしゃべりました | jfluteの日記

最後のポイントの補足ですが:

「データベース設計は国家資格じゃない」
 ↓
「DB設計者は現場で設計しながら成長していく」
 ↓
「まずは、こういった勉強会に参加することが大事」
 ↓
「そして、DB変更を許容できるフレームワークを」
 ↓
「DBFluteでDBのリファクタリングを」

という感じです。
プログラムはわりとリファクタリングってやりますが、
DBはなかなかできない。でも、DBこそ長生きなんだから、
リファクタリングして長持ちする構造にしたいと。
(というか業務が変われば普通にDB変更になるし)

DBFluteのLTの資料はこちらからダウンロードできます。
 -> 第146回 達人が語る こんなデータベース設計はヤダ! | ClubDB2
もちろん、メインはミックさんのお話。
ぜひぜひじっくり読んでください。

そのミックさんの講演時に、

質問者 「好きなO/Rマッパはありますか?」
ミックさん 「O/Rマッパが嫌いです」
jflute 「DBFluteはぐるぐる系のO/Rマッパではないですよぅ」
ミックさん 「じゃあDBFluteは好きです」(会場、笑いと拍手)

というちょっとしたおもしろやりとりがあったのですが、
せっかくなのでしっかり説明しておきましょう。
DBFluteがぐるぐる系じゃない理由を...
まずは大前提。
ここでいう「ぐるぐる系のO/Rマッパ」って、
要は LazyLoad を擁する Hibernate のようなもの。

例えば、会員一覧で50件レコードを取得するとします。
同時に会員ステータスの名称も画面に表示したいと。

特に会員ステータスのデータも取得するとか指定もせず、
Entityから getMemberStatus() とするだけで、
その Getter メソッドの中でその一人の会員に対応する
会員ステータスを検索するSQLが走ってデータを戻ります。
会員をループで回しながら get しまくると、
結果的にSQLの発行回数は「1 + 50 = 51」回に。
俗に「n+1問題」と言われています。
一つ一つのSQLは速くても通信コストかかり過ぎというやつ。

通常は、SQLで結合して一発で持ってくるのがセオリーですね。
こういう場合の結合コストなんて全然たいしたことないので。

DBFluteは、会員ステータスに対して何も指定がなければ、
getMemberStatus()したら null が戻ります。
要は、先に「何を取得するのかを明示的に指定する」ポリシー。
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus(); // *here

List<Member> memberList = memberBhv.selectList();

for (Member member : memberList) {
    MemberStatus status = member.getMemberStatus();
    ...
}
Hibernate使っていた方には「これが面倒じゃん!?」って
言われたりもしますが、いいんです。DBFluteは。
面倒でもトラブらない方がうれしいと考えるので。
(プログラムを見れば何を取っているのかが一目瞭然ってのも重要)

一方で、SQLで結合を書いてselect句並べるのは確かに面倒なのこと。
開発効率重視でそれを省略して LazyLoad に頼るというのは一理あり。
ということで、
DBFlute では「SQL でやることと本質を変えず」に、
ただ「SQLだと発生する手続きを省略」できるような実装を提供。
開発効率と実行時のパフォーマンスの両面にアプローチという感じ。

もちろん、Hibernateのような高機能O/Rマッパでは、
SQLの時点で明示的に結合して持ってくることもできます。
というか、すごく細かいフェッチ戦略が可能です。
感心するくらいすごいですよ。

ただ、LazyLoad ができちゃうってところがポイントなのかと。
また、細かいフェッチ戦略の難易度が高いという面も重なって。

どうしても現場は「開発効率重視」に向きますから、
どうしても現場はスキルがバラバラなメンバーになりがちですから、
さくっとできちゃう方向に進んでしまいがち。

たぶん、仮にDBFluteが LazyLoad をサポートしたとしたら、
結局は同じ問題が発生するでしょう。そうでなくっても、
わざわざfor文で回して検索する人もたまにはいらっしゃいます。

それがわかっていたから、当初から DBFlute は、
「LazyLoadは絶対にサポートしない」
というポリシーを全面に打ち出してきたのです。
Apache Torque の名残でOSSとして公開前は LazyLoad っぽい
機能はあったのですが、DBFluteにするときに削除をしました。

もろもろパフォーマンス考慮について書いたページもあります。
 -> DBFluteのパフォーマンス考慮 | DBFlute
もちろん、「なんでもかんでもSQL一発取り」みたいなのが
常に良いとは考えません。

明らかに無理矢理なSQLをやるくらいなら、
三発四発のSQLに分解してしまう方が、
開発効率も良いしメンテナンスもしやすいし(属人性の排除)、
実はSQLの実行スピードも安定する場合もあるかと。

実際に、枝分かれでネストしたone-to-manyのデータを
一発で取得するSQLが現場で火を噴いたのを見たことあります。
件数が少ない時はいいんだけど、ちょっと多くなるとSQLの結果セット
が膨大なことになって、JDBCのResultSetフェッチするところで
めっちゃ時間かかってしまうという。SQLが遅いというよりかは、
無駄なレコード膨張で逆に通信と処理が増えてしまっていると。
適度に分解すればスピードは安定したはず。

常にバランスなんですね。
開発効率も無視できない、実行スピードも無視できない、
時にそこが対立的になる。じゃあどうする?って話で。

どっちかを完全優先するんじゃなくて、割合優先をする。
片方を優先しつつも少し妥協も入る、くらいが現場にフィットする。
時と場合によって完全に片方に優先をよせることもたまにはあるけど、
そうじゃないことの方が多いから、バランスを取ることが大事。

無駄なぐるぐるはしない。
でも開発効率が極端に落ちるなら、少し妥協する。
妥協した分の実行時への影響が極小なら別に問題はない。
そして、そういうバランスがやりやすいツールを導入する。

DBFluteの one-to-many のアプローチはまさしくこのポリシー。
one-to-many のデータの検索は世界中で課題です。

SQL一発で取得するか、LazyLoadで取得するか?

後者は既に論外ということで置いておいて、さて前者は?
確かに速いときはめちゃ速く、最速と言えるスピードが出るのですが、
枝分かれしたりネストしたりとなると、先ほどの通り、
結果セットの膨張で突然遅くなる可能性があります。
また、そのフラットな結果セットをオブジェクト構造にマッピングする
プログラムもとても複雑だし、実行するのもコストがかかることです。

DBFluteでは、その真ん中。
例えば、会員一覧で50件レコードを取得するとします。
同時に購入履歴もツリー構造で画面に表示したいと(もしくは帳票とか!?)。
まずは会員一覧で50件検索、そしてその50人の会員に紐付く購入履歴を
全てもう一回のSQLで取得して、オブジェクト構造に(自動)マッピング。
「1 + 1 = 2」回のSQLで取得します。
 -> LoadReferrer | DBFlute
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus(); // with MEMBER_STATUS

List<Member> memberList = memberBhv.selectList();

// select PURCHASE related to the members
memberBhv.loadPurchaseList(memberList, ...(PurchaseCB cb)) {
    cb.setupSelect_Product(); // with PRODUCT
    cb.addOrderBy_PurchaseDatetime_desc();
};

for (Member member : memberList) {
    List<Purchase> purchaseList = member.getPurchaseList();
    ...
}
基点テーブルのレコード何件であろうと、SQLの発行回数は子テーブルの数。

最速ではないですね。
枝分かれやネストが入ってくると四発五発のSQLとなりますから。
でも一方で、そういった状況でもスピードは安定しています。
最速でなくてもいいです。十分運用に耐えられるのであれば。

ちなみに基点テーブルと to-one、子テーブルと to-one の関連テーブルは、
そのときそのときのSQLで結合して取得するのでSQLの発行回数に影響しません。
(例えば、会員の会員ステータス、購入履歴の商品マスタなど)

DBFluteのone-to-manyに対するアプローチはこれだけです。
HibernateのようにLazyLoadもできなければ細かいフェッチ戦略もないです。
(HibernateDBFluteと同じようなやり方にすることもできますね)

それでも、多くの現場で DBFlute を使ってもらっていますが、
この LoadReferrer で数多くの場面を乗り切っています。
逆に、これだけに絞っているので、誰がどんな考えで使おうと、
わりと同じような実装になって安定させることができますし、
プログラマも迷わないので開発効率にも貢献します。

もちろん、外だしSQLで一発取りもできるので、
(そのかわりフラットな構造での取得になりますが)
稀にどうにかしないといけない場面ではお好きなように。
いざとなればどうにでも、でも、メジャーなやり方は安定志向。
そのくらいのバランスが現場にはフィットするのかなぁと。

くどくど書きましたが、そのくらい書かないと
小数点の気持ちが伝わらないと思って。

// 小数点の自己主張 | jfluteの日記
http://d.hatena.ne.jp/jflute/20120818/decimal
まあ、LazyLoad とか細かいフェッチ戦略とか、
フラット構造をオブジェクト構造にマッピングとか、
フレームワーク作る方からすると実現するのあまりに大変なので、
負け惜しみのように聞こえるかもしれませんけどねー(^^

なので、オープンソースプログラマとしては、
当然「高機能O/Rマッパ」は尊敬しています。
本当にすごいなぁ、よく作ったなぁと機能を見るたびに感心します。
ただ、そのことと「現場にフィットすること」は別の話にならざるを得ないと。