C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[7]: 同じ検索結果のSQLの違い

分類:[データベース全般]　

Oracleの質問をさせて下さい。

select A,B,C from あTable
select A,B,D from いTable

上記のTableがあるとします。結合の方法で

select
あ.*,
い.*
from
あTable あ,
(select
*
from
いTable
where C = 'c') い
where
あ.A = い.A(+) and
あ.B = い.B(+)

という書き方と

select
あ.*,
い.*
from
あTable あ,
いTable い
where
あ.A = い.A(+) and
あ.B = い.B(+) and
い.C = 'c'

という書き方ができます。
検索結果も同じです。
パフォーマンスは下の書き方の方が早いです。

が、何故、早いのですか？
上と下のSQLの書き方違いとは何ですか?

最初のSQLを１、次のSQLを２　と都合上呼んで、おおざっぱに解釈してみますね

１は、最初に、「い」テーブルのためのSELECT文を実行して、結果をいったん内部テーブルに作る。その後に、その内部テーブルと「あ」テーブルをくっつけて、SELECT文を実行する

２は、「あ」テーブルと「い」テーブルをくっつけてSELECT文を実行する

というわけで、中でやっているSELECT回数が違うのが差が出る原因の一つだと思いますです。

あと、「あ」と「い」のテーブルにおいて、列AとBにインデックスもしくはキー項目がある場合、キー同士をくっつけてSELECTするSQLは、キー項目でない列をくっつけるよりも早くなります。
先ほどの１で途中につくられていた内部テーブルにはインデックスもキーも存在できないので、もっと差がはっきり出ると思いますですよ

横から質問させてください。

いTableに大量のデータが存在し、
かつ、where条件でかなり絞られる場合、
どちらが早いでしょうか？

昔から、そこが疑問にもっていましたが、
そういう実データに見当たらないので、疑問をそのままにしてまして。

大きな違いに気付いたので、横から（下から？）書かせてもらいます～

> という書き方ができます。
> 検索結果も同じです。

同じじゃないです。
同じにするためには２つめの方の最後の条件にも(+)がいります。
・・・い.C(+) = 'c'

inner joinとかの書き方で表すと、２つめのＳＱＬは、
「い.C = 'c'」とした場合は★１、
「い.C(+) = 'c'」とした場合は★２と同義です。

★１
select あ.*, い.*
from あTable あ
left outer join いTable い
on あ.A = い.A
and あ.B = い.B
where い.C = 'c'

★２
select あ.*, い.*
from あTable あ
left outer join いTable い
on あ.A = い.A
and あ.B = い.B
and い.C = 'c'

（違いはＣの条件がonにあるかwhereにあるかだけです。）

この２つは、
★１の方は、onの条件で結合した後にwhereの条件で絞り込み。
★２の方は、onにある片方への絞り込み条件で絞り込みが行われた後、結合。
のように処理されると解釈するようにしてます。
なので、★１の方は結局はinner joinしてるのと同じになってしまいます。
(+)は古い書き方なので、なるべくしない方がいいと習いました。

> パフォーマンスは下の書き方の方が早いです。
>
> が、何故、早いのですか？
> 上と下のSQLの書き方違いとは何ですか?

サブクエリーを使った方とそうでない方は、もしどちらもleft outer joinにされる場合には
パフォーマンスは変わらないです。（統計情報とかとると変わるのかな？）
これはオラクルでの「実行計画」というもので確認できます。
確認の仕方は調べてください～

下の方が速いのは、上はleft outer joinになっていて、下はinner joinになっているというのが理由だと思います。
「いTable」が右側の場合のleft outer joinでは、「いTable」の絞り込みはほとんど結果の速度には影響しませんけど、
inner joinの場合、「いTable」がたくさん絞り込めるかどうかが速度に大きく影響する可能性があります。
なので、下のＳＱＬの方が速くなったんだと思います。

私の書いたの、絶対間違ってるところあります。。。
かたぎりさんのがあってると思います。

あした、もう一度調べて返信します。

2009/04/13(Mon) 09:01:36 編集(投稿者)

横田さんへ

かたぎりです。
うーん、一概に、どっちって明言できません。
それぞれに使いたいテーブルとそのテーブルでのキー設定等、
状況が違っていたら同じSELECT文でも動きが異なりますし。

大量テーブルからデータを絞り込むSQLにおいて
絞込み条件がキーだけで構成されて粒度が細かい場合と
絞込み条件で細かくできるが、そのSQLに時間がかかる場合
とでは先ほどの１と２のSQLでの実行結果が逆転することもありえます（話が極端ですけどね）

ただ、ポイントとして、

・実行される個々のSQLのパフォーマンス（検索キー等）
・結合するテーブル同士のキー関係

を考えるようにすることで、ほんのちょっとですが
どちらが早くなるかを考える手がかりにはなるかと思いますですよ

おはようございます。
もう私の返信読まれてしまいました？
いろいろ間違ってましたorz

> 同じじゃないです。

わたし、言い切っちゃってますけど、いつも同じになるわけじゃないという意味でした。
確認されたデータでは同じになったんだと思いますが、データによっては同じにならない場合がでてきます。

> 下はinner joinになっているというのが理由だと思います。

間違ってました；
inner joinになってるって書きましたが、実行結果がinner joinと同じになるだけで、
オラクルではouter joinとして処理されてました；
いったんouter joinで結合され、その後に絞り込みが行われてました。

どうして速いかについて改めて調べたんですが・・・、わかりませんでした・・・；
実行計画では、
１つめは結合と同時にＣの条件も処理されるけど、
２つめは結合の後にＣの絞り込みが行われてました。
（もし２つめを(+)を付けたものにすると、１つめと同じ処理になりました）
この違いはそんなにパフォーマンスに影響しないように思います。

もしかすると、取得したデータはキャッシュされるので、２度目だから速かったとかないでしょうか。。？
何度も実行すると、どちらも同じにならないでしょうか？

(+)がない場合（inner joinの場合）だと、「いTable」を駆動表にすることもできるので、
Ｃ項目の絞り込みを使った最適化もできるかもしれないけど、
outer joinではそれほど最適化はできないと思います。

かたぎりさんが書かれてるポイントは、すごくポイントだと思います；
結局私のは明確な回答になってなくてごめんなさい・・・

HiJunです。

かたぎりさんがおっしゃっていることが一番ごもっともになります。
双方のテーブルのデータ件数の大小、主キー(やINDEX)を使用して
いる・使用していないによっても状況は変わるかと思います。

あとは、使用しているデータベースの処理の動きによっても
結果が変わることもあるかと思います。

C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[7]: 同じ検索結果のSQLの違い

過去ログには書き込み不可