Begin Transaction 會不會影響 SQL Performance?

Source: Begin Transaction 會不會影響 SQL Performance?

今天同事剛好問我這個問題，這個問題很有趣，也被很多人討論過。 首先，我們需要了解到 MS SQL的Default是Auto Commit，意思就是說縱使你沒有Commit Transaction，你的交易還是會被自動Commit。這點和Oracle不太一樣，算是MS SQL的特色之一(?)

我們先講結論，如果再大量Insert的時候加入Begin Transaction，效能會遠比沒有加好。

Why?–原理很簡單，假如沒有加入Begin Tran，因為MS SQL Default 是 Auto Commit，因此每Insert一筆資料Log都會有Begin Tran, Commit的紀錄，如果有十萬筆就會有十萬筆的Begin Tran, Commit。 如此一來效能就會很差….

所以如果在一開始加入Begin Tran，在Insert 十萬筆資料後再Commit，就會節省十萬筆的Begin Tran, Commit。所以效能就會好很多。

所以每次我們都加Begin Transaction嗎? 答案當然不是，因為加入Begin Transaction可能會造成Table Lock。這個時候你的其他交易都沒有辦法寫入資料庫…. 你老闆可能就會來找你了。

如果上面還是看不懂，就讓我們來看看Code和圖片吧。可以從下面這張圖發現對於log flush的資源差異。

-- Drop and create a test table
IF OBJECT_ID('TestBeginTran', 'U') IS NOT NULL
DROP TABLE TestBeginTran;

CREATE TABLE TestBeginTran
(
id INT,
created datetime DEFAULT GETDATE()
);
GO

SET NOCOUNT ON

-- Run a loop to insert 10000 rows
DECLARE @i INT = 1,
@start DATETIME = GETDATE();

WHILE @i <= 10000 BEGIN
INSERT INTO TestBeginTran (id) VALUES (@i);
SET @i = @i + 1;
END

-- Output the execution time in milliseconds
SELECT DATEDIFF(ms, @start, GETDATE()) as millisecond;
GO

沒有加入Begin Transaction的結果

我們來試試看一樣的筆數但是有加begin transaction所消耗的時間

-- Drop and create a test table
  IF OBJECT_ID('TestBeginTran', 'U') IS NOT NULL
    DROP TABLE TestBeginTran;
 
  CREATE TABLE TestBeginTran
  (
     id INT,
     created datetime DEFAULT GETDATE()
  );
  GO
 
  SET NOCOUNT ON
 
  -- Run a loop to insert 10000 rows
  DECLARE @i INT = 1,
          @start DATETIME = GETDATE();
 
  WHILE @i <= 10000 
  BEGIN
  
   -- Start a transaction
   IF @i % 10000 = 1 
       BEGIN TRANSACTION;

  INSERT INTO TestBeginTran (id) VALUES (@i);
  SET @i = @i + 1;

   -- Commit after each 10,000 row
    IF @i % 10000 = 0
      COMMIT;

  END
	
  -- Output the execution time in milliseconds
  SELECT DATEDIFF(ms, @start, GETDATE()) as millisecond; 
  GO

結果我們可以發現消耗時間差很多。

後記：

感謝SQL PASS的前輩讀完我的文章後給與我一些建議：

用了Begin Tran不只在交易紀錄檔內少紀錄 begin 和 commit，也同時改善Page 擴增 ，index 異動等資源的消耗，同時也降低交易紀錄檔大小的增加。

所以如果我今天有幾億筆資料要Insert使用begin transaction就好棒棒(?)

答案不是 因為你同時要考慮Table lock可能造成的block外，還要考慮交易紀錄檔增加會不會造成Disk容量不足。

如果是因為一次寫入幾億筆資料，你早些發現空間快要不足，立即Rollback….這時候悲劇也已經造成，Rollback往往需要三倍以上的時間，此外Rollback也需要寫入交易紀錄….那麼如果真的不幸走到這步…...除了燒香(?) 我們有機會在談要怎麼辦…… 

但是在走到上述這步之前，其實可以預防，那就是批次去做，並且考慮使用Bulk Insert的方式去做。

Note: Bulk Insert仍然會寫入交易紀錄檔，這點是需要注意的。所以最好要批次做