SQLSERVR语句 in和exists哪个效率高本人测试证明 最近很多人讨论in和exists哪个效率高，今天就自己测试一下 我使用的是客户的数据库GPOSDB(已经有数据) 环境 ：SQLSERVER2005 Windows7 我的测试条件 ：两个表作连接根据VC_IC_CardNO字段，查出CT_InhouseCard
SQLSERVR语句 in和exists哪个效率高本人测试证明
最近很多人讨论in和exists哪个效率高，今天就自己测试一下
我使用的是客户的数据库GPOSDB(已经有数据)
我的测试条件 ：两个表作连接根据VC_IC_CardNO字段，查出CT_InhouseCard表中的VC_IC_CardNO（卡号）在CT_FuelingData表中存在的记录
前提 ：某些人可能在SQL语句中有多个in，或者多个exists，这些情况很难测试效率的，因为大家的条件都不相同
例如下面两个SQL语句
1 SELECT OrderNo, SiteCode, AreaCode 2 FROM SchedulingProgram 3 WHERE AreaCode IN ( ' P ' , ' M ' ) AND SiteCode IN ( SELECT SiteCode 4 FROM EnvBasicInfo 5 WHERE cityiD = 31 ) AND OrderNo NOT IN ( 6 SELECT OrderNo 7 FROM KK_DeliveryinfoTmp )
上面SQL语句IN里面有IN和NOT IN
1 SELECT OrderNo, SiteCode, AreaCode 2 FROM SchedulingProgram 3 WHERE ( AreaCode IN ( ' P ' , ' M ' ) AND SiteCode IN ( SELECT SiteCode 4 FROM EnvBasicInfo 5 WHERE cityiD = 31 ) 6 ) AND NOT EXISTS ( SELECT OrderNo 7 FROM KK_DeliveryinfoTmp 8 WHERE KK_DeliveryinfoTmp.OrderNo = SchedulingProgram.OrderNo )
上面的SQL语句IN里面又有NOT EXISTS
这样的情况很难测试同等条件下IN语句和EXISTS语句的效率
还有一个非SARG运算符
在《SQLSERVER企业级平台管理实践》的第424页里提到：
SQLSERVER对筛选条件（search argument/SARG）的写法有一定的建议
对于不使用SARG运算符的表达式，索引是没有用的，SQLSERVER对它们很难使用比较优化的做法。非SARG运算符包括
NOT、 、NOT EXISTS、NOT IN、NOT LIKE和内部函数，例如：Convert、Upper等
所以当您的表中有索引并且SQL语句包含非SARG运算符，那么当测试SQL语句的执行时间的时候肯定相差很大，
因为有些SQL语句走索引，有些SQL语句不走索引
建表脚本
注意：两个表中都有索引！！
CT_FuelingData表
1 USE [ GPOSDB ] 2 GO 3 /* ***** 对象: Table [dbo].[CT_FuelingData] 脚本日期: 08/24/2013 11:00:34 ***** */ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 SET ANSI_PADDING ON 9 GO 10 CREATE TABLE [ dbo ] . [ CT_FuelingData ] ( 11 [ RecordNO ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , 12 [ I_FD_StationNo ] [ int ] NOT NULL , 13 [ VC_FD_No ] [ varchar ] ( 50 ) NOT NULL , 14 [ VC_FD_Cardno ] [ varchar ] ( 50 ) NOT NULL , 15 [ I_FD_CardStatus ] [ int ] NULL , 16 [ LI_FD_CTC ] [ bigint ] NOT NULL , 17 [ I_FD_TypeCode ] [ int ] NULL , 18 [ I_FD_PumpID ] [ int ] NOT NULL , 19 [ VC_FD_OilType ] [ varchar ] ( 50 ) NULL , 20 [ DE_FD_Volume ] [ decimal ] ( 18 , 2 ) NULL , 21 [ DE_FD_Price ] [ decimal ] ( 18 , 2 ) NULL , 22 [ DE_FD_Amount ] [ decimal ] ( 18 , 2 ) NULL , 23 [ I_FD_Point ] [ decimal ] ( 10 , 2 ) NULL , 24 [ D_FD_DateTime ] [ datetime ] NOT NULL , 25 [ VC_FD_GroupNo ] [ varchar ] ( 50 ) NULL , 26 [ D_FD_GroupDate ] [ datetime ] NULL , 27 [ DE_FD_CardAmount ] [ decimal ] ( 18 , 2 ) NULL , 28 [ DE_FD_VolumeTotals ] [ decimal ] ( 18 , 2 ) NULL , 29 [ DE_FD_AmountTotals ] [ decimal ] ( 18 , 2 ) NULL , 30 [ I_FD_ISSend ] [ int ] NULL , 31 [ VC_FD_CardMoneyauthFile ] [ varchar ] ( 50 ) NULL , 32 [ D_Month ] [ datetime ] NULL , 33 CONSTRAINT [ PK_CT_FuelingData_1 ] PRIMARY KEY CLUSTERED 34 ( 35 [ VC_FD_No ] ASC 36 ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] 37 ) ON [ PRIMARY ] 38 39 GO 40 SET ANSI_PADDING OFFCT_InhouseCard表
1 USE [ GPOSDB ] 2 GO 3 /* ***** 对象: Table [dbo].[CT_InhouseCard] 脚本日期: 08/24/2013 10:59:58 ***** */ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 SET ANSI_PADDING ON 9 GO 10 CREATE TABLE [ dbo ] . [ CT_InhouseCard ] ( 11 [ RecordNO ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , 12 [ VC_IC_CardNO ] [ varchar ] ( 50 ) NOT NULL , 13 [ VC_IC_PhysicalNO ] [ varchar ] ( 50 ) NULL , 14 [ I_IC_CardType ] [ int ] NULL , 15 [ VC_IC_UserName ] [ varchar ] ( 50 ) NULL , 16 [ VC_IC_JobNO ] [ varchar ] ( 50 ) NULL , 17 [ VC_IC_UserID ] [ varchar ] ( 50 ) NULL , 18 [ VC_IC_Password ] [ varchar ] ( 50 ) NULL , 19 [ DE_IC_CardAmount ] [ decimal ] ( 18 , 2 ) NULL , 20 [ DE_IC_AppendAmount ] [ decimal ] ( 18 , 2 ) NULL , 21 [ DE_IC_ConsumerAmount ] [ decimal ] ( 18 , 2 ) NULL , 22 [ I_IC_ISLost ] [ int ] NULL , 23 [ D_IC_UsedDateTime ] [ datetime ] NULL , 24 [ D_IC_UselifeDateTime ] [ datetime ] NULL , 25 [ I_IC_IssueStationNO ] [ int ] NULL , 26 [ VC_IC_IssuerNO ] [ varchar ] ( 50 ) NULL , 27 [ D_IC_IssueDateTime ] [ datetime ] NULL , 28 [ D_IC_LastUpdateDateTime ] [ datetime ] NULL , 29 [ I_IC_CardStatus ] [ int ] NULL , 30 [ VC_IC_Remark ] [ varchar ] ( 256 ) NULL , 31 CONSTRAINT [ PK_CT_InhouseCard ] PRIMARY KEY CLUSTERED 32 ( 33 [ VC_IC_CardNO ] ASC 34 ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] 35 ) ON [ PRIMARY ] 36 37 GO 38 SET ANSI_PADDING OFF
测试脚本
因为这个是客户的数据库，本来里面已经有数据了，所以在测试之前先更新两个表的统计信息，以做到公正
1 USE [ GPOSDB ] 2 GO 3 UPDATE STATISTICS CT_FuelingData 4 UPDATE STATISTICS CT_InhouseCard 5 GO
IN语句
1 USE [ GPOSDB ] 2 GO 3 DBCC DROPCLEANBUFFERS 4 GO 5 DBCC FREEPROCCACHE 6 GO 7 SET STATISTICS IO ON 8 GO 9 SET STATISTICS TIME ON 10 GO 11 SET STATISTICS PROFILE ON 12 GO 13 SELECT * FROM [ dbo ] . [ CT_FuelingData ] WHERE [ VC_FD_Cardno ] IN ( SELECT [ VC_IC_CardNO ] FROM [ dbo ] . [ CT_InhouseCard ] )EXISTS语句
1 USE [ GPOSDB ] 2 GO 3 DBCC DROPCLEANBUFFERS 4 GO 5 DBCC FREEPROCCACHE 6 GO 7 SET STATISTICS IO ON 8 GO 9 SET STATISTICS TIME ON 10 GO 11 SET STATISTICS PROFILE ON 12 GO 13 SELECT * 14 FROM [ dbo ] . [ CT_FuelingData ] 15 WHERE EXISTS ( SELECT [ VC_IC_CardNO ] 16 FROM [ dbo ] . [ CT_InhouseCard ] 17 WHERE [ dbo ] . [ CT_FuelingData ] . [ VC_FD_Cardno ] = [ dbo ] . [ CT_InhouseCard ] . [ VC_IC_CardNO ] )
测试结果
IN语句
1 SQL Server 执行时间: 2 CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。 3 SQL Server 分析和编译时间: 4 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 5 6 SQL Server 执行时间: 7 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 8 SQL Server 分析和编译时间: 9 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 10 11 SQL Server 执行时间: 12 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 13 SQL Server 分析和编译时间: 14 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 15 16 SQL Server 执行时间: 17 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 18 SQL Server 分析和编译时间: 19 CPU 时间 = 31 毫秒，占用时间 = 67 毫秒。 20 21 ( 167 行受影响) 22 表 ' Worktable ' 。扫描计数 0 ，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 23 表 ' CT_FuelingData ' 。扫描计数 1 ，逻辑读取 31 次，物理读取 1 次，预读 64 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 24 表 ' CT_InhouseCard ' 。扫描计数 1 ，逻辑读取 2 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 25 26 ( 4 行受影响) 27 28 SQL Server 执行时间: 29 CPU 时间 = 16 毫秒，占用时间 = 192 毫秒。
EXISTS语句
1 SQL Server 分析和编译时间: 2 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 3 4 SQL Server 执行时间: 5 CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 6 SQL Server 分析和编译时间: 7 CPU 时间 = 0 毫秒，占用时间 = 34 毫秒。 8 9 ( 167 行受影响) 10 表 ' Worktable ' 。扫描计数 0 ，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 11 表 ' CT_FuelingData ' 。扫描计数 1 ，逻辑读取 31 次，物理读取 1 次，预读 64 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 12 表 ' CT_InhouseCard ' 。扫描计数 1 ，逻辑读取 2 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 13 14 ( 4 行受影响) 15 16 SQL Server 执行时间: 17 CPU 时间 = 0 毫秒，占用时间 = 163 毫秒。大家可以看到除了执行时间有一点差别，IO是一样的
因为数据量比较大，所以两个查询都用到了Worktable（中间表）来存储中间结果
IN语句的执行计划
EXISTS语句的执行计划
从执行计划可以看到两个SQL语句的开销都是一样的，而且大家都使用了右半连接（Right Semi Join）
至于什么是半连接（Semi-join）大家可以看一下这篇文章： SQL Join的一些总结
总结
从上面实际的执行来比较,，IN语句和EXISTS语句基本上都是一样的效率
如有不对的地方，欢迎大家来拍砖o(∩_∩)o
查看更多关于SQLSERVER语句in和exists哪个效率高本人测试证明的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did98936