SQL Server 批量插入三大方案實測：循環 INSERT vs SqlBulkCopy vs 表值參數

測試環境

• SQL Server 2012
• 目標表：BulkTable (Id PK, UserName NVARCHAR(32), Pwd VARCHAR(16))
• 表值型別：dbo.BulkType 對應同結構
• 資料量：分 10 批，每批 10 萬，累計 100 萬筆
• 計時工具：Stopwatch 毫秒級

方案一：逐行參數化 INSERT（基準線）

SqlCommand cmd = new SqlCommand(
    "INSERT INTO BulkTable(Id,UserName,Pwd) VALUES(@p0,@p1,@p2)", conn);
cmd.Parameters.Add("@p0", SqlDbType.Int);
cmd.Parameters.Add("@p1", SqlDbType.NVarChar);
cmd.Parameters.Add("@p2", SqlDbType.VarChar);

conn.Open();
for (int i = 0; i < 1_000_000; i++) {
    cmd.Parameters["@p0"].Value = i;
    cmd.Parameters["@p1"].Value = $"User-{i}";
    cmd.Parameters["@p2"].Value = $"Password-{i}";
    sw.Start(); cmd.ExecuteNonQuery(); sw.Stop();
}

結果：~45,000+ ms（圖示），網路往返 + 解析編譯 + 日誌寫入 100 萬次，效能最差。

方案二：SqlBulkCopy（ADO.NET 批量複製）

using (var bulk = new SqlBulkCopy(conn)) {
    bulk.DestinationTableName = "BulkTable";
    bulk.BatchSize = dt.Rows.Count;  // 全批次提交
    conn.Open();
    bulk.WriteToServer(dt);
}

結果：~3,500 ms（圖示），快 12 倍。底層使用 Tabular Data Stream (TDS) 批量流，最小化日誌、鎖升級優化。

方案三：表值參數（TVP，SQL Server 2008+）

-- 資料庫端預建型別
CREATE TYPE dbo.BulkType AS TABLE (
    Id INT, UserName NVARCHAR(32), Pwd VARCHAR(16)
);

-- C# 呼端
var cmd = new SqlCommand(
    "INSERT INTO BulkTable(Id,UserName,Pwd) SELECT Id,UserName,Pwd FROM @tvp", conn);
var p = cmd.Parameters.AddWithValue("@tvp", dt);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.BulkType";
conn.Open();
cmd.ExecuteNonQuery();

結果：~2,800 ms（圖示），最快。單次往返、伺服器端直接流式插入、計畫重用性佳。

效能總結表

補充：單條 SQL 多值插入（中小批量）

INSERT INTO SystemSet_tbl (ss_guid, ss_type, ss_comment) 
VALUES 
('000...007', 1, ''),
('000...008', 1, '');
-- 單次最多 1000 個值列（SQL Server 限制），適合幾千行內場景

選型建議

• 1 萬行內：多值 VALUES 或 TVP 最簡潔
• 10 萬–百萬：TVP 首選（型別安全、計畫重用），SqlBulkCopy 次之
• 超百萬/跨實例：SqlBulkCopy + 批次大小調優、考慮 BULK INSERT/bcp
• 避免：任何場景下的循環單行 ExecuteNonQuery