with recompile

朋友問了一個有趣的問題:同樣的語法在預存程序內跑很慢,單獨跑很快

預存程序會快取執行計畫,一般來說可以省掉 CPU 耗費。但若兩次執行預存程序間,所引用的資料筆數差異很大,則第二次執行沿用舊的預存程序反而效能會很差。

可以觀察這現象:

1. 透過執行完的執行計畫觀察,傳回大量記錄卻還是用索引搜尋

2. 透過 Profiler 觀察預存程序內的語法和單獨執行該語法所耗的 IO/CPU/Duration 的數值,若將某句語法單獨拿到 Management Studio執行的效能遠好於該句話在預存程序內執行,就有可能是上述原因

簡單的解法是在執行或是建立預存程序時搭配 with recompile 選項

範例如下

use tempdb

create table t(c1 int identity,c2 char(900),c3 char(4000))

go

set nocount on

insert t values(‘Hello world’,’Nothing’)

go

insert t(c2,c3) select c2,c3 from t

go 15

create index idx1 on t(c1)

create index idx2 on t(c2)

go

insert t(c2,c3) values(‘Hi’,’HiHi’)

go

create proc sp @i int,@j int

as

select c2,COUNT(*) from

(select * from t where c1 between @i and @j) t

group by c2

go

set statistics io on

–大量資料找少量資料,透過索引很快

exec sp 1,1

–因為執行計畫被快取,導致透過索引在大量資料裡找尋大量資料,效能會很慢

exec sp 1,50000

–在大量資料找大量資料,重做執行計畫

exec sp 1,50000 with recompile

go

create proc sp2

as

select c2,COUNT(*) from

(select * from t where c2 =’hi’) t

group by c2

go

exec sp2

update t set c2=’hi’ where c1<20000

–大量資料變異後,舊的執行計畫錯誤

exec sp2

exec sp2 with recompile

相關細節可以參考悅知出的兩本書 <SQL Server 2005 效能調教> P525 或 <SQL Server 2008 資料庫開發實戰> P255

 

 

小民:我其實對exec sp2 with recompile比較有意見,因為前端程式應該不能這樣呼叫吧? 再者,如果都用 with recompiler,似乎就喪失 sp 的優點了。。。

我:‎1.前端程式可以這樣呼叫,就整句話傳給 SQL Server

2.這要看用途,若預存程序執行情境可以分 80-20 定律,或少數執行狀況 AP 自己知道,則 AP 可以判讀是否要下 with recompile 或是撰寫預存程序直接搭配 Option(Optimize for (參數定義))

但在一些狀況,例如使用者下 Range 查詢,或是財務滾算資料,會大量刪除與新增中繼資料表內的資料,Developer 無法預先評估可能用的資料量大小,則在預存程序建立時,直接搭配 with recompile,可得到較穩定的執行效能。除非預存程序非常大,否則 CPU 找預存程序所耗的資源是較用錯執行計畫少很多的

One Trackback

  1. […] 案例分析 – 同样的语法在存储过程内跑很慢,单独跑很快 (胡百敬, 繁体中文) : https://byronhu.wordpress.com/2010/07/15/with-recompile/ […]

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: