質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.02%

  • SQL

    2011questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

SQLのパフォーマンスについて

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 229

msd

score 80

オンラインの処理でデータをデータベースより取得してダウンロードをする機能を作成しています。
大量データ下でのSQLのパフォーマンスが悪くjavaでの処理を含めて10分以上かかっています。

現状、SQL単体で5分~7分程度かかっているのですが、
1.SQLの記述が悪い
2.そもそもこれくらいかかるもの

という切り分けができないでいます。
DBMSはoracle 11gです

【仕様】
テーブルA~Eは内部結合
テーブルF~Hは外部結合
各テーブルで特定のカラムの最大を求める必要がある
最終的に、取得したデータ単位内で並べ替えて優先順位1位のデータのみを取得したい
  
【データ】
テーブルA :3件
テーブルB :50件
テーブルC :150件
テーブルD :900,000件(会員に関するデータ。会員に対して各世代のデータを持つ。本件においては30万×3世代)
テーブルE :300,000件(会員単位の属性情報のデータ)
テーブルE´:300,000件(会員単位の属性情報のデータ。EかE´のどちらかに存在)
テーブルF :450件
テーブルG :450件
テーブルH :150件
 
【SQL】

SELECT
    *
FROM
    (
        SELECT
            t1.pk1,
            t1.pk2,
            t1.pk3,
            t1.pk4
        FROM
            テーブルD t1,
            (
                SELECT
                    --ここでpk1、2単位での最大値を取得
                    t2.pk1,
                    t2.pk2,
                    MAX(t2.pk3)
                FROM
                    テーブルD t2
                WHERE
                    t2.pk1 = 'XX' AND
                    t2.pk2 = 'YY' AND
                    EXISTS
                    (
                        SELECT * FROM テーブルE t3 WHERE t3.pk2 = t2.pk2
                    )
                AND
                    -- その他条件
                GROUP BY
                    t2.pk1,
                    t2.pk2
            )
            t4
        WHERE
            t1.pk1 = t4.pk1 AND
            t1.pk2 = t4.pk2 AND
            t1.pk3 = t4.pk3
    )
    t5,
    (
        SELECT
            t11.pk1,
            t11.pk2,
            t11.pk3,
            t11.pk4
        FROM
            テーブルA t11,
            (
                SELECT
                    --ここでpk1、2単位での最大値を取得
                    t12.pk1,
                    t12.pk2,
                    MAX(t12.pk3)
                FROM
                    テーブルA t12
                WHERE
                    t12.pk1 = 'XX' AND
                    t12.pk2 = 'YY' AND
                    -- その他条件
                GROUP BY
                    t12.pk1,
                    t12.pk2
            )
            t14
        WHERE
            t11.pk1 = t14.pk1 AND
            t11.pk2 = t14.pk2 AND
            t11.pk3 = t14.pk3
    )
    t15
WHERE
    t5.pk1 = t15.pk1 AND
    t5.pk2 = t15.pk2 AND
    t5.pk3 = t15.pk3
    -- pk4はテーブル間で一致しないのでN:Nの組合せになります

・・・以下、似た条件で結合していっています。    

全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
実行計画

| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                     |                           |     1 |   216 |  6894   (1)| 00:01:23 |
|*  1 |  FILTER                              |                           |       |       |            |          |
|*  2 |   HASH JOIN                          |                           |    94 | 20304 |  6706   (1)| 00:01:21 |
|*  3 |    HASH JOIN                         |                           |   282 | 53580 |  6701   (1)| 00:01:21 |
|   4 |     VIEW                             |                           |     1 |    26 |     9  (23)| 00:00:01 |
|   5 |      HASH GROUP BY                   |                           |     1 |    92 |     9  (23)| 00:00:01 |
|   6 |       NESTED LOOPS                   |                           |     1 |    92 |     8  (13)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                           |     1 |    92 |     8  (13)| 00:00:01 |
|   8 |         VIEW                         |                           |     1 |    36 |     5  (20)| 00:00:01 |
|   9 |          HASH GROUP BY               |                           |     1 |    77 |     5  (20)| 00:00:01 |
|* 10 |           TABLE ACCESS BY INDEX ROWID| テーブルC                 |     1 |    77 |     4   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | PK_テーブルC              |    13 |       |     3   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN             | PK_テーブルC              |     1 |       |     2   (0)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID   | テーブルC                 |     1 |    56 |     3   (0)| 00:00:01 |
|  14 |     NESTED LOOPS                     |                           | 14652 |  2346K|  6692   (1)| 00:01:21 |
|  15 |      NESTED LOOPS                    |                           | 35317 |  2346K|  6692   (1)| 00:01:21 |
|  16 |       NESTED LOOPS                   |                           |     1 |    75 |     1   (0)| 00:00:01 |
|  17 |        NESTED LOOPS                  |                           |     1 |    47 |     1   (0)| 00:00:01 |
|  18 |         VIEW                         |                           |     1 |    19 |     1   (0)| 00:00:01 |
|  19 |          HASH GROUP BY               |                           |     1 |    95 |     1   (0)| 00:00:01 |
|* 20 |           TABLE ACCESS BY INDEX ROWID| テーブルA              |     1 |    95 |     1   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN          | PK_テーブルA           |     1 |       |     1   (0)| 00:00:01 |
|* 22 |         INDEX UNIQUE SCAN            | PK_テーブルA           |     1 |    28 |     0   (0)| 00:00:01 |
|* 23 |        INDEX UNIQUE SCAN             | PK_テーブルA           |     1 |    28 |     0   (0)| 00:00:01 |
|* 24 |       INDEX RANGE SCAN               | PK_テーブルD          | 35317 |       |  6691   (1)| 00:01:21 |
|  25 |      TABLE ACCESS BY INDEX ROWID     | テーブルD             | 73583 |  6395K|  6691   (1)| 00:01:21 |
|  26 |    VIEW                              |                           |  1030K|    25M|     1   (0)| 00:00:01 |
|  27 |     HASH GROUP BY                    |                           |  1030K|    74M|     1   (0)| 00:00:01 |
|* 28 |      TABLE ACCESS BY INDEX ROWID     | テーブルD             |  1030K|    74M|     1   (0)| 00:00:01 |
|* 29 |       INDEX RANGE SCAN               | PK_テーブルD          |    55 |       |     1   (0)| 00:00:01 |
|* 30 |   TABLE ACCESS BY INDEX ROWID        | テーブルE             |   183 | 15555 |     4   (0)| 00:00:01 |
|* 31 |    INDEX RANGE SCAN                  | IDX__テーブルE       |     1 |       |     3   (0)| 00:00:01 |
|* 32 |   TABLE ACCESS BY INDEX ROWID        | テーブルE´          |     1 |    78 |     1   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                  | PK_テーブルE´       |     1 |       |     1   (0)| 00:00:01 |

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2017/10/10 12:07

    実行計画のnameを省略されるとちょっと厳しいですね。DBMSはoracleですよね?質問に明記して下さい。

    キャンセル

  • msd

    2017/10/10 12:20

    nameがないと厳しかったのですね。追加しました。見づらいですが、コピーしてテキストに貼り付けると整って見えます。

    キャンセル

  • szk.

    2017/10/10 16:04

    t4とt5と最終的な結果の想定件数を教えてください。またt15は3件以下になりますでしょうか。

    キャンセル

  • SVC34

    2017/10/11 09:54

    サブクエリの中で求めているMAXが上位のクエリに現れていませんが、実際は上位のクエリでこれを「最終的に、取得したデータ単位内で並べ替えて優先順位1位のデータのみを取得したい」という要件のために利用しているのでしょうか。

    キャンセル

回答 4

checkベストアンサー

0

修正

テーブルA,Dに対して「その他条件」として割愛されています。
テーブルDに対してインデックスは使用されていますが、ROWIDで実テーブルを参照していますので、
その部分でコストが掛かっていると思われます。
多分インデックスに無い項目に対する条件となっていると思われますので、
その条件も明らかにしてもらえますか。

追記

手っ取り早くは、「その他条件」となっているところをコメントアウトして、実行計画を確認してみることです。
それで、性能が改善されるようであれば、SQLを組み直したり、インデックスを追加するなどの手当てが必要になります。
それ以外では、自己結合が多いので、Lead() over()などを使用することで改善されるかもしれません。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/10 12:30

    nameが追加される前に勘で回答しちゃいました。テーブルDの方かな?
    追記してみます。

    キャンセル

  • 2017/10/10 13:04

    少し遅くなりました。
    実行計画の抜粋です。

    | 13 | VIEW | | 1 | 36 | 5 (20)| 00:00:01 |
    | 14 | HASH GROUP BY | | 1 | 77 | 5 (20)| 00:00:01 |
    |* 15 | TABLE ACCESS BY INDEX ROWID| テーブルA    | 1 | 77 | 4 (0)| 00:00:01 |
    |* 16 | INDEX RANGE SCAN | PK_テーブルA    | 13 | | 3 (0)| 00:00:01 |
    |* 17 | INDEX RANGE SCAN | PK_テーブルA    | 1 | | 2 (0)| 00:00:01 |
    | 18 | TABLE ACCESS BY INDEX ROWID | テーブルA    | 1 | 56 | 3 (0)| 00:00:01 |
    |* 19 | HASH JOIN | | 71091 | 8122K| 6696 (1)| 00:01:21 |
    |* 20 | INDEX RANGE SCAN | PK_テーブルA    | 2 | 56 | 1 (0)| 00:00:01 |
    | 21 | TABLE ACCESS BY INDEX ROWID | テーブルD     | 1030K| 87M| 6691 (1)| 00:01:21 |

    キャンセル

  • 2017/10/11 14:49

    アドバイスありがとうございます。
    再検討してみます。

    キャンセル

0

利用されているRDBMSが不明ですが、実行計画を取得してどこに時間がかかっているかを確認されるとよいかと思います。

一般的には、実行されるSQLの冒頭にEXPLAINを付与すれば取得できることが多いかと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/10 12:21

    実行計画を追加しました。

    キャンセル

0

勘で申し訳ありませんが、テーブルDとテーブルEとのEXISTSをJOINに変えてみたらどうでしょうか?
group byされているので同値になると思われます。

        SELECT
            t1.pk1,
            t1.pk2,
            t1.pk3,
            t1.pk4
        FROM
            テーブルD t1,
            (
                SELECT
                    --ここでpk1、2単位での最大値を取得
                    t2.pk1,
                    t2.pk2,
                    MAX(t2.pk3) pk3
                FROM
                    テーブルD t2,
                    テーブルE t3
                WHERE
                    t3.pk2 = t2.pk2 AND
                    t2.pk1 = 'XX' AND
                    t2.pk2 = 'YY'
                -- AND
                    -- その他条件
                GROUP BY
                    t2.pk1,
                    t2.pk2
            )
            t4
        WHERE
            t1.pk1 = t4.pk1 AND
            t1.pk2 = t4.pk2 AND
            t1.pk3 = t4.pk3

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/10 12:22

    当初はjoinしていました。
    existsのほうを試したのが上の結果ですが、あまり変わっていないのが現状です。

    キャンセル

0

分析関数 ROW_NUMBER の使用例を使うことで同じテーブルを2度SELECTしないで済むので、件数の多いテーブルに対してはかなりパフォーマンスを改善できます。

会員単位の属性情報のデータ。EかE´のどちらかに存在)

というテーブル設計ではSELECT時のパフォーマンスが悪いので、EとE´を合体したテーブルを作られては?

件数が多いテーブル同士のJOINはNESTED LOOP JOIN より HASH JOIN を使った方がパフォーマンスが改善されることがあります。USE_HASHヒント

また、SELECT文を実行できる範囲のCREATE TABLE, CREATE INDEXは提示された方が解決が早いです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 91.02%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2011questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。