本範例刻意將很簡單的Insert into Select寫法,分別改成Temp Table While寫法(本文將其簡稱While寫法)與Cursor寫法,使用測試兩者的執行速度及效能。
兩者做的事情是一樣的:
- 先於Employee資料表中產生1,000,000筆員工資料 (為了能明顯看出兩者效能差異)
- 將Employee資料表(共4個欄位)中Sex = 'F' 的資料寫入Female資料表(共3個欄位)
Employee資料表及其資料新增語法:
create table Employee
(
recno int identity(1,1),
id varchar(10),
name varchar(100),
sex char(1),
primary key(recno)
)
--插入1000000筆測試資料
declare @to int = 1000000
declare @from int = 1
while(@from <= @to)
begin
insert into Employee values (
RIGHT('0000000' + CAST(@from as varchar) , 7),
'F'+ CAST(@from as varchar),
'F'
)
set @from = @from + 1
end
Female資料表新增語法:
create table Female
(
recno int identity(1,1),
id varchar(10),
name varchar(100),
primary key(recno)
)
測試程式:
set nocount on;
declare @start table
(
RECNO int identity(1,1),
ID varchar(10),
NAME nvarchar(20)
)
declare @CurrentCount int,
@TotalCount int;
insert into @start
select ID, NAME from Employee where sex = 'F';
set rowcount 1; --一次只讀取一筆
select @TotalCount = count(0) from @start;
set @CurrentCount = 0;
begin transaction;
while(@CurrentCount <= @TotalCount)
begin
insert into Female --一次只新增一筆
select ID,NAME from @start;
delete @start; --一次只刪除一筆(用完就刪)
set @CurrentCount = @CurrentCount + 1;
end
commit;
set rowcount 0; --復原
於後面加入Cursor的寫法:
set nocount on;
declare @female_cursor as cursor;
declare @lv_id as varchar(4),
@lv_name as varchar(100);
set @female_cursor = cursor fast_forward for
select ID, NAME from Employee where sex = 'F';
open @female_cursor;
fetch next from @female_cursor into @lv_id , @lv_name;
begin transaction;
while(@@FETCH_STATUS = 0)
begin
insert into Female values(@lv_id , @lv_name)
fetch next from @female_cursor into @lv_id , @lv_name;
end
commit;
close @female_cursor;
deallocate @female_cursor;
開啟「顯示估計執行計畫」,來比較用戶端統計資料,結果如下圖:
從上圖可看出Cursor寫法的「從伺服器收到的TDS封包」會比While寫法高出許多,代表Cursor的寫法較為消耗較多的網路資源及效能。但從「時間統計資料」方面來看,此範例的執行速度較快的寫法Cursor,因此要採取哪一種寫法比較好,還是要依實際的情境去做選擇,是以網路資源為考量? 或是以執行速度為考量? 也要考慮資料量在使用Cursor寫法時,會產生的Lock問題。
再從SQL Profiler的偵測結果來看,於SQL視窗中使用右鍵,來開啟SQL Profiler,如下圖。
欄位說明:(其他說明可參考)
CPU:事件使用的 CPU 時間(毫秒)。
Reads:由服務器代表事件讀取邏輯磁盤的次數。
Writes:由服務器代表事件寫入物理磁盤的次數。
Duration:事件佔用的時間。儘管服務器以微秒計算持續時間,SQL Server Profiler 卻能夠以毫秒爲單位顯示該值。
以上四個欄位,Cursor寫法都較占用CPU資源及物理存取次數,所以就算某些情境下執行速度快,也不能視為最佳做法,還是要經過多方測試或客戶要求,來得到折衷的解法。
補充:
本範例的while寫法並不是很好,因為使用了set rowcount功能,在實際案例中可能更複雜,可能在迴圈中會另外呼叫到其他的Stored procedured,這樣會影響到其內部的更新狀況,因此非常不建議使用這種作法。
建議可以在Temp Table裡面的數字索引當作Key值(如本範例start資料表裡的 recno欄位),以利後續做 select 跟 delete 操作,不過也因此會使用到where語句,額外增加了執行成本,Script的執行時間會變更長...
參考來源:
http://ina-work.blogspot.com/2015/07/stored-procedurecursor.html
https://dotblogs.com.tw/richardnote/2017/12/15/150905
https://ithelp.ithome.com.tw/articles/10200568
https://mssqltaiwan.wordpress.com/2018/05/10/begin-transaction-performance/
https://dotblogs.com.tw/ricochen/archive/2010/11/09/19323.aspx