[MSSQL]搭配TempTable While vs Cursor分別做1,000,000資料逐筆新增的效能測試

  • 2926
  • 0
  • SQL
  • 2020-01-30

本範例刻意將很簡單的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的執行時間會變更長...

參考來源: 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/02c6da90-954d-487d-a823-e24b891ec1b0/how-do-i-check-if-temptable-exists?forum=transactsql

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