【SQL】複数テーブル間でのOR条件検索のアンチパターンと改善方法

はじめに

複数テーブル間にまたがるようなOR条件でレコードを抽出する際、パフォーマンス上適切でないテーブル結合を行っているケースにしばしば遭遇します。
ここでは、そのアンチパターンと改善方法を示します。

TL;DR

• 検索条件に関連するテーブルを一度に全て結合するべきでない
• 各条件に該当するレコードを個別に抽出し、最後にその和集合を取得すべき

例

社内SEのKさんが、人事部から時間外手当の支給実績者の抽出依頼を受け、以下のような要件が発生したとします。

• 在籍社員を管理する在籍社員テーブルがある
• 深夜残業履歴・法定休日出勤履歴・所定休日出勤履歴を管理する３テーブルがある
• 上記の３テーブルは、在籍社員テーブルと社員番号で紐づき、複数の実施年月日を持つ
  というようなテーブル群に対し、
  深夜残業履歴または法定休日出勤履歴または所定休日出勤履歴のある社員番号を、在籍社員テーブルから抜き出す

在籍社員テーブルの行数（在籍社員数）は10,000人（件）、1人あたりの深夜残業の平均件数は100件、法定休日出勤と所定休日出勤の平均回数はそれぞれ20件とします。

ER図

在籍社員テーブル（参照元X）

社員番号	氏名
000001	田中　太郎
000002	山田　花子
・・・	・・・

深夜残業履歴テーブル（参照先A）

社員番号	実施年月日
000001	20230405
000001	20230406
・・・	・・・

法定休日出勤履歴テーブル（参照先B）

社員番号	実施年月日
000001	20230409
000002	20230416
・・・	・・・

所定休日出勤履歴テーブル（参照先C）

社員番号	実施年月日
000001	20230408
000001	20230415
・・・	・・・

アンチパターン

以下のように、条件検索に必要なテーブルをすべて外部結合した上で、条件指定するクエリはパフォーマンスの観点から適切ではありません。

SQL(SQLSERVER)

SELECT DISTINCT X.社員番号
FROM 在籍社員 X
LEFT JOIN　深夜残業履歴　A
 ON X.社員番号 = A.社員番号
LEFT JOIN　法定休日出勤履歴　B
 ON X.社員番号 = B.社員番号
LEFT JOIN　所定休日出勤履歴　C
 ON X.社員番号 = C.社員番号
WHERE A.社員番号 IS NOT NULL
 OR B.社員番号 IS NOT NULL
 OR C.社員番号 IS NOT NULL

テーブルXに対して、テーブルAは複数レコードがヒットする場合があります。
また、同様にテーブルBも複数、テーブルCも複数ヒットします。
そのため、内部的には、テーブルA×B×C分のレコードが作成されたのち、Xの社員番号の重複を排除する操作が走ることになります。

テーブルX1行に対してヒットするテーブルA～Cの行数が少ない場合は、パフォーマンス上大きな課題にはなりませんが、A～Cの行数が大きい場合、処理時間への影響が大きくなります。

テーブルXの行数（在籍社員数）が10,000人、1人あたりの深夜残業の平均回数が100回、法定休日出勤、所定休日出勤の平均回数がそれぞれ20回であるため、10,000×100×20×20=400,000,000と、約4億件のレコードに対して重複を除く操作が走ることなり、パフォーマンスは大幅に劣化します。（DISTINCTはソートを要するため、処理は重い）

また、仮に要件追加によって、参照するテーブルD（例えば、長時間残業実施者履歴、等）が追加されることになると、4億件のレコード×テーブルDのレコード数分、処理対象が増加するため、指数関数的に処理時間が増加することになります。

どうすればよいか

各条件に該当するテーブルをそれぞれ個別に内部結合し、各結果の和集合をとる方が、多くの場合、パフォーマンスは良好になります。（テーブルXに対して、ヒットするA～Cのレコード数が多いほど、パフォーマンスの差が顕著になります。）

SQL(SQLSERVER)

SELECT DISTINCT X.社員番号 INTO #T1
FROM 在籍社員 X
INNER JOIN　深夜残業履歴　A
 ON X.社員番号 = A.社員番号

SELECT DISTINCT X.社員番号 INTO #T2
FROM 在籍社員 X
INNER JOIN　法定休日出勤履歴　B
 ON X.社員番号 = B.社員番号

SELECT DISTINCT X.社員番号 INTO #T3
FROM 在籍社員 X
INNER JOIN　所定休日出勤履歴　C
 ON X.社員番号 = C.社員番号

SELECT 社員番号 FROM #T1
UNION　
SELECT 社員番号 FROM #T2
UNION　
SELECT 社員番号 FROM #T3

この場合、

①テーブルAに対する操作　10,000×100=100万件の重複排除処理
②テーブルBに対する操作　10,000×20=20万件の重複排除処理
③テーブルCに対する操作　10,000×20=20万件の重複排除処理

の操作が走った後、上記の処理結果の①～③それぞれ最大でも10,000件×3=30,000件と、高々3万件のレコードに対する重複排除処理が走ることになります。

メモリや一時テーブルへの格納コストや、複数回の重複排除の操作が必要になりますが、大量データに対する重複排除処理よりはコストが低い場合が多く、このように処理を分割した方が、パフォーマンスが良好となることが多いでしょう。