最近有個維護案需要定期匯入百萬筆記錄到 SQL Server 之中，客戶的原始廠商在後台是有寫了一個功能可以讓後台管理員上傳 Excel 檔案，然後透過程式一筆一筆新增到資料庫中，但這個匯入的過程竟然要花上 1.5 小時才能新增完畢，實在是太慢了。其實從 .NET Framework 2.0 開始，在 System.Data.SqlClient 命名空間下就有個 SqlBulkCopy 類別可以用來高效率的大量寫入資料到資料表中，其寫入速度可能快上 50 倍之多！
龜速的 INSERT INTO 語法
透過以下程式新增 10 萬筆資料，需花費 25 秒左右的時間：
for (int i = 0; i < 100_000; i++)
{
var cmd = new SqlCommand(@"
INSERT INTO [dbo].[coupon] (
[coupon_no],
[coupon_value],
[coupon_sdate],
[coupon_edate]
) VALUES (
@coupon_no,
@coupon_value,
@coupon_sdate,
@coupon_edate
)
", conn);
cmd.Parameters.AddWithValue("@coupon_no", i+1);
cmd.Parameters.AddWithValue("@coupon_value", new Random().Next(500, 5000));
cmd.Parameters.AddWithValue("@coupon_sdate", DateTime.Now);
cmd.Parameters.AddWithValue("@coupon_edate", DateTime.Now.AddYears(1));
cmd.ExecuteNonQuery();
}
極速的 SqlBulkCopy 語法
透過以下程式新增 10 萬筆資料，需花費 600 豪秒(ms)左右的時間而已，速度真的超快：
// 先建立 DataTable 或 DataRow[] 物件都可以讓 SqlBulkCopy 快速寫入資料
DataTable dt = new DataTable();
dt.Columns.Add("coupon_no", typeof(string));
dt.Columns.Add("coupon_value", typeof(string));
dt.Columns.Add("coupon_sdate", typeof(string));
dt.Columns.Add("coupon_edate", typeof(string));
for (int i = 0; i < 100_000; i++)
{
DataRow dr = dt.NewRow();
dr["coupon_no"] = i+1;
dr["coupon_value"] = new Random().Next(500, 5000);
dr["coupon_sdate"] = DateTime.Now;
dr["coupon_edate"] = DateTime.Now.AddYears(1);
dt.Rows.Add(dr);
}
// 對 DataTable 進行批次匯入
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.DestinationTableName = "[dbo].[coupon]";
bulkCopy.WriteToServer(dt);
}
相關連結