【ADO.NET-中級】百萬級資料的批量插入的兩種方法測試

標籤：username   str   base   new   ++   arp   lap   pwd   catch   

  在SQL Server 中插入一條資料使用Insert語句，但是如果想要批量插入一堆資料的話，迴圈使用Insert不僅效率低，而且會導致SQL一系統效能問題。下面介紹SQL Server支援的兩種批量資料插入方法：Bulk和表值參數(Table-Valued Parameters)。

運行下面的指令碼，建立測試資料庫和表值參數。

--Create DataBase  create database BulkTestDB;  Go  use BulkTestDB;  go  --Create Table  Create table BulkTestTable(  Id int primary key,  UserName nvarchar(32),  Pwd varchar(16))  go  --Create Table Valued  CREATE TYPE BulkUdt AS TABLE    (Id int,     UserName nvarchar(32),     Pwd varchar(16))  

下面我們使用最簡單的Insert語句來插入100萬條資料，代碼如下：

Stopwatch sw = new Stopwatch();    SqlConnection sqlConn = new SqlConnection(      ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//串連資料庫    SqlCommand sqlComm = new SqlCommand();  sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//參數化SQL  sqlComm.Parameters.Add("@p0", SqlDbType.Int);  sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);  sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);  sqlComm.CommandType = CommandType.Text;  sqlComm.Connection = sqlConn;  sqlConn.Open();  try  {      //迴圈插入100萬條資料，每次插入10萬條，插入10次。      for (int multiply = 0; multiply < 10; multiply++)      {          for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)          {                sqlComm.Parameters["@p0"].Value = count;              sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);              sqlComm.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);              sw.Start();              sqlComm.ExecuteNonQuery();              sw.Stop();          }          //每插入10萬條資料後，顯示此次插入所用時間          Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));      }  }  catch (Exception ex)  {      throw ex;  }  finally  {      sqlConn.Close();  }    Console.ReadLine();  

耗時圖如下：

由於運行過慢，才插入10萬條就耗時72390 milliseconds，所以我就手動強行停止了。

 

下面看一下使用Bulk插入的情況：

bulk方法主要思想是通過在用戶端把資料都緩衝在Table中，然後利用SqlBulkCopy一次性把Table中的資料插入到資料庫

代碼如下：

public static void BulkToDB(DataTable dt)  {      SqlConnection sqlConn = new SqlConnection(          ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);      SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);      bulkCopy.DestinationTableName = "BulkTestTable";      bulkCopy.BatchSize = dt.Rows.Count;        try      {          sqlConn.Open();      if (dt != null && dt.Rows.Count != 0)          bulkCopy.WriteToServer(dt);      }      catch (Exception ex)      {          throw ex;      }      finally      {          sqlConn.Close();          if (bulkCopy != null)              bulkCopy.Close();      }  }    public static DataTable GetTableSchema()  {      DataTable dt = new DataTable();      dt.Columns.AddRange(new DataColumn[]{          new DataColumn("Id",typeof(int)),          new DataColumn("UserName",typeof(string)),      new DataColumn("Pwd",typeof(string))});        return dt;  }    static void Main(string[] args)  {      Stopwatch sw = new Stopwatch();      for (int multiply = 0; multiply < 10; multiply++)      {          DataTable dt = Bulk.GetTableSchema();          for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)          {              DataRow r = dt.NewRow();              r[0] = count;              r[1] = string.Format("User-{0}", count * multiply);              r[2] = string.Format("Pwd-{0}", count * multiply);              dt.Rows.Add(r);          }          sw.Start();          Bulk.BulkToDB(dt);          sw.Stop();          Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));      }        Console.ReadLine();  }  

耗時圖如下：

 

可見，使用Bulk後，效率和效能明顯上升。使用Insert插入10萬資料耗時72390，而現在使用Bulk插入100萬資料才耗時17583。

 

最後再看看使用表值參數的效率，會另你大為驚訝的。

 

表值參數是SQL Server 2008新特性，簡稱TVPs。對於表值參數不熟悉的朋友，可以參考最新的book online，我也會另外寫一篇關於表值參數的部落格，不過此次不對錶值參數的概念做過多的介紹。言歸正傳，看代碼：

[c-sharp] view plain copypublic static void TableValuedToDB(DataTable dt)  {      SqlConnection sqlConn = new SqlConnection(        ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);      const string TSqlStatement =       "insert into BulkTestTable (Id,UserName,Pwd)" +       " SELECT nc.Id, nc.UserName,nc.Pwd" +       " FROM @NewBulkTestTvp AS nc";      SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);      SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);      catParam.SqlDbType = SqlDbType.Structured;      //表值參數的名字叫BulkUdt，在上面的建立測試環境的SQL中有。      catParam.TypeName = "dbo.BulkUdt";      try      {        sqlConn.Open();        if (dt != null && dt.Rows.Count != 0)        {            cmd.ExecuteNonQuery();        }      }      catch (Exception ex)      {        throw ex;      }      finally      {        sqlConn.Close();      }  }    public static DataTable GetTableSchema()  {      DataTable dt = new DataTable();      dt.Columns.AddRange(new DataColumn[]{        new DataColumn("Id",typeof(int)),        new DataColumn("UserName",typeof(string)),        new DataColumn("Pwd",typeof(string))});        return dt;  }    static void Main(string[] args)  {      Stopwatch sw = new Stopwatch();      for (int multiply = 0; multiply < 10; multiply++)      {          DataTable dt = TableValued.GetTableSchema();          for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)          {                      DataRow r = dt.NewRow();              r[0] = count;              r[1] = string.Format("User-{0}", count * multiply);              r[2] = string.Format("Pwd-{0}", count * multiply);              dt.Rows.Add(r);          }          sw.Start();          TableValued.TableValuedToDB(dt);          sw.Stop();          Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));      }        Console.ReadLine();  }  

耗時圖如下：

比Bulk還快5秒。

【ADO.NET-中級】百萬級資料的批量插入的兩種方法測試