SQLServer Execptとnot inのパフォーマンスの違い

主にexceptとnot inの性能の違いを話します.

 
  
CREATE TABLE tb1(ID int) 
  
CREATE TABLE tb2(ID int) 
  
BEGIN TRAN 
  
DECLARE @i INT = 500 
  
WHILE @i > 0 
  
begin 
  
INSERT INTO dbo.tb1 
  
VALUES ( @i -- v - int 
  
) 
  
SET @i = @i -1 
  
end 
  
COMMIT tb1  1000，tb2  500 
  
 
 

 
  
DBCC FREESYSTEMCACHE ('ALL','default'); 
  
SET STATISTICS IO ON 
  
SET STATISTICS TIME on 
  
SELECT * FROM tb1 EXCEPT SELECT * FROM tb2; 
  
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);--  
  
SET STATISTICS IO OFF 
  
SET STATISTICS TIME OFF 
  
 
 

実行計画:

 
  
SELECT * FROM tb1 EXCEPT SELECT * FROM tb2; 
  
|--Merge Join(Right Anti Semi Join, MERGE:([master1].[dbo].[tb2].[ID])=([master1].[dbo].[tb1].[ID]), RESIDUAL:([master1].[dbo].[tb1].[ID] = [master1].[dbo].[tb2].[ID])) 
  
|--Sort(DISTINCT ORDER BY:([master1].[dbo].[tb2].[ID] ASC)) 
  
| |--Table Scan(OBJECT:([master1].[dbo].[tb2])) 
  
|--Sort(DISTINCT ORDER BY:([master1].[dbo].[tb1].[ID] ASC)) 
  
|--Table Scan(OBJECT:([master1].[dbo].[tb1])) 
  
 
 

 
  
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);--  
  
|--Hash Match(Right Anti Semi Join, HASH:([master1].[dbo].[tb2].[ID])=([master1].[dbo].[tb1].[ID]), RESIDUAL:([master1].[dbo].[tb1].[ID]=[master1].[dbo].[tb2].[ID])) 
  
|--Table Scan(OBJECT:([master1].[dbo].[tb2])) 
  
|--Nested Loops(Left Anti Semi Join) 
  
|--Nested Loops(Left Anti Semi Join, WHERE:([master1].[dbo].[tb1].[ID] IS NULL)) 
  
| |--Table Scan(OBJECT:([master1].[dbo].[tb1])) 
  
| |--Top(TOP EXPRESSION:((1))) 
  
| |--Table Scan(OBJECT:([master1].[dbo].[tb2])) 
  
|--Row Count Spool 
  
|--Table Scan(OBJECT:([master1].[dbo].[tb2]), WHERE:([master1].[dbo].[tb2].[ID] IS NULL)) 
  
 
 

SQL Serverの実行時間:
CPU時間=0ミリ秒、占有時間=0ミリ秒.
(500行の影響)
表'tb 1'です.スキャンカウント1、論理読み出し2回、物理読み出し0回、プリリード0回、lob論理読み出し0回、lob物理読み出し0回、lobプリリード0回.
表'tb 2'です.スキャンカウント1、論理読み出し1回、物理読み出し0回、プリリード0回、lob論理読み出し0回、lob物理読み出し0回、lobプリリード0回.
(6行の影響)
(1行に影響)
SQL Serverの実行時間:
CPU時間=0ミリ秒、占有時間=528ミリ秒.
(500行の影響)
表'Worktable'です.スキャンカウント0、論理読み出し0回、物理読み出し0回、プリリード0回、lob論理読み出し0回、lob物理読み出し0回、lobプリリード0回.
表'tb 2'です.スキャンカウント3、論理読み出し1002回、物理読み出し0回、プリリード0回、lob論理読み出し0回、lob物理読み出し0回、lobプリリード0回.
表'tb 1'です.スキャンカウント1、論理読み出し2回、物理読み出し0回、プリリード0回、lob論理読み出し0回、lob物理読み出し0回、lobプリリード0回.
(10行の影響)
(1行に影響)
SQL Serverの実行時間:
CPU時間=16ミリ秒、占有時間=498ミリ秒.
SQL Serverの実行時間:
CPU時間=0ミリ秒、占有時間=0ミリ秒.
結論:より多くのデータとより少ないデータのテストにより、より少ないデータの場合、not inはexceptより性能が良いが、より多くのデータの場合、execptはnot inより優れている.
実行計画を見ると、tb 1とtb 2にインデックスを作成する方法がわかり、exceptの実行計画が最適化されます.
興味があればnot existsの実行計画を見てみましょう.推奨事項:
すべてのパフォーマンスは実行計画と密接に関連しているので、テスト結果を迷信しないでください.実行計画と統計データは密接に分かれている.
したがって、過度な迷信テストの結果は、本番ライブラリにパフォーマンスに与える影響が予想されるパフォーマンス効果に達しない可能性があります.