五餅二魚工作室

之前在跟一些人討論一個 Stored Procedure 效能案例的時候，當時有人提出說是「因為在該程序內，使用到 temp 物件才導致效能不佳，因此要改用 table 變數來取代」。當時雖然覺得這樣的推論並不合理，但因為手邊沒有實際的數據和資料來佐證，因此這個部分當時就先放在自己的待研究區裡面給擱置了。

而最近又剛好要協助同事，進行 Stored Procedure 的效能調教，剛好也是有大量使用 temp 物件的狀況，因此自己就做了一下簡單的測試來比較這兩個的差異。我的測試方式是分別建立兩個 Stored Procedure，而在 Stored Procedure 都同時放入一些相同的 DML 的處理。在所要測試的時間內，來看看分別能處理多少次數，用這樣的方式來比較這兩種不同方式的差異。

下面這個是第一個 Stored Procedure，這個是使用 temp 物件，會傳入一個參數代表測試要執行的時間

CREATE PROCEDURE [dbo].[TestTempObject] ( @Minutes int = 5 )
AS
	CREATE TABLE #AAAA
	(	A1	INT IDENTITY(1,1) PRIMARY KEY,
		A2	VARCHAR(128),
		A3	VARCHAR(128)
	)

	DECLARE @STARTTIME DATETIME;
	DECLARE @ENDTIME DATETIME;
	DECLARE @PTR INT;

	SET @STARTTIME = GETDATE();
	SET @ENDTIME = DATEADD( MI, @Minutes , @STARTTIME );
	SET @PTR = 0;

	SET NOCOUNT ON

	WHILE GETDATE() < @ENDTIME
	BEGIN
		INSERT INTO #AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));
		INSERT INTO #AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));
		INSERT INTO #AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));

		UPDATE #AAAA SET A2 = LEFT( NEWID(),10 ), A3 = RIGHT( NEWID(),10 )

		DELETE #AAAA

		SET @PTR += 1;
	END

	PRINT @PTR;

接下來這個跟上面的 Stored Procedure 類似，但是資料在處理過程中是放在 table 型態的變數內。

CREATE PROCEDURE TestTempVariable( @Minutes int = 5 )
AS
	DECLARE @AAAA TABLE 
	(	A1	INT IDENTITY(1,1) PRIMARY KEY,
		A2	VARCHAR(128),
		A3	VARCHAR(128)
	)

	DECLARE @STARTTIME DATETIME;
	DECLARE @ENDTIME DATETIME;
	DECLARE @PTR INT;

	SET @STARTTIME = GETDATE();
	SET @ENDTIME = DATEADD( MI, @Minutes , @STARTTIME );
	SET @PTR = 0;

	SET NOCOUNT ON

	WHILE GETDATE() < @ENDTIME
	BEGIN
		INSERT INTO @AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));
		INSERT INTO @AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));
		INSERT INTO @AAAA( A2, A3) VALUES ( LEFT( NEWID(),10 ), RIGHT( NEWID(),10 ));

		UPDATE @AAAA SET A2 = LEFT( NEWID(),10 ), A3 = RIGHT( NEWID(),10 )

		DELETE @AAAA

		SET @PTR += 1;
	END

	PRINT @PTR;

接下來我們開始測試，在執行過程中我們利用活動監視器來看相關的資源使用和 tempdb 上的 I/O 狀況，首先我們先在 SSMS 執行，利用 EXEC TestTempObject 10 來進行十分鐘的測試。在執行過程中，可以看到 tempdb 的交易紀錄檔 30MB/寫的速度持續進行。

在持續的 10 分鐘內，會發現 CPU 和 I/O 都一直保持的固定

接下來我們換測試 table 變數的方式，這次利用 EXEC TestTempVariable 10 來進行十分鐘的測試，看起來兩者差異不大，都會寫入到 tempdb 的交易記錄檔內，只是在執行過程中，可以看到 tempdb 的交易紀錄檔 32MB/寫的速度持續進行，比使用 temp 物件的時候稍微高了一點。

看起來似乎只有交易紀錄檔的寫入量有差異，而執行的次數有不一樣嗎 ? 我將執行次數給整理一下做成表格

從上表中看出來，似乎使用 table 變數在執行效率上比較好一點，在大量使用的狀況下，會比使用 temp 物件來的有效率。但為什麼要用「似乎」這兩個不肯定的字眼呢 ? 基本上當我們在做這個測試的時候，會有幾個地方需要再注意一下。

第一個是 CPU 的使用率，看起來在上面的測試過程中，不論使用哪張方式，CPU 的使用率幾乎都在 13% 固定，而利用工作管理員查看，會發現幾乎都只有一個核心在運作， i7-4790 實際上是一個 四核心的 CPU，而且具有 HT ( Hyper-Thread )，在這個的測試過程中因為這些迴圈的處理，都因為只使用單一核心的效能，因為會變成瓶頸在這個部分。

第二個是沒有充分使用到 temp 物件的效益，一般來說，您可以把 table 變數視為一種簡化版的 temp 物件，因此他沒有辦法配合交易去做 ROLLBACK 的處理，也不能像 temp 物件一般去建立其他額外的 Index，或者是直接透過 select into 的方式直接產生。也就是因為減少這些功能，導致在上述的測試過程大量操作下，效能會稍微好一點。但如果需要更多的處理，特別是跟其他資料表做關聯的時候，temp 物件是還可以增加 Index 來提升效能，這一點就是使用 table 變數所無法取代的。

而更重要的一點，不論是使用哪一種方式，這兩種都會對 tempdb 的資料庫進行寫入，也因此在規劃上要注意 tempdb 所在磁碟的效能，以及配合多核心的狀況下，使用多個 tempdb 的資料檔，都會對效能上來說會有不小的影響。

PS. 在 SQL Server 2014 下， table 變數在建立的時候可以建立多個 Index