1子查詢的影響

Nest loop適用于被連接的數(shù)據(jù)

如果兩個(gè)表做join操作,會(huì)有三種join方式: Nested join, Merge Join, Hash Join

Nested Join適用于結(jié)果集較小表

Hash Join適用于結(jié)果集很大的表

示例如下

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í)行計(jì)劃和io統(tǒng)計(jì)信息

IO情況

(100000 行受影響)

表 'Worktable'。掃描計(jì)數(shù) 0，邏輯讀取 0 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable2'。掃描計(jì)數(shù) 1，邏輯讀取 361 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable1'。掃描計(jì)數(shù) 1，邏輯讀取 176 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

(1 行受影響)

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

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

(50 行受影響)

表 'moderatetable1'。掃描計(jì)數(shù) 50，邏輯讀取 124 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable2'。掃描計(jì)數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

(1 行受影響)

我們看到,當(dāng)指定了top 50之后,查詢計(jì)劃成了nested join. 當(dāng)使用TOP時(shí),SQLSEVER會(huì)認(rèn)為這是一個(gè)較小的數(shù)據(jù)集,所以會(huì)使用nested join.對(duì)于這個(gè)查詢,IO的開(kāi)銷比較李小. 但SQLSERVER經(jīng)常會(huì)估計(jì)錯(cuò)誤(即使統(tǒng)計(jì)信息是正確的).

我們看一下下面的查詢:

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

(500 行受影響)

表 'moderatetable1'。掃描計(jì)數(shù) 500，邏輯讀取 1080 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable2'。掃描計(jì)數(shù) 1，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

(1 行受影響)

返回行數(shù)增加到了500,SQLSERVER仍然使用nested join,得到了較差的IO

隨著TOP的行數(shù)的增多,IO開(kāi)銷會(huì)越來(lái)越大. 但也不是總是這樣,當(dāng)top值達(dá)到一個(gè)臨界點(diǎn)后,執(zhí)行計(jì)劃會(huì)變更成hash join.

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

(20000 行受影響)

表 'Worktable'。掃描計(jì)數(shù) 0，邏輯讀取 0 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable2'。掃描計(jì)數(shù) 1，邏輯讀取 74 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

表 'moderatetable1'。掃描計(jì)數(shù) 1，邏輯讀取 176 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

(1 行受影響)

慎用TOP ...

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

【編輯推薦】

1. 分析TOP語(yǔ)句放到表值函數(shù)外，效率異常低下的原因
2. 雙TOP二分法生成分頁(yè)SQL類
3. SQL Server數(shù)據(jù)庫(kù)中簡(jiǎn)單的SELECT TOP
4. 利用top構(gòu)造Sql Server分頁(yè)查詢