[SQL Server] 使用cursor與TempTable逐步讀取資料列之效能差異

2017-12-15

27257
0
2017-12-21

stored procedure中不使用cursor逐步讀取資料列的方法

兩者流程圖大致差異如下

測試環境背景：

DB ：SQL server Enterprise Edition (64-bit) 10.50.2550.0

OS ：Windows server 2008 R2

CPU ：16 Cores

RAM：48GB

運算方式：

Join來源資料TableA與TableB的資料，更新/插入至TableC

跑迴圈的次數共17599筆資料

A、B表資料型態皆為 ↓

A	int
B	datetime
C	nvarchar(200)
D	nvarchar(100)
E	nvarchar(100)

Cursor程式碼：

declare @A int,
declare @B datetime,
declare @C nvarchar(200),
declare @D nvarchar(100),
declare @E nvarchar(100)
--準備跑迴圈的資料放入cursor
declare crs cursor for 
        select A,B,C,D,E 
        from tableA a right join TableB b on a.A = b.A 
        where a.A is not null

open crs
fetch next from crs into @A, @B, @C, @D, @E
while(@@fetch_status != -1)
begin
    if( Exists(select * from TableC where A = @A and B = @B))
    begin
        update TableC set
                B = @B,
                C = @C,
                D = @D + @E
        where A = @A and B = @B
    end
    else
    begin
        insert into TableC(A, B, C, D)
        values(@A, @B, @C, @D + @E)
    end
    fetch next from crs into @A, @B, @C, @D, @E
end
close crs
deallocate crs

TempTable程式碼：

declare @temp table
(
    A int,
    B datetime,
    C nvarchar(200),
    D nvarchar(100),
    E nvarchar(100)
)
declare @A int,
declare @B datetime,
declare @C nvarchar(200),
declare @D nvarchar(100),
declare @E nvarchar(100)
--準備跑迴圈的資料insert到暫存表
Insert into @temp(A, B, C, D, E)
        select A, B, C, D, E
        from TableA a right join TableB b on a.A = b.B
        where a.A is not null
--計算tempTable資料數量
select @countTemp = count(*) from @temp
--讀取動作設定為1列
set rowcount 1

while(@countTemp > 0)
begin
    --抓最上列的TempTable資料
    select @A = A, @B = B, @C = C, @D = D, @E = E from @temp
    --Do somethings
    if( Exists(select * from TableC where A = @A and B = @B))
    begin
        update TableC 
        set B = @B,
            C = @C,
            D = @D + @E
        where A = @A and B = @B
    end
    else
    begin
        insert into TableC(A, B, C, D)
        values(@A, @B, @C, @D + @E)
    end
    --因設定rowcount為1，只會刪除temp最上一列資料
    delete from @temp
    --重新計算tempTable資料數量
    select @countTemp = count(*) from @temp
end
--改回讀取設定為不限列
set rowcount 0

ps. 測試時資料都已先匯入，只會跑update的地方

最後結果：

Cursor花費時間153秒

TempTable花費時間111秒

(153-111) / 111 = 37.83%

結論：

在此Case下，Cursor花費的時間會多出TempTable迴圈約37%

Rererence：

1.[程式] stored procedure中不使用cursor逐步讀取資料列的方法

http://ina-work.blogspot.tw/2015/07/stored-procedurecursor.html

回首頁