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

1子查询的影响

Nest loop适用于被连接的数据

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

Nested Join适用于结果集较小表

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

示例如下

createtablemoderatetable1(idintidentity(1,1)primarykey,c1int,c2int,c3int,c4int) 
createtablemoderatetable2(idintidentity(1,1)primarykey,c1int,c2int,c3int,c4int) 
declare@nint=0 
while@n<100000 
begin
insertmoderatetable1(c1,c2,c3,c4)values(@n,@n,@n,@n) 
insertmoderatetable2(c1,c2,c3,c4)values(@n,@n,@n,@n) 
set@n+=1 
end
createindexindex1onmoderatetable1(C1) 
createindexindex1onmoderatetable2(C2) 
go 
setstatisticsioon
selectt1.c1frommoderatetable1t1innerjoinmoderatetable2t2 
ont1.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:

selecttop50t1.c1frommoderatetable1t1innerjoinmoderatetable2t2
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经常会估计错误(即使统计信息是正确的).

我们看一下下面的查询:

selecttop500t1.c1frommoderatetable1t1innerjoinmoderatetable2t2 
ont1.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.

selecttop20000t1.c1frommoderatetable1t1innerjoinmoderatetable2t2 
ont1.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 …