百萬筆資料導入 SQL Server 僅需 4 秒：五種方法實測大比拚

實驗環境

• 硬體：Dell 2850，雙核 2.0GHz，2GB RAM
• 資料庫：SQL Server 2000 / SQL Server 2008
• 目標表：`TableB (Value1 VARCHAR)`
• 資料量：1,000,000 筆
• 框架：IsLine Framework DataProvider (薄封裝，不影響本質)

五種方法與實測耗時 (毫秒)

核心程式碼片段

BULK INSERT (T-SQL)

BULK INSERT TableB 
FROM 'C:\data\bulk.txt' 
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '|',
    BATCHSIZE = 100000
);

SqlBulkCopy (C#)

using (var sqlBC = new SqlBulkCopy(connStr)) {
    sqlBC.BatchSize = 100000;
    sqlBC.BulkCopyTimeout = 60;
    sqlBC.DestinationTableName = "dbo.TableB";
    sqlBC.ColumnMappings.Add("ValueA", "Value1"); // DataTable 欄位 -> 資料表欄位
    sqlBC.WriteToServer(dataTable); // dataTable 含 100 萬列
}

深度分析：為什麼 SQL 2008 沒有更快？

• 測試資料特性：單欄位窄表，`BULK INSERT`/`SqlBulkCopy` 已逼近硬體 I/O 上限，SQL 2008 新特性 (資料壓縮、稀疏欄位等) 無發揮空間。
• 記錄檔模式：測試可能使用完整復原模式，大量最小記錄仍產生記錄檔寫入壓力。簡單/大量記錄模式下差距會拉大。
• 多執行緒反而變慢：單機單磁碟下，並行寫入產生隨機 I/O、鎖競爭、記憶體壓力，得不償失。多檔案群組/多磁碟陣列下多執行緒才有優勢。
• SqlBulkCopy 記憶體模型：需先將資料載入 `DataTable` 佔用大量記憶體，百萬列以上建議分批流式讀取 (`IDataReader`) 避免 OOM。

生產環境最佳實務建議

1. 首選 SqlBulkCopy (IDataReader 流式)：程式碼可控、支援資料轉換/驗證、跨平台、易整合 CI/CD。
2. 次選 BULK INSERT：純 T-SQL 環境、檔案已就緒、無複雜轉換邏輯。
3. 批次大小 10,000~100,000：平衡記錄檔、鎖持有時間、記憶體。
4. 匯入前：停用非叢集索引、約束、觸發器；設定資料庫為 `BULK_LOGGED` 或 `SIMPLE` 復原模式。
5. 匯入後：重建索引、更新統計資料、檢查約束、切回 `FULL` 復原模式並完整備份。
6. 超大量 (億級+)：分區表 + SWITCH 切換、多檔案群組平行 I/O、SSIS 封裝、考慮 PolyBase/外部表。

總結

百萬筆資料 4 秒級匯入，**BULK INSERT** 與 **SqlBulkCopy** 同屬最小記錄批次匯入機制，性能極致接近。單執行緒即可榨乾單磁碟 I/O，多執行緒需硬體架構配合。選型核心：程式整合度 → `SqlBulkCopy`；純資料庫端腳本 → `BULK INSERT`。切記：索引、約束、復原模式的前後配合才是決定成敗的關鍵。