9 つのテーブルで INNER JOIN を使用する SQL コマンドを作成しましたが、このコマンドには非常に長い時間がかかります (5 分以上)。そのため、私の知っていることにもかかわらず、LEFT JOIN のパフォーマンスの方が優れているため、同僚から INNER JOIN を LEFT JOIN に変更するように勧められました。変更後、クエリの速度が大幅に向上しました。
LEFT JOIN が INNER JOIN よりも高速な理由を知りたいです。
私のSQLコマンドは以下のようになりますSELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D
。
更新:これは私のスキーマの概要です。
FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
ON a.CompanyCd = b.CompanyCd
AND a.SPRNo = b.SPRNo
AND a.SuffixNo = b.SuffixNo
AND a.dnno = b.dnno
INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
ON a.CompanyCd = h.CompanyCd
AND a.sprno = h.AcctSPRNo
INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
ON c.CompanyCd = h.CompanyCd
AND c.FSlipNo = h.FSlipNo
AND c.FSlipSuffix = h.FSlipSuffix
INNER JOIN coMappingExpParty d -- NO PK AND FK
ON c.CompanyCd = d.CompanyCd
AND c.CountryCd = d.CountryCd
INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
ON b.CompanyCd = e.CompanyCd
AND b.ProductSalesCd = e.ProductSalesCd
LEFT JOIN coUOM i -- PK = UOMId
ON h.UOMId = i.UOMId
INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
ON a.CompanyCd = j.CompanyCd
AND b.BFStatus = j.BFStatus
AND b.ProductSalesCd = j.ProductSalesCd
INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
ON e.ProductGroup1Cd = g1.ProductGroup1Cd
INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
ON e.ProductGroup1Cd = g2.ProductGroup1Cd
ベストアンサー1
はLEFT JOIN
より絶対に速いわけではありませんINNER JOIN
。実際、 は より遅いのです。定義により、外部結合 (LEFT JOIN
またはRIGHT JOIN
) は のすべての作業にINNER JOIN
加えて、結果を null 拡張する追加の作業を行う必要があります。また、より多くの行を返すことが予想され、結果セットのサイズが大きいため、合計実行時間がさらに長くなります。
(そして、想像しにくい要因の合流により、特定の状況で が高速にLEFT JOIN
なったとしても、 は と機能的に同等ではないため、単純に のすべてのインスタンスを他の に置き換えることはできません。)INNER JOIN
おそらく、候補キーや外部キーのインデックスが適切に作成されていないなど、パフォーマンスの問題は他の場所にあると考えられます。9 つのテーブルを結合するのはかなり多いため、速度低下は文字通りほぼどこでも発生する可能性があります。スキーマを投稿していただければ、より詳しい情報を提供できる可能性があります。
編集:
LEFT JOIN
これをさらに考えてみると、 がよりも速くなる可能性がある状況が 1 つ考えられますINNER JOIN
。それは次の場合です。
- 一部のテーブルは非常に小さいです (たとえば、10 行未満)。
- テーブルにはクエリをカバーするのに十分なインデックスがありません。
次の例を考えてみましょう。
CREATE TABLE #Test1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')
CREATE TABLE #Test2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')
SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name
SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name
DROP TABLE #Test1
DROP TABLE #Test2
これを実行して実行プランを表示すると、クエリは上記の 2 つの条件を満たしているため、INNER JOIN
確かに よりもコストがかかることがわかります。これは、SQL Server が に対してハッシュ マッチを実行しようとしますが、 に対してはネストされたループを実行するためです。通常、前者の方がはるかに高速ですが、行数が非常に少なく、使用するインデックスがないため、ハッシュ操作がクエリの中で最もコストがかかる部分になります。LEFT JOIN
INNER JOIN
LEFT JOIN
お気に入りのプログラミング言語で、5 つの要素を持つリストと 5 つの要素を持つハッシュ テーブルで多数の検索を実行するプログラムを作成することで、同じ効果を確認できます。サイズのため、ハッシュ テーブル バージョンは実際には遅くなります。ただし、50 要素、または 5000 要素に増やすと、リスト バージョンは O(N) に対してハッシュ テーブルは O(1) であるため、非常に遅くなります。
ID
しかし、このクエリをではなく 列に変更するName
と、まったく異なる状況になります。その場合、両方のクエリに対してネストされたループが実行されますが、このINNER JOIN
バージョンではクラスター化インデックス スキャンの 1 つをシークに置き換えることができます。つまり、行数が多い場合、文字通り桁違いに高速になります。
したがって、結論は、私が数段落前に述べたこととほぼ同じです。これはほぼ間違いなくインデックスまたはインデックス カバレッジの問題であり、1 つ以上の非常に小さなテーブルと組み合わされている可能性があります。SQL Server がよりもに対して悪い実行プランを選択する可能性があるのは、このような状況だけです。INNER JOIN
LEFT JOIN