一次性事務和CTE插入資料的比較

有時要構造一些資料來做測試資料，像下面這樣：

IF OBJECT_ID(N'T14') IS NOT NULL
BEGIN
    DROP TABLE T14
END
GO
CREATE TABLE T14 (t14_id INT)
GO

DECLARE @i INT = 1
WHILE @i <= 1000
BEGIN
    INSERT INTO T14 (t14_id)
    SELECT @i
    SET @i = @i + 1
END
GO      

code-1

這裡存在一個問題，每運作一次insert相當于commit了一次事務，資料量小的還不會出現問題，如果把要插入100萬，200萬，1000萬甚至更多的資料呢？既然insert語句是隐式commit的，在這個循環外面加一個顯式的事務，即可顯著提高插入的性能。另一種方法就是使用CTE也可以一次把資料插入到表中，進而提高性能。現在就這兩種方法插入資料的性能來做一個比較。沒有結果之前，猜猜哪種速度更快？或者兩者差不多？

首先是加事務，插入100萬條記錄：

IF OBJECT_ID(N'T14') IS NOT NULL
BEGIN
    DROP TABLE T14
END
GO
CREATE TABLE T14 (t14_id INT)
GO

DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS
GO

SET NOCOUNT ON;
BEGIN TRAN
DECLARE @i INT = 1
WHILE @i <= 1000000
BEGIN
    INSERT INTO T14 (t14_id)
    SELECT @i
    SET @i = @i + 1
END
COMMIT TRAN;
SET NOCOUNT OFF;
GO      

code-2

我的機器上測試多次，取平均值，大概使用了22秒即可完成100萬條記錄的插入，速度還是挺快的。（如果沒有加顯式事務，要多久才能完成呢？有興趣的朋友可以試下

）

下面是使用CTE：

IF OBJECT_ID(N'T15') IS NOT NULL
BEGIN
    DROP TABLE T15
END
GO
CREATE TABLE T15 (t15_id INT)
GO

DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS b
)
,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T15 (t15_id)
SELECT row_no  FROM CTE2 WHERE row_no <= 1000000
GO      

code-3

也是測試多次取平均值，竟然是5秒左右就完成，大大出乎我的意料！現在改為插入1000萬條記錄，看結果如何。前者隻需把code-2中的1000000修改為10000000，再運作即可。後者由于CTE1的記錄數不夠，需要UNION ALL兩次，代碼如下：

IF OBJECT_ID(N'T15') IS NOT NULL
BEGIN
    DROP TABLE T15
END
GO
CREATE TABLE T15 (t15_id INT)
GO

DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS b
UNION ALL
SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS b
UNION ALL
SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS b
)
,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T15 (t15_id)
SELECT row_no  FROM CTE2 WHERE row_no <= 10000000
GO      

code-4

測試結果：加事務的插入大概需要3分多鐘，而CTE則不超過1分半鐘的時間就完成了。看來還是CTE更高效啊！在測試過程中，發現記憶體的使用量不多，但CPU的使用有較明顯的提高。此外，插入大資料到表中，有無索引和日志恢複模式也會影響插入的性能。

-------補充-----

這裡補充一下CTE1中記錄數的生成。如果隻需要100萬的資料量，隻需要master.sys.databases表CROSS JOIN自己一次就可以了，或者找兩張表CROSS JOIN後資料更接近的所需就更好了，不夠的可以UNIONL ALL幾次。那如果需要1000萬或更大的記錄數，可以在此基礎上再CROSS JOIN一次一張小表，比如：

;WITH CTE3 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS b, master.sys.databases AS c
)

SELECT COUNT(*) AS counts,LEN(COUNT(*)) AS counts_length FROM CTE3
GO      

code-5

figure-1

我的機器上生成了1億1多千萬條記錄。