SqlBulkCopy類進行大資料(10000萬條以上)插入測試

標籤：des   style   blog   http   color   使用   os   io   

好多天沒寫部落格了，剛剛畢業一個多月，在IT的路上真是迷茫啊！

關於上一篇部落格中提到的，在進行批量資料插入資料庫的時候可以通過給預存程序傳遞一個類型為Table的參數進行相關操作，在這個過程中本人沒有進行效率的測試。後來尋找發現可以通過SqlBulkCopy類進行大資料插入操作，本文就來介紹一個這個類的作用並做了相關的效率測試(與Insert語句進行對比)。

SqlBulkCopy只能向SqlServer表寫入資料，該表既可以是同一個伺服器上的也可以是遠程伺服器上的。當然要寫入的資料即資料來源不單單是SqlServer，可以是任何資料來源，只要資料可以載入到DataTable中或者DataReader可以讀取的資料。

SqlBulkCopy類批量插入資料代碼如下：

1.先建立一個User類，對應資料庫的User表。

 1 public class User 2 { 3     public User() 4     { 5          6     } 7     public string UserName { get; set; } 8     public string PassWord { get; set; } 9     public string Address { get; set; }10     public string Telephone { get; set; }11 }

View Code

資料庫User表結構:

2.建立一個返回儲存了一萬條資料的DataTable方法：

 1     protected DataTable GetDataTableData() 2     { 3         List<User> userList=new List<User>(); 4         for (int i = 0; i < 10000; i++) 5         { 6             User user=new User(); 7             user.UserName = "zhangsan"; 8             user.PassWord = "123456"; 9             user.Address = "北京路一號";10             user.Telephone = "13232323232";11             userList.Add(user);12         }13         using (DataTable dt = new DataTable())14         {15             dt.Columns.Add("UserName", typeof (string));16             dt.Columns.Add("PassWord", typeof(string));17             dt.Columns.Add("Address", typeof(string));18             dt.Columns.Add("Telephone", typeof(string));19             foreach (User user in userList)20             {21                 DataRow dr = dt.NewRow();22                 dr["UserName"] = user.UserName;23                 dr["PassWord"] = user.PassWord;24                 dr["Address"] = user.Address;25                 dr["Telephone"] = user.Telephone;26                 dt.Rows.Add(dr);27             }28             return dt;29         }30     }

View Code

3.在頁面上放了一個按鈕，點擊該按鈕便可進行資料的插入操作，按鈕點擊事件代碼如下：

 1     protected void Button1_Click(object sender, EventArgs e) 2     { 3         string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; 4         SqlConnection connection=new SqlConnection(conStr); 5         try 6         { 7             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr, SqlBulkCopyOptions.KeepIdentity|SqlBulkCopyOptions.UseInternalTransaction)) 8             { 9                 bulkCopy.DestinationTableName = "dbo.[User]";//目標表，就是說您將要將資料插入到哪個表中去10                 bulkCopy.ColumnMappings.Add("UserName", "UserName");//資料來源中的列名與目標表的屬性的映射關係11                 bulkCopy.ColumnMappings.Add("PassWord", "PassWord");12                 bulkCopy.ColumnMappings.Add("Address", "Address");13                 bulkCopy.ColumnMappings.Add("Telephone", "Telephone");14                 DataTable dt = GetDataTableData();//資料來源資料15                 //bulkCopy.BatchSize = 3;16                 Stopwatch stopwatch=new Stopwatch();//跑表，該類可以進行時間的統計17                 stopwatch.Start();//跑表開始18                 bulkCopy.WriteToServer(dt);//將資料來源資料寫入到目標表中19                 Response.Write("插入資料所用時間:"+stopwatch.Elapsed);//跑表結束，Elapsed是統計到的時間20             } 21         }22         catch (Exception ex)23         { 24             throw new Exception(ex.Message);25         }26        Response.Write("<br/>插入成功！！！");27     }

View Code

SqlServer中通過Insert語句批量插入資料的代碼如下：

1.首先建立一個插入一萬條資料的預存程序，插入的資料與SqlBulkCopy插入的資料一模一樣。

 

 1 CREATE PROC USP_InsertUserTable 2 AS 3 BEGIN 4     DECLARE @i INT 5     SET @i=1 6     WHILE @i<10001 7     BEGIN 8         INSERT INTO dbo.[User] 9                 ( UserName ,10                   PassWord ,11                   Address ,12                   Telephone13                 )14         VALUES  ( N‘zhangsan‘ , -- UserName - nvarchar(50)15                   N‘123456‘ , -- PassWord - nvarchar(6)16                   N‘北京路一號‘ , -- Address - nvarchar(50)17                   ‘13232323232‘  -- Telephone - varchar(11)18                 )19 20                   SET @[email protected]+121     END22 END

View Code

 

2.執行預存程序。
EXEC dbo.USP_InsertUserTable

SqlBulkCopy與Insert語句效率測試對比如下：

SqlBulkCopy所用時間統計（5次):

SqlServer Insert語句所用時間統計(5次),通過工具--》Sql Server Profiler進行測試，使用的模板是TSQL_Duration:

有一點需要注意的：無論是SqlBulkCopy還是Insert語句進行操作，每次測試前都應該進行緩衝區清理以及刪除原有資料，以免對測試進行影響，代碼如下：

DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE( ‘ALL‘ )
DELETE dbo.[User]

 

結論：通過以上的統計分析測試，可以看出SqlBulkCopy操作大資料插入的效率效能明顯比Insert語句更高效。

本人才疏學淺，哪裡有不對的歡迎指出。