性能优化

Question

看一看哪一个效率会更高呢？

select agentid from agent where agentid  not in(select bank_node_id  from LifeExt.dbo.BIS_M_BANK_NODE )

 (select  agentid from agent) except (select bank_node_id from LifeExt.dbo.BIS_M_BANK_NODE)

---

星光总能为我指引方向

Answer 1

declare @LifeExt1 table (id int identity,col int)
declare @i int;set @i=0
while @i<10000
begin
insert into @LifeExt1 select @i
set @i=@i+1
end

declare @LifeExt2 table (id int identity,col int)
declare @j int;set @j=2000
while @j<8000
begin
insert into @LifeExt2 select @j
set @j=@j+1
end

--第一种情况
select col from @LifeExt1 
where col not in ( 
select col from @LifeExt2)
--0 时0 分10 秒16 毫秒

--第二种情况
select col from @LifeExt1
except 
select col from @LifeExt2
--0 时0 分5 秒983 毫秒

我用1万数据测试，结果显示第二种效率好。

Answer 2

时间较长是因为我之前循环插入了2次数据，如果是已经存在的表的话，10000的数据量不会这么久的。

Answer 3

恩，那就是说在同一种要求下，两种语句的执行效率有很大的差别？

从结果中我看出一点问题，not  in 是经过服务器中间处理后按照排序读出结果

这是NOT in 的结果

COL

------------

0
1
2
3
4
5
6
7
8
9
10
11
12

省略

而Except应该是没有经过中间处理后，算出结果的，得出来的数据是无序

这是 Except  结果

COL

------------

0
1589
23
1566
8513
46
215
1374
1543
69
192
1397

省略

---

星光总能为我指引方向

Answer 4

看一看哪一个效率会更高呢？

select agentid from agent where agentid  not in(select bank_node_id  from LifeExt.dbo.BIS_M_BANK_NODE )

 (select  agentid from agent) except (select bank_node_id from LifeExt.dbo.BIS_M_BANK_NODE)

---

星光总能为我指引方向

You can run both of them in same batch then check execution plan, it'll tell you cost of each query.

Answer 5

恩,在机器上执行看到消耗 COST 为

select agentid from agent where agentid  not in(select bank_node_id  from LifeExt.dbo.BIS_M_BANK_NODE )

 

82% 结果有序输出

而

(select  agentid from agent) except (select bank_node_id from LifeExt.dbo.BIS_M_BANK_NODE)

18%输出结果为无序输出

---

星光总能为我指引方向

Answer 6

You can add 'order by' clause to second query then compare costs again, lower is better.

Answer 7

恩最后排序输出 NOT IN  和 Except 各消耗Cost 80%,20%.

---

星光总能为我指引方向

Answer 8

Now you know which one is more efficient.

Answer 9

是的，是的，谢谢阿

---

星光总能为我指引方向

Answer 10

ColumnA not in (1,2,3) = ColumnA <> 1 AND ColumnA <> 2 AND Column <> 3

當 in 的查詢子集越多，整體查詢效能越慢