Q&A
回答ありがとうございます。
すいません、100個は大げさでした。
＞ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。
これはwhere句で
t1.a=t2.b
t2.b=t3.c
t3.c=t1.d
という条件だと遅くなるという意味でしょうか？
javaとOracleデータベースの組合せで開発をしています。
javaから複数回クエリを実行する場合、それらをすべてjoinまたは副問合せして1回で実行した方がパフォーマンスは上がると思いますが、
例えば、結合数が100個だとしても同じでしょうか？
仮に500万レコードのテーブルを100個結合または副問合せすることになった場合、少しでもパフォーマンスを上げるにはどのような方法があるでしょうか？
例えば、
where句で条件を記述するか、on句にandで記述するかの違い。
複数条件を記述する場合はよりレコード数を絞れる条件を先に記述するなど。
回答6件
0
ケースバイケースだと思います。
OracleのEditionによっても対応策は変わるでしょうし、
そもそも設計から見直したほうがいい気もします。
そのタイミングで人間が使うデータ、という意味ではそんな大量データ見れませんし、
件数の絞り込みもできると思います。
データの分類でテーブル分けしておくなり、パーティション分割するなり、
マテリアライズドビュー使ってサマリテーブル作っておくなり、
そもそもアプリ含めて参照DBを複数たてるなり、
KVSを部分的に使うなり、オンメモリも一部検討するなり、
別の観点での対応が必要かと。
その現場によってできるできないもあるでしょう。
SQLだけでの解決は現実的ではありませんが、
そこまでしか自身の立場で関与できないのであれば、
いろいろSQL文編集しながら実行計画読んで
最適解探していくしかないんじゃないでしょうか。
投稿2014/12/13 11:00
総合スコア30
0
はっきり言って、「ケースバイケース」以上の結論は出ませんので、場面に応じてパフォーマンスをチェックする必要があります。
ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。最近自分が遭遇したケースでは、4つのテーブルにまたがるようなSELECTで、WHERE条件が3テーブルにまたがるようにしていた場合に1秒かかったクエリが、事前に2テーブルのWHERE条件を別途で抽出しておいて、WHEREで絞るテーブルを1つにまとめたところ、（事前抽出も含めて）0.1秒以下で終わるようになりました。
あと、毎回100個ものテーブルをJOINするのが適切なのか、検討が必要かもしれません。もっとも、日計処理のように、リアルタイム実行しなくていいものであればそこまで困ることもないのですが。
投稿2014/12/05 00:22
総合スコア145183
0
まず、DBの設定、各テーブルの正規化が適切になされていることが前提になります。
データベースサーバー側のリソースにもよりますが、CPUやメモリーが十分であれば、中間処理を省いたり通信部分のコストが削減できる点で、一度に処理した方がパフォーマンスは向上すると思います。
パフォーマンスとは別に、Java側で集計やソートするのは、Java8もしくは専用のライブラリーでも無ければコードが冗長になりがちなので、極力UNIONやJOIN、副問合せを駆使して一度で済ませた方が良い、と個人的には思います。
大量のテーブルを結合する場合、主となるテーブルでの絞込みが十分に行われており、被結合テーブルをカーディナリティ度が高いインデックスのキー（PK含む）で結合することができれば、結合するテーブルの数が多くても別々に実行するよりパフォーマンスが低下することはないでしょう。
ただし、結合の数と組み合わせによっては挙動を推測するのが難しくなるので、やはり一概には言えないところではあります。
それ以外にも、一部の結合には、ビューを作っておくのも手です。
WHEREの中で結合するか、JOIN ... ONの違いは、同じ意味になるならSQLの長さだけの問題程度と思います。どのインデックスが使用されるかの方が重要です。
最後になりますが、
どのような方法でも、DB設定や11gのオプティマイザーの挙動によっては想定外のアクセスパスが使用される可能性もありますので、最終的にはSQLトレースで実測してください。
投稿2014/12/05 00:38
総合スコア9388
回答ありがとうございます。
100個はおおげさでした。すいません。
VIEWを作るのも手とありますが、私が思っているVIEWというのは
単純に問い合わせするSQLを保存しておくだけで、
結局は内部で同じものが実行されるので、SQLが見やすくなるだけで
パフォーマンス的には変わらないんじゃないかと勝手に思っているのですが
どうでしょうか？
また、UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよいと聞いたことがあるのですがなるべく使わないほうがいいのでしょうか？
> SQLが見やすくなるだけでパフォーマンス的には変わらない
オプティマイザーは、場合によってはビュー定義のクエリーと追加のクエリーを、効率の良い形式に変換してくれるみたいです。そうなるようにVIEWを定義する、というのはまた一概には言えない話になりそうです。
> UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよい
集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。
集計でも集合でも、まずはできるだけ局所的にデータを絞りこむことです。たとえば10のテーブルをそれぞれ10件まで絞り込んだあとであれば、集計なり集合がそれほどパフォーマンスに悪影響を与えることは無いはずです。
＞集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。
実行計画を見るようにするのが一番ですね。見るクセをつけようとおもいます。
0
失礼します。
以前ある現場で、１時間待っても帰ってこなかったクエリが、結合でレコードの少ないテーブル（マスタなど）から先に参照させることで、２～３秒で返ってきました。
ご参考まで。
投稿2014/12/07 10:50
総合スコア160
おっしゃるとおりで、とても正しいやり方だと思います。
左外部結合時に左側にマスタをもつのは正しい。
私が考えたのは数万行あるテーブル二つ以上を連結する場合の話で、マスターテーブルとトランザクションテーブルを結合するような場合はARADDIOさんのおっしゃっているようなやり方をすべきだと思います。
回答ありがとうございます。
外部結合で左右のテーブルを入れ替えてしまうと、出力結果が変わってしまいますが、入れ替わっても問題ない場合の話でしょうか？
＞ShinpeiYamamotoさん
どのテーブルのレコード数も多い場合ですね。
その場合は別途対応が必要になりますね。失礼いたしました。
＞lupus_dingoさん
問題ない場合（あんまりないと思いますが）入れ替えでいいと思います。
私がイメージしていたのはLEADING句を使うやり方でした。
leading句初めて聞きました。
ヒントの1つなんですね。
参考にさせていただきます。
0
細かいクエリの話は他の方が言っておられるので省きます。
500万レコードのテーブル100個に跨る様なリクエストが発生する様な状況は、設計段階で排除すべきです。
それだけ複雑なテーブル構造で最適なSQLを作り、最適なチューニングを行うのは、もはや職人芸と言っても過言ではないです。
（理論上は、実行計画的、且つインフラ的に最適化されていれば性能は担保できると思いますが）
そういったいわゆるビッグデータ系のデータを扱う場合、RDBではそもそも限界があるので、
全てを基本プライマリキーのみで検索できるように設計した上で、NoSQL系DBを利用すべきでしょう。
もしくはカラムナー系のDBを利用するかですね。
もし客先要件等のめんどくさい理由からOracleしか使えないという事であれば、
MySQLをNoSQLDBとして利用するノウハウを流用できるかもしれません。
ちなみにですが、DBサーバを複数台に分散している様な場合には、物理的なサーバ間を跨ぐようなリクエストをすると、
どう頑張ってもパフォーマンスは一定以上に上がらないので、ご注意ください。
以上、ご参考になれば幸いです。
投稿2014/12/05 05:16
総合スコア384
回答ありがとうございます。
100個はおおげさでした。すみません。
確かにもしそうなったら設計の段階で排除すべきことですね。
初めて聞いた単語がいくつかあるので勉強してみます。
0
ベストアンサー
自分の経験上、JOINは遅いという認識です。
JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。
基本的には、数万行から数十万行のデータをもつテーブルを連結する場合、
①ON句に指定する結合条件にインデックスサーチが効くようにする。結合条件で指定する列にINDEXを張る、もしくは主キーを使う。複合主キーの場合は、主キーに使用される列すべてをそろえて指定しインデックスサーチがきくようにする。
②サブクエリを用いてあらかじめWHERE句で集約を行い、JOINされるテーブルのレコードの母数を減らす。
のような工夫をおこないます。
あとはそうですね、ロジックの組み方にもよりますが分けてとった方が早くなる、ということもよくあります。
数万回もクエリを投げると明らかに遅くなると思いますが、数回に分ける程度で済むのであれば、無理してJOINで結合して取得するより圧倒的に早いという印象です。
取得してからアプリケーションのロジックで結合を行う必要があったとしても、何かしらの条件で集約された結果に対しての照合になるので、照合・比較の回数は圧倒的に減ることになるからです。
実行計画などを確認して、結合でどのあたりがネックになるか確認しながら作業を進めることをお勧めします。
参考になれば幸いです。
投稿2014/12/07 07:18
総合スコア540
回答ありがとうございます。
＞JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。
結合したテーブルはon句に条件を書くことで母数を減らせるんですね。これからはなるべくwhere句ではなくon句に条件をandで書くようにします。
申し訳ない。私の説明が悪かったと思います。ON句に条件を追加しても母数が減ることはありません。それは誤りです。
そうではなくて、JOINする対象のテーブルをサブクエリ等で仮想表とし、whereによる条件指定で結合対象のレコードを減らしておくのがよい、と申し上げたつもりでした。
①JOINを行う場合、母数となる集合にたいして集約を行った上でJOINした方が早い
②ON句に指定する条件をインデックスの張られた列にする。複合主キーの場合、すべての主キー列を指定する
ということです。
select * from a
inner join b
on a.id=b.id
よりも、
select * from a
inner join (
select id from b
where id in (111,222,333)
) b
on a.id=b.id
というように、joinでviewにした方がパフォーマンスがいいということですね。
知識不足ですみません。
勉強になります。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2014/12/15 23:22