说说Top子句对查询计划的影响

1子查询的影响

Nest loop适用于被连接的数据

如果两个表做join操作,会有三种join方式: Nested join, Merge Join, Hash Join

Nested Join适用于结果集较小表

Hash Join适用于结果集很大的表

示例如下

create table moderatetable1(id int identity(1,1) primary key, c1 int ,c2 int,c3 int,c4 int)  
create table moderatetable2(id int identity(1,1) primary key, c1 int ,c2 int,c3 int,c4 int)  
declare @n int=0  
while @n<100000  
begin 
insert moderatetable1(c1,c2,c3,c4) values(@n,@n,@n,@n)  
insert moderatetable2(c1,c2,c3,c4) values(@n,@n,@n,@n)  
set @n+=1  
end 
create index index1 on moderatetable1(C1)  
create index index1 on moderatetable2(C2)  
go  
set statistics io on 
select t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
on t1.c1=t2.c1  
go 

下图是上面查询的执行计划和io统计信息

IO情况

(100000 行受影响)

表 ‘Worktable’。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable2’。扫描计数 1，逻辑读取 361 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable1’。扫描计数 1，逻辑读取 176 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

如果只想取前50行,可以指定top 50:

select top 50 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2 
on t1.c1=t2.c1

(50 行受影响)

表 ‘moderatetable1’。扫描计数 50，逻辑读取 124 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable2’。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

我们看到,当指定了top 50之后,查询计划成了nested join. 当使用TOP时,SQLSEVER会认为这是一个较小的数据集,所以会使用nested join.对于这个查询,IO的开销比较李小. 但SQLSERVER经常会估计错误(即使统计信息是正确的).

我们看一下下面的查询:

select top 500 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
on t1.c1=t2.c1 
 

(500 行受影响)

表 ‘moderatetable1’。扫描计数 500，逻辑读取 1080 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable2’。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

返回行数增加到了500,SQLSERVER仍然使用nested join,得到了较差的IO

随着TOP的行数的增多,IO开销会越来越大. 但也不是总是这样,当top值达到一个临界点后,执行计划会变更成hash join.

select top 20000 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
on t1.c1=t2.c1 

(20000 行受影响)

表 ‘Worktable’。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable2’。扫描计数 1，逻辑读取 74 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 ‘moderatetable1’。扫描计数 1，逻辑读取 176 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

慎用TOP …

原文链接：http://www.cnblogs.com/stswordman/archive/2011/06/14/2080396.html

【编辑推荐】

1. 分析TOP语句放到表值函数外，效率异常低下的原因
2. 双TOP二分法生成分页SQL类
3. SQL Server数据库中简单的SELECT TOP
4. 利用top构造Sql Server分页查询