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

2019-11-27

2939
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，如下圖。