百萬級數據入庫，誰是性能之王？

針對 SQL Server 大批量插入，本文對比五大技術方案：循環調用存儲過程、SqlBulkCopy、表值參數（TVP）、字符串拼接拆分、異步隊列。通過 1K/10K/100W 三檔測試數據，給出明確選型建議。

方案對比總覽

方案	原理	適用規模	優點	缺點
1. 循環調用 SP	逐行 EXEC sp_insert	< 1K	簡單、兼容舊版本	網絡往返多、上下文切換重、極慢
2. SqlBulkCopy	ADO.NET 批量複製流	1K - 1000W+	極快、流式內存、支援 BatchSize/NotifyAfter	需 .NET 環境、列映射嚴格
3. 表值參數 (TVP)	用戶定義表類型傳參	< 1K 最優，>1K 性能下降	集合操作、單次往返、類型安全	SQL Server 2008+、內存壓力大、>1000 行需分批
4. 字符串拼接拆分	CSV 傳入 SP 再 SPLIT	< 10K	無需新建類型	拆分邏輯複雜、長度限制、注入風險
5. 異步隊列	生產者-消費者模式	極大/削峰填谷	解耦、可重試、平滑寫入	架構複雜、最終一致性、延遲

TVP 完整實現步驟

1. 建立表類型：
   CREATE TYPE PassportTableType AS TABLE (PassportKey NVARCHAR(50));
2. 存儲過程接收 TVP：
   CREATE PROCEDURE CreatePassportWithTVP @TVP PassportTableType READONLY AS INSERT INTO Passport(PassportKey) SELECT PassportKey FROM @TVP;
3. C# 調用：
   DataTable dt = ...; SqlParameter p = new SqlParameter("@TVP", dt) { TypeName = "PassportTableType" }; cmd.Parameters.Add(p); cmd.ExecuteNonQuery();

實測性能數據（單表單列，SSD、本地實例）

數據量	循環 SP (ms)	SqlBulkCopy (ms)	TVP (ms)
1,000	~2,500	~15	~20
10,000	~25,000	~120	~180
1,000,000	~2,500,000	~8,000	~25,000 (內存壓力大)

核心結論

1. SqlBulkCopy 是通用首選：全量級別最快、內存可控、API 簡單、跨版本穩定。
2. TVP 適合小批量（<1K）高頻調用：如單據明細保存，單次網絡往返，類型安全。
3. 循環調用 SP 只適合極小量測試，生產禁用。
4. 超大規模（>1000W）考慮：分區表 + SqlBulkCopy 並行、或 Kafka/Flink 流式入庫。

SqlBulkCopy 最佳實踐參數

var bulk = new SqlBulkCopy(connStr, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints);
bulk.DestinationTableName = "Passport";
bulk.BatchSize = 5000;           // 批次提交，平衡事務日誌與鎖
bulk.BulkCopyTimeout = 600;      // 10 分鐘超時
bulk.EnableStreaming = true;    // 大數據流式讀取，不全載入內存
bulk.ColumnMappings.Add("PassportKey", "PassportKey");  // 顯式映射
bulk.WriteToServer(dataTable);

避坑指南

• 目標表有觸發器 → SqlBulkCopyOptions.FireTriggers
• 身份列自增 → 目標表不映射身份列，或 SqlBulkCopyOptions.KeepIdentity
• 外鍵/檢查約束 → 大批量考慮 NOCHECK CONSTRAINT 導入後 CHECK CONSTRAINT
• 字符集編碼：連接字串指定 Character Set=utf8（MySQL）或確保 SQL Server 排序規則一致

總結

百萬級首選 SqlBulkCopy，千級高頻用 TVP，
循環 SP 進博物館，異步隊列做削峰。
BatchSize 調優記心間，ColumnMappings 顯式全。
備份驗證少不了，上線前必壓測。