SQL Server中INNER JOIN与子查询IN的性能测试

这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣?”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣(IN是子查询的实现方式之一,本篇还是只对比INNER JOIN与子查询IN的性能,如果展开INNER JOIN与子查询性能对比,范围太大了,没法一一详述)。下面这篇文章,我们就INNER JOIN与子查询IN这两种写法孰优孰劣,在不同场景下进行一下测试对比一下,希望能解答你心中的疑惑。

下面例子以AdventureWorks2014为测试场景,测试表为Sales.SalesOrderHeader与Sales.SalesOrderDetail。 如下所示:

 

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

 

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT  h.* FROM 

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail)

SQL Server中INNER JOIN与子查询IN的性能测试

SQL Server中INNER JOIN与子查询IN的性能测试

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

如下所示,两种写法的SQL的实际执行计划是几乎一致。而且对比IO开销也是一致。cpu time 与elapsed time 有所差别,这个是因为两者返回的数据有所差别的缘故(SQL 1 返回 31465行数据, SQL 2返回 121317行数据),两者在逻辑上实际上是不一致的。因为重复数据的缘故。撇开这个不谈,光从性能上来考察两种,它们几乎是一模一样。没有优劣之分。

SQL Server中INNER JOIN与子查询IN的性能测试

SQL Server中INNER JOIN与子查询IN的性能测试

如果有人对上面的重复数据不明白的话,下面做个简单的例子演示给大家看看。如下所示,截图中INNER JOIN就会有重复数据。

CREATE TABLE P

(

    PID    INT ,

    Pname  VARCHAR(24)

)

 

INSERT INTO dbo.P

SELECT 1, 'P1' UNION ALL

SELECT 2, 'P2' UNION ALL

SELECT 3, 'P3'

 

 

CREATE TABLE dbo.C

(

    CID       INT ,

    PID       INT ,

    Cname  VARCHAR(24)

)

 

INSERT INTO dbo.c

SELECT 1, 1, 'C1' UNION ALL

SELECT 2, 1, 'C2' UNION ALL

SELECT 3, 2, 'C3' UNION ALL

SELECT 3, 3, 'C4'

SQL Server中INNER JOIN与子查询IN的性能测试

其实下面SQL在逻辑上才是相等的,它们的实际执行计划与IO是一样的。没有优劣之分。

SELECT  h.* FROM 

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail);

 

 

SELECT DISTINCT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID;

SQL Server中INNER JOIN与子查询IN的性能测试

那么我们再来看另外一个例子,测试一下两者的性能差别。如下所示

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT  C.*

FROM    Sales.Customer C

        INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID;

 

 

SELECT  C.*

FROM    Sales.Customer C

WHERE  C.PersonID IN ( SELECT Person.Person.BusinessEntityID

                                     FROM   Person.Person );

INNER JOIN与子查询IN的实际执行计划对比的百分比为66% VS 34% , 子查询IN的性能还比 INNER JOIN的性能要好一些. IO几乎无差别,cpu time 与elapsed time的对比情况来看,子查询IN的性能确实要好一些。

这个是因为子查询IN在这个上下文环境中,它使用右半连接(Right Semi Join)方式的Hash Match,即一个表中返回的行与另一个表中数据行进行不完全联接查询(查找到匹配的数据行就返回,不再继续查找)。那么可以肯定的是,在这个场景(上下文)中,子查询IN这种方式的SQL的性能比INNER JOIN 这种写法的SQL要好。

SQL Server中INNER JOIN与子查询IN的性能测试

SQL Server中INNER JOIN与子查询IN的性能测试

那么我们再来看一个INNER JOIN性能比子查询(IN)要好的案例。如下所示,我们先构造测试数据。

CREATE TABLE P

(

    P_ID    INT IDENTITY(1,1),

    OTHERCOL        CHAR(500),

    CONSTRAINT PK_P PRIMARY KEY(P_ID)

)

GO

 

BEGIN TRAN

DECLARE @I INT = 1

WHILE @I<=10000

BEGIN

    INSERT INTO P VALUES (NEWID())

    SET @I = @I+1

    IF (@I%500)=0

    BEGIN

        IF @@TRANCOUNT>0

        BEGIN

            COMMIT

            BEGIN TRAN

        END

    END

END

IF @@TRANCOUNT>0

BEGIN

    COMMIT

END

GO

 

 

CREATE TABLE C 

(

    C_ID  INT IDENTITY(1,1) ,

    P_ID   INT  FOREIGN KEY REFERENCES P(P_ID),

    COLN  CHAR(500),

    CONSTRAINT PK_C  PRIMARY KEY (C_ID) 

)

 

 

 

 

SET NOCOUNT ON;

 

DECLARE @I INT = 1

WHILE @I<=1000000

BEGIN

    INSERT INTO C VALUES ( CAST(RAND()*10 AS INT)+1,  NEWID())

    SET @I = @I+1

END

GO

构造完测试数据后,我们对比下两者的性能差异

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT C.* FROM dbo.C C

INNER JOIN dbo.P  P ON C.P_ID = P.P_ID

WHERE P.P_ID=8

 

 

SELECT * FROM dbo.C

WHERE P_ID IN (SELECT P_ID FROM dbo.P WHERE P_ID=8)

SQL Server中INNER JOIN与子查询IN的性能测试

SQL Server中INNER JOIN与子查询IN的性能测试

增加对应的索引后,这个性能差距更更明显。 如下截图所示

 

USE [AdventureWorks2014]

GO

CREATE NONCLUSTERED INDEX [IX_C_N1]

ON [dbo].[C] ([P_ID])

INCLUDE ([C_ID],[COLN])

GO

SQL Server中INNER JOIN与子查询IN的性能测试

在生产环境遇到一个案例, 两个视图使用INNER JOIN 与 IN 两种写法,在性能上差距很大。 使用子查询IN的性能比使用INNER JOIN的性能要好很多。如下截图所示。因为视图里面涉及多表。这样肯定导致执行计划非常复杂,导致SQL用INNER JOIN 的写法在性能上没有用子查询IN的写法要快

SQL Server中INNER JOIN与子查询IN的性能测试

其实一部分情况下,INNER JOIN 与 子查询IN都是等价的。因为SQL Server优化器已经足够聪明,能够进行一些内部转换,生成等价的计划。但是在某一些特殊场景下,各有优劣。不能武断的就说INNER JOIN在性能上要比子查询IN要好。一定要结合上下文环境具体来谈性能优劣。否则没有多大意义。另外,子查询可以分为相关子查询和无关子查询,对于无关子查询来说,Not In子句比较常见,但Not In潜在会带来两种问题,结果不正确和性能问题,具体可以参考在SQL Server中为什么不建议使用Not In子查询

上一篇:ASP.net发布项目引用了C++DLL后页面提示找不到指定模块的异常


下一篇:【unittest单元测试框架】(4)HTML 测试报告