能用迴圈,不要用重覆T-SQL語法

碰到同仁寫了一個 8 千行的預存程序,但其中有很多是重覆的T-SQL語法,僅是參數條件不同,但以複製貼上並修改 Where 條件。結果找執行計畫花了 2 分多鐘,但實際執行僅花 40 多秒鐘,我以如下的 Sample 模擬:

use tempdb
–產生範例資料庫
–drop table t
create table t(c1 int identity(1,1) primary key,c2 datetime2 default(sysdatetime()),c3 int)
–湊出 8000 句僅參數不同的語法
set nocount on
declare @i int=0
while @i<8000
begin
    insert t default values
    print ‘update t set c3=(select c1 from t where c1=’ + convert(varchar(4),@i) + ‘) where c1=’ + convert(varchar(4),@i)
    set @i+=1
end

而後 Copy 產生出來的語法執行,如下:

set nocount on
–8000 句簡單的 T-SQL 語法
select ‘送出批次的時間’,sysdatetime()
go
select ‘找到執行計畫並開始執行的時間’,sysdatetime()
update t set c3=(select c1 from t where c1=0) where c1=0
update t set c3=(select c1 from t where c1=1) where c1=1

update t set c3=(select c1 from t where c1=7998) where c1=7998
update t set c3=(select c1 from t where c1=7999) where c1=7999
select ‘執行執行計畫的時間’,sysdatetime()
GO

–改以迴圈執行 8000 次
select ‘送出批次的時間’,sysdatetime()
go
select ‘找到執行計畫並開始執行的時間’,sysdatetime()
declare @i int=0
while @i<8000
begin
    update t set c3=(select c1 from t where c1=@i) where c1=@i
    set @i+=1
end
select ‘執行執行計畫的時間’,sysdatetime()

結果如下:

image

可以發現 SQL Server 花了 1 分 6 秒鐘找 8 千句簡單語法的執行計畫,花了2分6秒鐘執行。但若改以迴圈做這重複的動作,僅有 0.13 秒找執行計畫,並執行了1.1 秒鐘

 

 

後來發現,因為 Table 結構定義,讓他非用多行語法不可…慘傷心

發表迴響

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

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 位部落客按了讚: