將Execute回傳資料寫入資料表時，會耗費較多的SQL Server資源

2015-12-01

1242
0
[SQL]TSQL語法技巧
2015-12-01

相信很多時候大家會用字串組成動態的SQL語法然後再用Execute來塞入到資料表中

，或是將一些SP回傳資料寫入到資料表後再作運用。但這樣的作法會耗費較多資源歐

日前寫了一些TSQL用來搬資料到Temp Table然後再做後續交易處理。由於只搬3萬多

筆資料，依照以往的經驗應該在4秒內可以完成，但卻花了9秒左右。由於需動態產生

SQL語法再透過該語法的回傳資料寫入Temp Table，所以我用了Execute的方式。

例 :

DECLARE @i INT;
DECLARE @SQL VARCHAR(1000);
SET @i=1;
SET @SQL='SELECT * FROM TABLE WHERE NO=' + @i;
INSERT INTO #TMP EXECUTE(@SQL);

由於搬3萬筆資料花了我2倍時間，所以我做個小LAB來看看用Execute跟不用的差異

有多少呢?本實驗我用AdventureWorks2012資料庫的Person資料表來測試。

以下語法是用 Execute :

CREATE TABLE #tmp(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] [BIT] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL,
	[AdditionalContactInfo] [xml],
	[Demographics] [xml] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL );
GO
SET STATISTICS IO,TIME ON;
GO
Insert Into #tmp Exec('select * from person.person');
GO
SET STATISTICS IO,TIME OFF;
GO
DROP TABLE #tmp;
GO

上述的TSQL執行耗費的資源如下圖，看起來SQL會先將Execute的結果暫存，然後再塞入Temp Table，

形同做兩次工。

下面語法是不要用 Execute :

CREATE TABLE #tmp(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] [BIT] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL,
	[AdditionalContactInfo] [xml],
	[Demographics] [xml] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL );
GO
SET STATISTICS IO,TIME ON;
GO
Insert Into #tmp Select * From person.person;
GO
SET STATISTICS IO,TIME OFF;
GO
DROP TABLE #tmp;
GO

上述語法執行耗費資源如下，可以看見時間花費少了一半，IO更是大大的降低。