database

使用SqlBulkCopy將資料批次寫入資料庫

前demo有介紹利用SqlDataSoure和手動撰寫ADO.NET的方式大量新增資料的方法，雖然已經有效的改善了寫入的速度，但在發現了SqlBulkCopy以後，發現它更是威力強大，現在就來介紹SqlBulkCopy的猛。

★注意事項：本文是延續將資料大量寫入資料庫時的優化寫法的更優化如果對於文中有不清楚之意，可利用本文最下方相關的文章進入觀看資料大量寫入資料庫時的優化寫法一文

▲說明都在程式註解中啦

  //一開始我們先產生一個DataTable來裝我們要寫入的資料
 DataTable dt = new DataTable();
 dt.Columns.Add("id", typeof(int));
 dt.Columns.Add("name", typeof(string));
 
 //因為SqlBulkCopy的猛就是大量的一次寫入，所以我們也來跑10萬筆吧
 int i;
 for (i = 0; i < 100000; i++)
 {
     DataRow dr = dt.NewRow();
     dr["name"] = i.ToString();
     dt.Rows.Add(dr);
 }
 
 //宣告連結字串
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
 
 conn.Open();
 //宣告SqlBulkCopy
 using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
         {
             //設定一個批次量寫入多少筆資料
             sqlBC.BatchSize = 1000;
             //設定逾時的秒數
             sqlBC.BulkCopyTimeout = 60;
 
             //設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
             sqlBC.NotifyAfter = 10000;
             sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
 
             //設定要寫入的資料庫
             sqlBC.DestinationTableName = "dbo.Table1";
              
             //對應資料行
             sqlBC.ColumnMappings.Add("id", "id");
             sqlBC.ColumnMappings.Add("name", "name");
 
             //開始寫入
             sqlBC.WriteToServer(dt);
         }
         conn.Dispose();
 }
 void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
 {
     Response.Write("---<br/>");
 }

測試環境：SQL2005 Express
測試資料量：10萬筆
測試次數：10次
平均秒數：2.3532秒

?小鋪廢言：太可怕啦，之前的寫法如果真的要寫10萬筆這種大量的資料都需花費一分鐘左右，但使用了SqlBulkCopy卻只要短短的兩秒鐘，下表看的出來如果資料筆數很少就沒必要使用SqlBulkCopy了。

寫入十萬筆資料10次的平均秒數
- 使用SqlBulkCopy：2.2051
- 使用AddWithValue：63.418
寫入一萬筆資料10次的平均秒數
- 使用SqlBulkCopy：0.2188
- 使用AddWithValue：6.3856
寫入一千筆資料10次的平均秒數
- 使用SqlBulkCopy：0.0187
- 使用AddWithValue：0.5805
寫入一百筆資料10次的平均秒數
- 使用SqlBulkCopy：0.0062
- 使用AddWithValue：0.0353
寫入十筆資料10次的平均秒數
- 使用SqlBulkCopy：0.004
- 使用AddWithValue：0.004

http://bibby.be/2008/04/blog-post_25.html
http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlbulkcopy_members(VS.80).aspx

Read next