SQL Server 批次插入資料效能優化：單筆、集合、迴圈三種模式比較

測試背景

BI 專案效能測試需向維度表 time_by_day 持續寫入資料，比較三種 T-SQL 寫法的吞吐量與資源消耗。

目標表結構

CREATE TABLE time_by_day (
    time_id       INT PRIMARY KEY,
    the_date      DATE,
    the_year      SMALLINT,
    month_of_year TINYINT,
    quarter       CHAR(2),
    day_of_month  TINYINT
);

三種插入模式

1. 單筆 INSERT VALUES（基準線）

INSERT INTO time_by_day (time_id, the_date, the_year, month_of_year, quarter, day_of_month)
VALUES (1101, '1999-10-01', 1999, 10, 'Q4', 1);

特點： 每筆獨立交易、大量網路往返、記錄檔頻繁 Flush、效能最差。

2. 集合式 INSERT ... SELECT（推薦）

INSERT INTO time_by_day (time_id, the_date, the_year, month_of_year, quarter, day_of_month)
SELECT TOP 1
    time_id + 1,
    DATEADD(day, 1, the_date),
    YEAR(DATEADD(day, 1, the_date)),
    MONTH(DATEADD(day, 1, the_date)),
    'Q' + CAST(DATEPART(quarter, DATEADD(day, 1, the_date)) AS CHAR(1)),
    DAY(DATEADD(day, 1, the_date))
FROM time_by_day
ORDER BY time_id DESC;

優點： 單一陳述式、集合運作、最小化記錄檔寫入、可利用最少記錄（Minimal Logging，配合 TABLOCK 與簡單/大量記錄復原模式）。

3. WHILE 迴圈批次插入（可控頻率）

DECLARE @Counter INT = 0;
WHILE @Counter < 1000
BEGIN
    WAITFOR DELAY '00:00:10';  -- 控制每 10 秒一批
    INSERT INTO time_by_day (...)
    SELECT TOP 1 ... FROM time_by_day ORDER BY time_id DESC;
    SET @Counter = @Counter + 1;
END

適用： 需模擬即時資料流、控制寫入速率避免衝擊線上系統。

效能比較摘要（百萬筆資料級）

進階優化技巧

1. 最少記錄： INSERT INTO target WITH (TABLOCK) SELECT ... + 簡單/大量記錄復原模式 → 僅記錄分頁配置，不記錄逐列操作。
2. 批次大小調整： 大量資料可分批（如 10 萬筆/批）配合 CHECKPOINT 控制記錄檔截斷。
3. 停用非必要索引/觸發器： 匯入前 DISABLE TRIGGER、DROP INDEX，匯入後重建。
4. 使用 BCP / BULK INSERT / SqlBulkCopy： 繞過 SQL 引擎解析，直接資料流寫入，效能更強。
5. 分區表切換： 先寫入暫存分區，再 ALTER TABLE ... SWITCH 瞬間上線，零停機。

程式碼片段修正說明

原文片段中 { fn QUARTER(...) } 為 ODBC 標量函數語法，標準 T-SQL 應用 DATEPART(quarter, ...)；日期運算建議用 DATEADD 而非 + 運算子以確保型別安全。