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 

下圖是上面查詢的執(zhí)行計劃和io統(tǒng)計信息

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經常會估計錯誤(即使統(tǒng)計信息是正確的).

我們看一下下面的查詢:

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值達到一個臨界點后,執(zhí)行計劃會變更成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分頁查詢