海量資料之快樂轉移–SqlBulkCopy

 

最近，正和團隊做集團內A公司X平台向B公司移植，眾所周知，軟體移植項目必然少不了資料轉移——資料從一個地方複製到另外一個地方是再平常不過的需求了。然而B公司老X平台已經上線5,6年，單表上百萬的資料量真是家常便飯，如果僅說讓這些資料能妥善的“安置”到新的平台中，那也很easy，可是讓人頭痛的是海量資料的轉移需要多久？幾個小時？幾個工作日？——因為所有資料轉移的時間的總和等於最終新老平台切換時我們的服務需要停止的時間。拋開項目其它工程不說，如果整個資料轉移需求3,5個工作日，我想別說客戶，產品和業務部門的同事都不可能同意。

我很幸運，也是我孤陋寡聞，上周一個新來的同事建議說ASP.NET 2.0有一個SqlBulkCopy類，它可以協助我們很快速的複製資料。

起初查到一篇這樣的博文：《DataTable的資料批量寫入資料庫》。原文如下：

最近在將excel中的檔案匯入到資料庫中，用程式進行編寫，由於資料量較大所以速度很慢，後來採用了SqlBulkCopy類，解決了速度的問題，我就insert語句,sqldataadapter.update(dataset,tablename);sqlbulkcopy.WriteToServer(datatable);三個方法的效能進行比較：

1、產生測試的datatable表，表結構如下：

UniqueID(主鍵，自動成長)  |  CompanyName  |  CompanyCode  |  Address  |  Owner  |  Memo

共6個欄位。

SqlConnection sqlconnection = new SqlConnection(connectionString);
SqlDataAdapter sqldataadapter = new SqlDataAdapter("select * from Table_1 where 1=2", sqlconnection);
DataSet dataset = new DataSet();
sqldataadapter.Fill(dataset, "Table_1");
DataTable datatable = dataset.Tables[0];
  //產生20000條記錄

  for (int i = 0; i   {
          DataRow datarow = datatable.NewRow();
          datarow["CompanyName"] = "companyname"+string.Format("{0:0000}",i);
          datarow["CompanyCode"] = "companycode" + string.Format("{0:0000}", i);
          datarow["Address"] = "address" + string.Format("{0:0000}", i);
          datarow["Owner"] = "owner" + string.Format("{0:0000}", i);
          datarow["Memo"] = "memo" + string.Format("{0:0000}", i);

          datatable.Rows.Add(datarow);
  }

2、使用sqlcommand.executenonquery()方法插入

foreach (DataRow datarow in datatable.Rows)
{
string sql = "INSERT INTO [Table_1]

([CompanyName],[CompanyCode],[Address],[Owner],[Memo])" +
"VALUES('" + datarow["CompanyName"].ToString() + "'" +
",'" + datarow["CompanyCode"].ToString() + "'" +
",'" + datarow["Address"].ToString() + "'" +
",'" + datarow["Owner"].ToString() + "'" +
",'" + datarow["Memo"].ToString() + "')";
  using (SqlConnection sqlconn = new SqlConnection(connectionString))
  {
      sqlconn.Open();

      SqlCommand sqlcommand = new SqlCommand(sql, sqlconn);
      sqlcommand.ExecuteNonQuery();
      sqlconn.Close();
  }
}

插入20000條記錄時間:00:00:29.7336000

3、使用sqldataadapter.update(dataset,tablename);

SqlCommand insertcommand = new SqlCommand("INSERT INTO [Table_1]([CompanyName],[CompanyCode],[Address],[Owner],[Memo])" +
"VALUES(@CompanyName, @CompanyCode,@Address,@Owner,@Memo)",new SqlConnection(connectionString));
insertcommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 50, "CompanyName");
insertcommand.Parameters.Add("@CompanyCode", SqlDbType.NChar, 25, "CompanyCode");
insertcommand.Parameters.Add("@Address", SqlDbType.NChar, 255, "Address");
insertcommand.Parameters.Add("@Owner", SqlDbType.NChar, 25, "Owner");
insertcommand.Parameters.Add("@Memo", SqlDbType.NChar, 255, "Memo");
sqldataadapter.InsertCommand = insertcommand;

sqldataadapter.Update(dataset, "Table_1");  

插入20000條記錄時間:00:00:22.8938000

4、使用sqlbulkcopy.writetoserver(datatable)

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "Table_1";//資料庫中的表名

sqlbulkcopy.WriteToServer(dataset.Tables[0]);

插入20000條記錄時間:00:00:00.3276000

所以說速度是sqlbulkcopy最快，sqldataadapter.update()次之，sqlcommand.ExecuteNonQuery()最慢。

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/humozhi/archive/2009/03/31/4039868.aspx

我有個習慣，就是新的事物總抱有懷疑的態度，於是發動幾個同事一起測試，結果確實很讓我吃驚。平均匯入速度要比之前傳統的方式快10倍左右。

查了些資料，微軟是這樣定義的：

SqlBulkCopy 類允許編寫提供類似功能的Managed 程式碼解決方案。還有其他將資料載入到 SQL Server 表的方法（例如 INSERT 語句），但相比之下 SqlBulkCopy 提供明顯的效能優勢。
使用 SqlBulkCopy 類只能向 SQL Server 表寫入資料。但是，資料來源不限於 SQL Server；可以使用任何資料來源，只要資料可載入到 DataTable 執行個體或可使用 IDataReader 執行個體讀取資料。

補充：SqlBulkCopy是.NET Framework 2.0新增的類，位於命名空間System.Data.SqlClient下，主要提供把其他資料來源的資料有效批量的載入到SQL Server表中的功能。類似與 Microsoft SQL Server 包中名為 bcp 的命令列應用程式。但是使用 SqlBulkCopy 類可以編寫Managed 程式碼解決方案，效能上優於bcp命令列應用程式，更優於如Insert方式向SQL Server表載入大量資料。SqlBulkCopy可以應用到大批量資料的轉移上，而不管資料來源是什麼。

另外，《使用asp.net 2.0中的SqlBulkCopy類批量複製資料》說：

SqlBulkCopy 包含一個方法 WriteToServer，它用來從資料的源複製資料到資料的目的地。 WriteToServer方法可以處理的資料類型有DataRow[]數組，DataTable 和 DataReader。 你可以根據不同的情形使用不同的資料類型，但是更多時候選擇DataReader是一個比較好的主意。 這是因為DataReader是一個只向前的、唯讀資料流，它不會儲存資料，所以要比DataTable 和 DataRows[]都要快。

下面的代碼的作用是把資料從源表複製到目的表。
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
      {
bulkCopy.BatchSize = 500;

        bulkCopy.NotifyAfter = 1000;
        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
        bulkCopy.DestinationTableName = "Products_Latest";
        bulkCopy.WriteToServer(reader);                   
      }

這裡有一對需要提及的知識點。 首先，我使用DataReader來從資料庫的表中讀取資料。 Products_Latest是目的表，因為資料要從Products_Archive表複製到Products_Latest表。 bulkCopy對象提供了一個SqlRowCopied事件，在每次處理完NotifyAfter屬性指定的行數時發生。 本例中的意思就是每處理完1000行就觸發一次該事件，因為NotifyAfter被設定成了1000

BatchSize屬性是非常重要的，程式效能如何主要就依靠著它。 BatchSize的意思就是同每一批次中的行數，在每一批次結束時，就將該批次中的行發送到資料庫。 我將BatchSize設定成了500，其意思就是reader每讀出500行就將他們發送到資料庫從而執行批量複製的操作。 BatchSize的預設值是“1”，其意思就是把每一行作為一個批次發送到資料庫。

設定不同的BatchSize在效能上將給你帶來不同的結果。 你應該根據你的需求進行測試，來決定BatchSize的大小。

查了下原理：

SqlBulkCopy讓 DataSet 內大量的資料或是 DataReader通過資料流（Stream）直接讀取大量的記錄，可以快速將這些記錄添加到目的資料庫的資料表中。但要注意的是它並非如我們一般用的 bcp.exe工具程式，可以從某個符號分隔檔案讀取大量資料，選擇性地搭配格式檔案（Format File）將記錄裝載到資料庫中，或是將資料庫內的資料匯出成為一個檔案。但由於DataSet 能整合 XML 資料，因此依然可以採用 SqlBulkCopy 類型，輕鬆地通過 DataSet 將 XML 檔案資料大量轉入到資料庫。

總結

在瞭解了SqlBulkCopy之後，我們基本上所有大資料都是用它實現的，有人說SqlBulkCopy不是走ADO.NET的而是直接操作MDB檔案，我也沒有證實。但我想暫時不影響我實用它。當然，SqlBulkCopy使用時要注意以下幾點：

1．確認確實需要大容量更新在執行此操作，(幾十行的資料請盡量使用別的渠道把).

2．確認資料一致性，與檢查機制，以免遇到主鍵衝突，資料不符格式等意外。

3．SqlBulkCopy操作可能會導致對目標表中繼資料的更改（例如，禁用約束檢查時）。如果出現這種情況，訪問大容量插入表的並發快照隔離事務將失敗。

4. SqlBulkCopy將向資料庫下大容量更新鎖定，請注意並發性，以免其他串連因長時間等待而逾時。

延伸

SqlBulkCopy的快速資料複製，不得不讓我想到Database Backup及資料庫讀寫分離的多機資料同步。剛才在找資料時，也看到了一篇SqlBulkcopy的應用《利用SqlBulkCopy實現網上答題系統》。當然，資料庫讀寫分離後資料同步是一門很大的學問，SqlBulkcopy我想只能是初級的解決方案。