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