In & Exists & Join 分析测试

测试In & Exists & Join 的性能

 

Step 1. 测试环境搭建

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Enterprise Edition (64-bit) on Windows NT 6.1

 

-- IN & EXISTS & INNER JOIN
CREATE TABLE BIG_TB
(
ID        INT IDENTITY PRIMARY KEY,
Col       CHAR(4) NOT NULL
)

CREATE TABLE SMALL_TB
(
ID        INT IDENTITY PRIMARY KEY,
Col       CHAR(4) NOT NULL,
IDate     DATE DEFAULT(GETDATE())
)


INSERT INTO BIG_TB (Col)
SELECT top 250000
char(65+FLOOR(RAND(a.column_id *1111 + b.object_id)*10)) + char(65+FLOOR(RAND(b.column_id *2222 + b.object_id)*12)) +
char(65+FLOOR(RAND(b.column_id *3333 + a.object_id)*12)) + char(65+FLOOR(RAND(a.column_id *4444 + b.object_id)*8))
from master.sys.columns a cross join master.sys.columns b;
GO
--250000


INSERT INTO SMALL_TB (col)
SELECT DISTINCT col
FROM BIG_TB TABLESAMPLE (20 PERCENT);
GO
--2807

Step 2.测试代码

--===================== No Index =====================
SELECT ID, col FROM BIG_TB
WHERE col IN (SELECT col FROM SMALL_TB)

SELECT ID, col FROM BIG_TB
WHERE EXISTS (SELECT col FROM SMALL_TB WHERE SMALL_TB.col = BIG_TB.col)

SELECT A.ID ,A.col FROM BIG_TB A
INNER JOIN SMALL_TB  B ON A.col = B.col


--Not In & Not Exists
SELECT ID, col FROM BIG_TB
WHERE col NOT IN (SELECT col FROM SMALL_TB)

SELECT ID, col FROM BIG_TB
WHERE NOT EXISTS (SELECT col FROM SMALL_TB WHERE SMALL_TB.col = BIG_TB.col)

SELECT A.ID,A.col FROM BIG_TB A
LEFT OUTER JOIN SMALL_TB  B ON A.col = B.col
WHERE B.col IS NULL


--===================== Index =====================
CREATE INDEX idx_BIG_TB_col
ON BIG_TB (col)

CREATE INDEX idx_SMALL_TB_col
ON SMALL_TB (col)


SELECT ID, col FROM BIG_TB
WHERE col IN (SELECT col FROM SMALL_TB)

SELECT ID, col FROM BIG_TB
WHERE EXISTS (SELECT col FROM SMALL_TB WHERE SMALL_TB.col = BIG_TB.col)

SELECT A.ID ,A.col FROM BIG_TB A
INNER JOIN SMALL_TB  B ON A.col = B.col


--Not In & Not Exists
SELECT ID, col FROM BIG_TB
WHERE col NOT IN (SELECT col FROM SMALL_TB)

SELECT ID, col FROM BIG_TB
WHERE NOT EXISTS (SELECT col FROM SMALL_TB WHERE SMALL_TB.col = BIG_TB.col)

SELECT A.ID,A.col FROM BIG_TB A
LEFT OUTER JOIN SMALL_TB  B ON A.col = B.col
WHERE B.col IS NULL

Step 3.测试数据汇总

总结：             在有无Index的前提下，In/Exists及Not In/Not Exists的性能表现相差无几。    而Inner Join/Left Outer Join则相较而言，性能表现上稍微较慢。但注意到Col列    是Not Null属性的。当该列为Nullable状态时，Not Exists与Not In的处理方式将    有所不同，性能表现见下表：

ALTER TABLE BIG_TB
 ALTER COLUMN col char(4)  NULL

ALTER TABLE SMALL_TB
 ALTER COLUMN col char(4)  NULL



SELECT ID, col FROM BIG_TB
WHERE col NOT IN (SELECT col FROM SMALL_TB)

/*
表 'SMALL_TB'。扫描计数 13，逻辑读取 500058 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'BIG_TB'。扫描计数 5，逻辑读取 3992 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 1095 毫秒，占用时间 = 306 毫秒。
*/


SELECT ID, col FROM BIG_TB
WHERE NOT EXISTS (SELECT col FROM SMALL_TB WHERE SMALL_TB.col = BIG_TB.col)
/*
表 'BIG_TB'。扫描计数 1，逻辑读取 3639 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'SMALL_TB'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。


 SQL Server 执行时间:
   CPU 时间 = 265 毫秒，占用时间 = 263 毫秒。
*/

又上述数据可以看出。在处理含有null的数据时，Not exists 的性能将比Not In的性能有很大提升。提升效率大约为5倍左右。