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

標籤：

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

IF OBJECT_ID(N‘T14‘) IS NOT NULLBEGIN    DROP TABLE T14ENDGOCREATE TABLE T14 (t14_id INT)GODECLARE @i INT = 1WHILE @i <= 1000BEGIN    INSERT INTO T14 (t14_id)    SELECT @i    SET @i = @i + 1ENDGO

code-1

 

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

 

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

IF OBJECT_ID(N‘T14‘) IS NOT NULLBEGIN    DROP TABLE T14ENDGOCREATE TABLE T14 (t14_id INT)GODBCC FREESESSIONCACHEDBCC DROPCLEANBUFFERSGOSET NOCOUNT ON;BEGIN TRANDECLARE @i INT = 1WHILE @i <= 1000000BEGIN    INSERT INTO T14 (t14_id)    SELECT @i    SET @i = @i + 1ENDCOMMIT TRAN;SET NOCOUNT OFF;GO

code-2

 

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

 

下面是使用CTE：

IF OBJECT_ID(N‘T15‘) IS NOT NULLBEGIN    DROP TABLE T15ENDGOCREATE TABLE T15 (t15_id INT)GODBCC FREESESSIONCACHEDBCC DROPCLEANBUFFERSGOWITH 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 <= 1000000GO

code-3

 

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

IF OBJECT_ID(N‘T15‘) IS NOT NULLBEGIN    DROP TABLE T15ENDGOCREATE TABLE T15 (t15_id INT)GODBCC FREESESSIONCACHEDBCC DROPCLEANBUFFERSGOWITH CTE1 AS ( SELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS bUNION ALLSELECT a.[object_id] FROM master.sys.all_objects AS a, master.sys.all_objects AS bUNION ALLSELECT 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 <= 10000000GO

code-4

 

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

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