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

  • 27253
  • 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