« 初心者向けプログラミング言語 | トップページ | オブジェクト指向分析設計は万能薬なのか »

2009年12月 3日 (木)

SQL を実行時毎に組み立てなければならないとき

まあ、全くないわけじゃないのですけど。

例えば、アプリに、項目の並び順を、ユーザに指定させるような機能があるときとか。そもそもパラメータが使えない構文要素ですと、どうしようもないわけで。

ただ、検索条件の指定みたいに、SQL の 条件句に入ってくるのは、パラメータを使えば、 SQL の組み立ては、 基本、 1 回で済むはずなのですよね。

例えば、商品(書籍)に対して、以下の条件を使って検索できる機能が、アプリにあるとします。

  • タイトル
  • 発売日
  • 出版社
  • 著者名

この場合、以下のように SQL を作ればよいですね。

require 'pg'

SQL_ITEM_SEARCH = <<EOS
select
i_title,
i_pub_date,
i_publisher,
a_fname,
a_lname,
a_mname
from item i
inner join author a
on i.i_a_id = a_id
where
(i_title = $1 or $1::varchar is null)
and (i_pub_date >= $2 or $2::date is null)
and (i_pub_date <= $3 or $3::date is null)
and (i_publisher = $4 or $4::varchar is null)
and (a_fname = $5 or $5::varchar is null)
and (a_lname = $6 or $6::varchar is null)
and (a_mname = $7 or $7::varchar is null)
order by i_pub_date desc, i_title
EOS

conn = PGconn.open(
:dbname => 'dbt1',
:hostaddr => '127.0.0.1',
:user => 'postgres')
conn.set_client_encoding('SJIS')

res = conn.exec(
SQL_ITEM_SEARCH,
[nil, '2000-01-01', nil, nil, nil, nil, nil])

res.each { |t| p t }

|

« 初心者向けプログラミング言語 | トップページ | オブジェクト指向分析設計は万能薬なのか »

コメント

ronさん、こんばんは。

よくやるのですが、上のパターンだとインデックスが全く使えませんので、弊社では必須の検索条件でインデックスを使って1万件以内にしぼれているときに使うように指示しています。

バインド変数を使わなかったらインデックスが効きますので敢えてバインド変数を使わない形にするとか、だったらIF文再構築するのとどう違うのか?って悩みますね……。

そういうときのヒントを作って欲しいのですけどね。

投稿: kantomi | 2009年12月19日 (土) 18時06分

kantomi さん、コメントありがとうございます。

あれ? と思って試したのですが、
(PostgreSQL 8.4 です)

同じ様な Ruby コードで、
---
explain select
i_title,
i_pub_date,
i_publisher
from item i
where
(i_pub_date = $1 or $1::date is null)
---

を実行しますと、
---
{"QUERY PLAN"=>"Index Scan using i_i_pub_date on item i (cost=0.00..8.27 rows=1
width=88)"}
{"QUERY PLAN"=>" Index Cond: (i_pub_date = '2000-01-01'::date)"}
---

と出てきます。

PostgreSQL 7 ですと、planner がバインド変数の値まで見ない、というのはありそうです。また、PREPARE -> EXECUTE で実行する場合は、また結果が違ってくると思います。
---
In some situations, the query plan produced for a prepared statement will be inferior to the query plan that would have been chosen if the statement had been submitted and executed normally. This is because when the statement is planned and the planner attempts to determine the optimal query plan, the actual values of any parameters specified in the statement are unavailable.

PREPARE
PostgreSQL 8.4.0 Documentation
---

私、何か勘違いしてます?

投稿: ron | 2009年12月20日 (日) 02時18分

PostgreSQLは、ストアドプロシージャの内部のSQLにシンタックスエラーがあってもコンパイルが通りますから、ストアドプロシージャですら毎回コンパイルしているのかも知れませんね。

バインド変数も同じ扱いなのかな。

OracleやSQLServerでは使えません。

バインド変数を使うとアクセスパスは前のモノを再利用するので、バインド変数の周辺のロジックはルールベースと同じになりますので、OR が入った時点でインデックスを使わなくなりますが、リテラルで書くとインデックスを使います。

投稿: kantomi | 2009年12月20日 (日) 09時00分

kantomi さん、おはようございます。

>バインド変数を使うとアクセスパスは前のモノを再利用するので、バインド変数の周辺のロジックはルールベースと同じになりますので

それはまた、微妙な判定ですねえ。

コストベース・オプティマイザを使う以上、
アクセスパスの再利用はないと踏んでましたが。
手元に Oracle がないので、明日以降試してみます。

投稿: ron | 2009年12月20日 (日) 10時02分

kantomi さん

Oracle 10g XE の存在を思い出しまして、急遽ダウンロード&インストールして試してみました。

確かにおっしゃるとおりですorz

これ、リテラルでインデックスが効くのは、おそらく、コンパイル時に定数が畳み込まれて消えるからですね。実行計画には、OR の後半が出てこなくなりますし。

と、すると、コンパイル・フェーズまで差し戻さなければならないわけで、ヒントだとそこまではできないかもしれませんね。

OR を UNION ALL に変換する USE_CONCATヒント というのはありますけど、結局後半の IS NULL は FULL SCAN されますね。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/sql_elements.html#55592

うーん、確かに悩ましいですね(笑)

何か良い方法が思いついたら、ブログで紹介させていただきます。

投稿: ron | 2009年12月20日 (日) 19時27分

逆にPostgreSQLではガンガン使えるということが分かって良かったです。
弊社では、EC-CUBEのカスタマイズぐらいでしか、PsotgreSQLは使わないのですが、今後、検討しようと思います。

ありがとうございました。

投稿: kantomi | 2009年12月21日 (月) 13時20分

kantomi さん

未熟ぶりをさらしてしまいましたが、
こちらこそ、勉強になりました。

これからも、よろしくお願いします。

投稿: ron | 2009年12月22日 (火) 01時50分

コメントを書く



(ウェブ上には掲載しません)


コメントは記事投稿者が公開するまで表示されません。



トラックバック

この記事のトラックバックURL:
http://app.f.cocolog-nifty.com/t/trackback/80472/32466357

この記事へのトラックバック一覧です: SQL を実行時毎に組み立てなければならないとき:

« 初心者向けプログラミング言語 | トップページ | オブジェクト指向分析設計は万能薬なのか »