SQL Server Execpt和not in 性能区别-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

SQL Server Execpt和not in 性能区别

简介:

网上有很多 except 和 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 行受影响)
表 'tb1'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'tb2'。扫描计数 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 次。
表 'tb2'。扫描计数 3,逻辑读取 1002 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'tb1'。扫描计数 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 出色。

        看执行计划可以得知 如何 在 tb1 和tb2 上建立索引,那么except 的执行计划开可以得到优化。

        

如果大家有兴趣可以看看 not exists 的执行计划。建议:

 

大家不要迷信测试结果,因为所有的性能都是和执行计划密切相关的。而执行计划和统计数据又密不可分。

 

所以过度的迷信测试结果,可能会对生产库造成性能的影响达不到预期的性能效果。

 

 

 

 

 

 

 

 





    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2012/01/16/2323606.html,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章