SQL Server WHILE 迴圈批次寫入資料的正確姿勢與效能陷阱

許多開發者初學 T-SQL 時，習慣用 WHILE 迴圈逐筆 INSERT 測試資料。雖然語法直觀，但在生產環境或大量資料場景下，這是效能殺手。本文示範基本寫法，並解析為何應改用集合式操作。

基本 WHILE 迴圈範例

DECLARE @i INT = 1;

WHILE @i < 30
BEGIN
    INSERT INTO dbo.test (userid) VALUES (@i);
    SET @i += 1;
END;

為何這寫法在大量資料會拖垮資料庫

• 上下文切換開銷：每次迴圈都觸發編譯/執行計畫、鎖管理、記錄寫入、提交交易
• 交易日誌膨脹：預設自動提交模式下，每筆 INSERT 產生獨立記錄，IOPS 飆升
• 鎖升級風險：大量細粒度鎖易觸發鎖升級，封鎖並發查詢

集合式替代方案（效能提升 100 倍以上）

方案一：單一 INSERT 配合 VALUES 列表（適用 < 1,000 列）

INSERT INTO dbo.test (userid)
VALUES (1), (2), (3), ..., (29);

方案二：遞迴 CTE 產生數列（適用萬級以內）

WITH nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 29
)
INSERT INTO dbo.test (userid)
SELECT n FROM nums
OPTION (MAXRECURSION 0);

方案三：系統物件交叉聯結（百萬級首選）

INSERT INTO dbo.test (userid)
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

若必須用迴圈：最佳化清單

1. 顯式交易包裹：BEGIN TRAN ... COMMIT 在迴圈外層，減少日誌刷盤次數
2. 批次大小控制：每 1,000~5,000 列提交一次，避免長交易封鎖
3. 使用 TABLE-VALUED PARAMETER：從應用層傳入資料表參數，單次 INSERT ... SELECT
4. 啟用最小化記錄：搭配 TABLOCK 與 BULK_LOGGED 復原模式（需評估 HA/DR 影響）

總結

WHILE 迴圈適合邏輯複雜、無法集合化的極少數場景。99% 的批次寫入需求，集合式 INSERT 搭配數列產生器才是專業選擇。下次寫測試資料腳本時，請直接複製方案三並調整 TOP (N)。