SQL

SELECT句の副問合せ 処理速度検証

  • このエントリーをはてなブックマークに追加

どんなシステムでもトランザクションテーブルにコード値を持たせて、コード値の名称はマスタテーブルに持たせるという方式がスタンダードな方式になっていると思います。

その際にマスタテーブルをINNER JOINして名称を取得する方法と、SELECT句でサブクエリを使用してコード値の名称を取得する方法の2パターンがありますが、処理速度的にどちらが優れているか検証しました。

INNER JOINとサブクエリのコード値の名称取得SQL 処理速度検証

TB_SAMPLEに1,000万レコードが格納されており、カラム「ID」はPKとなっています。

MST_ITEMには10レコードが格納されており、カラム「ITEM_CD」はPKとなっています。

下記は前提条件としてのデータ件数とデータの内容になります・

前提条件

--------------------------------------------
-- トランザクションテーブルのレコード件数
--------------------------------------------
SELECT COUNT(*) FROM TB_SAMPLE;

  COUNT(*)
----------
  10000000

--------------------------------------------
-- コード値毎のレコード件数
--------------------------------------------
SELECT 
  ITEM_CD
 ,COUNT(*)
FROM
  TB_SAMPLE
GROUP BY
  ITEM_CD
ORDER BY
  ITEM_CD
;

   ITEM_CD  COUNT(*)
---------- ----------
         1     998454
         2     999044
         3    1000188
         4    1000279
         5    1001524
         6     999894
         7    1000468
         8    1000246
         9    1000652
        10     999251

--------------------------------------------
-- マスタテーブルの件数
--------------------------------------------
SELECT COUNT(*) FROM MST_ITEM;

  COUNT(*)
----------
        10

--------------------------------------------
-- マスタテーブルの内容
--------------------------------------------
SELECT
  ITEM_CD
 ,ITEM_NM
FROM
  MST_ITEM
;

   ITEM_CD ITEM_NM
---------- --------------------
         1 歯ブラシ
         2 ポテトチップス
         3 アイスカフェラテ
         4 牛丼
         5 カレーライス
         6 からあげくん
         7 メロンパン
         8 えんぴつ
         9 筆箱
        10 オレンジジュース

INNER JOINでコード値の名称を取得するSQL

SELECT
  Z.ITEM_NM
 ,COUNT(*)
FROM
  (
    SELECT
      X.ID
     ,Y.ITEM_NM
    FROM
      TB_SAMPLE X
        INNER JOIN MST_ITEM Y
                ON Y.ITEM_CD = X.ITEM_CD
  ) Z
GROUP BY
  Z.ITEM_NM
;

サブクエリでコード値の名称を取得するSQL

SELECT
  Z.ITEM_NM
 ,COUNT(*)
FROM
(
  SELECT
    X.ID
   ,( SELECT
        Y.ITEM_NM
      FROM
        MST_ITEM Y
      WHERE
        Y.ITEM_CD = X.ITEM_CD
    ) ITEM_NM
  FROM
    TB_SAMPLE X
) Z
GROUP BY
  Z.ITEM_NM
;

INNER JOINとサブクエリの処理速度測定結果

回数/平均 INNER JOIN サブクエリ
1回目9.01秒11.58秒
2回目8.68秒11.88秒
3回目8.40秒11.40秒
平均8.7秒11.6秒

結論としては「INNER JOINでコード値の名称を取得するSQL」の方が処理速度的に優れています。

1,000万レコードで処理測定を行うと3秒ぐらいの違いがでます。

INNER JOINとサブクエリのコード値の名称取得SQL 実行計画 統計情報の比較

INNER JOINでコード値の名称を取得するSQL 実行計画

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    10 |   360 |  5570   (7)| 00:01:07 |
|   1 |  HASH GROUP BY                |             |    10 |   360 |  5570   (7)| 00:01:07 |
|   2 |   MERGE JOIN                  |             |    10 |   360 |  5569   (7)| 00:01:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MST_ITEM    |    10 |   200 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | SYS_C007813 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |    10 |   160 |  5567   (7)| 00:01:07 |
|   6 |     VIEW                      | VW_GBC_9    |    10 |   160 |  5566   (7)| 00:01:07 |
|   7 |      HASH GROUP BY            |             |    10 |    30 |  5566   (7)| 00:01:07 |
|   8 |       TABLE ACCESS FULL       | TB_SAMPLE   |    10M|    28M|  5274   (1)| 00:01:04 |
---------------------------------------------------------------------------------------------

サブクエリでコード値の名称を取得するSQL 実行計画

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10M|   209M|  5566   (7)| 00:01:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MST_ITEM    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007813 |     1 |       |     0   (0)| 00:00:01 |
|   3 |  HASH GROUP BY              |             |    10M|   209M|  5566   (7)| 00:01:07 |
|   4 |   VIEW                      |             |    10M|   209M|  5274   (1)| 00:01:04 |
|   5 |    TABLE ACCESS FULL        | TB_SAMPLE   |    10M|    28M|  5274   (1)| 00:01:04 |
-------------------------------------------------------------------------------------------

実行計画の違い

実行計画の違いは下記の3点になりました。

1. INNER JOINの方がCostが「4」多い

2. INNER JOINの方がBytesが「約208M」少ない

3. INNER JOINの方がマスターテーブルをFULLスキャンしているが、サブクエリの方はINDEXスキャンをしている

実行計画だけを確認するとINNER JOINのBytesが低く、サブクエリのBytesが極端に高いです。

サブクエリのクエリが遅くなる原因はBytesが高いという部分に起因していると読み取れます。

おそらくサブクエリは内部的にレコード件数分だけサブクエリを実行するため、ディスクI/Oがたくさん発生し遅くなるのではと思われます。

統計情報

統計項目 INNER JOIN サブクエリ
recursive calls10
db block gets00
consistent gets1928219294
physical reads1927719277
redo size00
bytes sent via SQL*Net to client928928
bytes received via SQL*Net from client523523
SQL*Net roundtrips to/from client22
sorts (memory)10
sorts (disk)00
rows processed1010

統計項目の違いは下記になりました。

1. INNER JOINの方がrecursive calls(再帰的コールの回数)が「1」多い

2. INNER JOINの方がconsistent gets(一貫性読み込み回数)が「12」少ない

3. INNER JOINの方がsorts(memory)が「1」多い

統計情報の違いはほとんどありません。consistent gets(一貫性読み込み回数)だけ、INNER JOINの方が負荷は少しだけ少ないようです。

INNER JOINとサブクエリのコード値の名称取得SQL 処理速度計測の結論

サブクエリよりもINNER JOINを使った方が処理速度は速いので、なるべくINNER JOINを使った方がよいです。

INNER JOINを使った方が速いという理由は、INNER JOINはディスクI/Oが少なく、サブクエリは極端に多くなるためです。

実行計画のCostが高くてもディクスI/O(Bytes)が低いSQLの方が、処理速度が速いので、実行計画を確認する際はCostだけを確認しても失敗するので気を付けましょう。

  • このエントリーをはてなブックマークに追加

コメントを残す

*