MSSQL批量插入數(shù)據(jù)優(yōu)化詳細(xì)

更新時(shí)間：2017年07月17日 16:38:59 作者：張龍豪

這篇文章主要為大家分享一下批量插入數(shù)據(jù)的方法，有時(shí)候我們需要插入大量的數(shù)據(jù)那么就需要優(yōu)惠了，要不根本受不了

需求

現(xiàn)在有一個(gè)需求是將10w條數(shù)據(jù)插入到MSSQL數(shù)據(jù)庫(kù)中，表結(jié)構(gòu)如下，你會(huì)怎么做，你感覺(jué)插入10W條數(shù)據(jù)插入到MSSQL如下的表中需要多久呢？

或者你的批量數(shù)據(jù)是如何插入的呢？我今天就此問(wèn)題做個(gè)探討。

壓測(cè)mvc的http接口看下數(shù)據(jù)

首先說(shuō)下這里只是做個(gè)參照，來(lái)理解插入數(shù)據(jù)庫(kù)的性能狀況，與開(kāi)篇的需求無(wú)半毛錢(qián)關(guān)系。

mvc接口代碼如下：

public bool Add(CustomerFeedbackEntity m)
    {
      using (var conn=Connection)
      {
        string sql = @"INSERT INTO [dbo].[CustomerFeedback]
                      ([BusType]
                      ,[CustomerPhone]
                      ,[BackType]
                      ,[Content]
                     )
                   VALUES
                      (@BusType
                      ,@CustomerPhone
                      ,@BackType
                      ,@Content
                      )";
        return conn.Execute(sql, m) > 0;
      }
    }

壓測(cè)的此mvc接口單條數(shù)據(jù)插入數(shù)據(jù)庫(kù)的聚合數(shù)據(jù)圖。

用例這樣的：5000個(gè)請(qǐng)求分500個(gè)線程執(zhí)行post請(qǐng)求接口。

這個(gè)圖告訴我們，最慢的請(qǐng)求只用啦4毫秒。那么我們做個(gè)算法。

如開(kāi)篇的需求來(lái)看，我們用最小的響應(yīng)時(shí)間來(lái)計(jì)算。

那么插入10w條數(shù)據(jù)到數(shù)據(jù)庫(kù)需用時(shí)=100000*4毫秒，大致是6.67分鐘。那么我們奔著這個(gè)目標(biāo)來(lái)做出插入方案。

最常見(jiàn)的insert做法

首先我們的工程師拿到需求后這樣寫(xiě)啦段代碼，如下：

//執(zhí)行數(shù)據(jù)條數(shù)
    int cnt = 10 * 10000;
    //要插入的數(shù)據(jù)
    CustomerFeedbackEntity m = new CustomerFeedbackEntity() { BusType = 1, CustomerPhone = "1888888888", BackType = 1, Content = "123123dagvhkfhsdjk肯定會(huì)撒嬌繁華的撒嬌防護(hù)等級(jí)劃分噶哈蘇德高房?jī)r(jià)盛大開(kāi)放" };
    //第一種
    public void FristWay()
    {
      using (var conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        Stopwatch sw = new Stopwatch();
        sw.Start();
        StringBuilder sb = new StringBuilder();
        Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開(kāi)始循環(huán)執(zhí)行:" + cnt + "條sql語(yǔ)句 ...");
        for (int i = 0; i <= cnt; i++)
        {
          sb.Clear();
          sb.Append(@"INSERT INTO [dbo].[CustomerFeedback]
                      ([BusType]
                      ,[CustomerPhone]
                      ,[BackType]
                      ,[Content]
                     )
                   VALUES(");
          sb.Append(m.BusType);
          sb.Append(",'");
          sb.Append(m.CustomerPhone);
          sb.Append("',");
          sb.Append(m.BackType);
          sb.Append(",'");
          sb.Append(m.Content);
          sb.Append("')");
          using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
          {
            cmd.CommandTimeout = 0;
            cmd.ExecuteNonQuery();
          }
        }
        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，執(zhí)行:" + cnt + "條sql語(yǔ)句完成 ! 耗時(shí)：" + sw.ElapsedMilliseconds + "毫秒。");
      }
    }

執(zhí)行結(jié)果如下：

10w條數(shù)據(jù)，693906毫秒，11分鐘，有沒(méi)有感覺(jué)還行，或者還可以接受的。親們，我是吐血狀不說(shuō)話，繼續(xù)寫(xiě)，你們看MSSQL數(shù)據(jù)庫(kù)與.Net配合插入止于哪里？

點(diǎn)評(píng)下：

1、不停的創(chuàng)建與釋放sqlcommon對(duì)象，會(huì)有性能浪費(fèi)。

2、不停的與數(shù)據(jù)庫(kù)建立連接，會(huì)有很大的性能損耗。

此2點(diǎn)還有執(zhí)行結(jié)果告訴我們，此種方式不可取，即便這是我們最常見(jiàn)的數(shù)據(jù)插入方式。

那么我們針對(duì)以上兩點(diǎn)做優(yōu)化，1、創(chuàng)建一次sqlcommon對(duì)象，只與數(shù)據(jù)庫(kù)建立一次連接。優(yōu)化改造代碼如下：

public void SecondWay()
    {
      using (var conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        Stopwatch sw = new Stopwatch();
        sw.Start();
        StringBuilder sb = new StringBuilder();
        Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開(kāi)始循環(huán)拼接:" + cnt + "條sql語(yǔ)句 ...");
        for (int i = 0; i <= cnt; i++)
        {
          sb.Append(@"INSERT INTO [dbo].[CustomerFeedback]
                      ([BusType]
                      ,[CustomerPhone]
                      ,[BackType]
                      ,[Content]
                     )
                   VALUES(");
          sb.Append(m.BusType);
          sb.Append(",'");
          sb.Append(m.CustomerPhone);
          sb.Append("',");
          sb.Append(m.BackType);
          sb.Append(",'");
          sb.Append(m.Content);
          sb.Append("')");
        }
        var result = sw.ElapsedMilliseconds;
        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，循環(huán)拼接:" + cnt + "條sql語(yǔ)句完成 ! 耗時(shí)：" + result + "毫秒。");
        using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
        {
          cmd.CommandTimeout = 0;
          Stopwatch sw1 = new Stopwatch();
          sw1.Start();
          Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開(kāi)始執(zhí)行:" + cnt + "條sql語(yǔ)句 ...");
          cmd.ExecuteNonQuery();
          Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，執(zhí)行:" + cnt + "條sql語(yǔ)句完成 ! 耗時(shí)：" + sw1.ElapsedMilliseconds + "毫秒。");
        }
      }
    }

執(zhí)行結(jié)果如下：

呀，好奇怪啊，為什么跟上一個(gè)方案沒(méi)有多大區(qū)別呢？

首先我們看下拼接這么長(zhǎng)的sql語(yǔ)句是怎么在數(shù)據(jù)庫(kù)中是怎么執(zhí)行的。

1、查看數(shù)據(jù)庫(kù)的連接情況

select * from sysprocesses where dbid in (select dbid from sysdatabases where name='dbname')
--或者
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT 
  [DBID]
FROM 
  [Master].[dbo].[SYSDATABASES]
WHERE 
  NAME='dbname'
)

2、查看數(shù)據(jù)庫(kù)正在執(zhí)行的sql語(yǔ)句

SELECT [Spid] = session_id ,
      ecid ,
      [Database] = DB_NAME(sp.dbid) ,
      [User] = nt_username ,
      [Status] = er.status ,
      [Wait] = wait_type ,
      [Individual Query] = SUBSTRING(qt.text,
                      er.statement_start_offset / 2,
                      ( CASE WHEN er.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                            * 2
                         ELSE er.statement_end_offset
                       END - er.statement_start_offset )
                      / 2) ,
      [Parent Query] = qt.text ,
      Program = program_name ,
      hostname ,
      nt_domain ,
      start_time
  FROM  sys.dm_exec_requests er
      INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
  WHERE  session_id > 50 -- Ignore system spids.
      AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY  1 ,

點(diǎn)評(píng)：雖然看似得到啦優(yōu)化，其實(shí)與上一個(gè)解決方案的執(zhí)行過(guò)程幾乎是一樣的，所以就不用多說(shuō)什么啦。

利于MSSQL數(shù)據(jù)庫(kù)的用戶(hù)自定義表類(lèi)型做優(yōu)化
依舊先上代碼，或許這樣你才能對(duì)用戶(hù)自定義表類(lèi)型產(chǎn)生興趣。

CREATE TYPE CustomerFeedbackTemp AS TABLE(
BusType int NOT NULL,
CustomerPhone varchar(40) NOT NULL,
BackType int NOT NULL,
Content nvarchar(1000) NOT NULL
)

public void ThirdWay()
    {
      Stopwatch sw = new Stopwatch();
      Stopwatch sw1 = new Stopwatch();
      DataTable dt = GetTable();
      using (var conn = new SqlConnection(ConnStr))
      {
        string sql = @"INSERT INTO[dbo].[CustomerFeedback]
                      ([BusType]
                      ,[CustomerPhone]
                      ,[BackType]
                      ,[Content]
                     ) select BusType,CustomerPhone,BackType,[Content] from @TempTb";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
          cmd.CommandTimeout = 0;
          SqlParameter catParam = cmd.Parameters.AddWithValue("@TempTb", dt);
          catParam.SqlDbType = SqlDbType.Structured;
          catParam.TypeName = "dbo.CustomerFeedbackTemp";
          conn.Open();
          Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開(kāi)始循環(huán)插入內(nèi)存表中:" + cnt + "條數(shù)據(jù) ...");
          sw.Start();
          for (int i = 0; i < cnt; i++)
          {
            DataRow dr = dt.NewRow();
            dr[0] = m.BusType;
            dr[1] = m.CustomerPhone;
            dr[2] = m.BackType;
            dr[3] = m.Content;
            dt.Rows.Add(dr);
          }
          Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，循環(huán)插入內(nèi)存表:" + cnt + "條數(shù)據(jù)完成 ! 耗時(shí)：" + sw.ElapsedMilliseconds + "毫秒。");
          sw1.Start();
          if (dt != null && dt.Rows.Count != 0)
          {
            cmd.ExecuteNonQuery();
            sw.Stop();
          }
          Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，執(zhí)行:" + cnt + "條數(shù)據(jù)的datatable的數(shù)據(jù)進(jìn)數(shù)據(jù)庫(kù) ! 耗時(shí)：" + sw1.ElapsedMilliseconds + "毫秒。");
        }
      }
    }

運(yùn)行結(jié)果：

哇抓Q，不到2秒，不到2秒，怎么比每條4毫秒還快，不敢相信，是不是運(yùn)行出問(wèn)題啦。

再來(lái)一遍

是的你沒(méi)有看錯(cuò)，10w條數(shù)據(jù)，不到2秒。是不是迫不及待的要知道為什么？迫不及待的想知道我們用到的用戶(hù)自定義表類(lèi)型是什么？

用戶(hù)自定義表類(lèi)型

首先類(lèi)型大家應(yīng)該很容易理解，像int，varchar，bit等都是類(lèi)型，那么這個(gè)表類(lèi)型是個(gè)毛線呢？

其實(shí)他就是用戶(hù)可以自己定義一個(gè)表結(jié)構(gòu)然后把他當(dāng)作一個(gè)類(lèi)型。

創(chuàng)建自定義類(lèi)型的詳細(xì)文檔：https://msdn.microsoft.com/zh-cn/library/ms175007.aspx

其次自定義類(lèi)型也有一些限制，安全性：https://msdn.microsoft.com/zh-cn/library/bb522526.aspx

然后就是如何用這個(gè)類(lèi)型，他的使用就是作為表值參數(shù)來(lái)使用的。

使用表值參數(shù)，可以不必創(chuàng)建臨時(shí)表或許多參數(shù)，即可向 Transact-SQL 語(yǔ)句或例程（如存儲(chǔ)過(guò)程或函數(shù)）發(fā)送多行數(shù)據(jù)。

表值參數(shù)與 OLE DB 和 ODBC 中的參數(shù)數(shù)組類(lèi)似，但具有更高的靈活性，且與 Transact-SQL 的集成更緊密。表值參數(shù)的另一個(gè)優(yōu)勢(shì)是能夠參與基于數(shù)據(jù)集的操作。

Transact-SQL 通過(guò)引用向例程傳遞表值參數(shù)，以避免創(chuàng)建輸入數(shù)據(jù)的副本。可以使用表值參數(shù)創(chuàng)建和執(zhí)行 Transact-SQL 例程，并且可以使用任何托管語(yǔ)言從 Transact-SQL 代碼、托管客戶(hù)端以及本機(jī)客戶(hù)端調(diào)用它們。

優(yōu)點(diǎn)

就像其他參數(shù)一樣，表值參數(shù)的作用域也是存儲(chǔ)過(guò)程、函數(shù)或動(dòng)態(tài) Transact-SQL 文本。同樣，表類(lèi)型變量也與使用 DECLARE 語(yǔ)句創(chuàng)建的其他任何局部變量一樣具有作用域。可以在動(dòng)態(tài) Transact-SQL 語(yǔ)句內(nèi)聲明表值變量，并且可以將這些變量作為表值參數(shù)傳遞到存儲(chǔ)過(guò)程和函數(shù)。

表值參數(shù)具有更高的靈活性，在某些情況下，可比臨時(shí)表或其他傳遞參數(shù)列表的方法提供更好的性能。表值參數(shù)具有以下優(yōu)勢(shì)：

首次從客戶(hù)端填充數(shù)據(jù)時(shí)，不獲取鎖。

提供簡(jiǎn)單的編程模型。

允許在單個(gè)例程中包括復(fù)雜的業(yè)務(wù)邏輯。

減少到服務(wù)器的往返。

可以具有不同基數(shù)的表結(jié)構(gòu)。

是強(qiáng)類(lèi)型。

使客戶(hù)端可以指定排序順序和唯一鍵。

在用于存儲(chǔ)過(guò)程時(shí)像臨時(shí)表一樣被緩存。從 SQL Server 2012 開(kāi)始，對(duì)于參數(shù)化查詢(xún)，表值參數(shù)也被緩存。

限制

表值參數(shù)有下面的限制：

SQL Server 不維護(hù)表值參數(shù)列的統(tǒng)計(jì)信息。

表值參數(shù)必須作為輸入 READONLY 參數(shù)傳遞到 Transact-SQL 例程。不能在例程體中對(duì)表值參數(shù)執(zhí)行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。

不能將表值參數(shù)用作 SELECT INTO 或 INSERT EXEC 語(yǔ)句的目標(biāo)。表值參數(shù)可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存儲(chǔ)過(guò)程中。

常見(jiàn)的BULK INSERT 數(shù)據(jù)集插入優(yōu)化

public void FourWay()
    {

      Stopwatch sw = new Stopwatch();
      Stopwatch sw1 = new Stopwatch();
      DataTable dt = GetTable();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
        bulkCopy.BulkCopyTimeout = 0;
        bulkCopy.DestinationTableName = "CustomerFeedback";
        bulkCopy.BatchSize = dt.Rows.Count;
        conn.Open();
        Console.WriteLine("從：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "開(kāi)始循環(huán)插入內(nèi)存表中:" + cnt + "條數(shù)據(jù) ...");
        sw.Start();
        for (int i = 0; i < cnt; i++)
        {
          DataRow dr = dt.NewRow();
          dr[0] = m.BusType;
          dr[1] = m.CustomerPhone;
          dr[2] = m.BackType;
          dr[3] = m.Content;
          dt.Rows.Add(dr);
        }
        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，循環(huán)插入內(nèi)存表:" + cnt + "條數(shù)據(jù)完成 ! 耗時(shí)：" + sw.ElapsedMilliseconds + "毫秒。");
        sw1.Start();
        if (dt != null && dt.Rows.Count != 0)
        {
          bulkCopy.WriteToServer(dt);
          sw.Stop();
        }
        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時(shí)，執(zhí)行:" + cnt + "條數(shù)據(jù)的datatable的數(shù)據(jù)進(jìn)數(shù)據(jù)庫(kù) ! 耗時(shí)：" + sw1.ElapsedMilliseconds + "毫秒。");
      }

執(zhí)行結(jié)果：

1秒之內(nèi)完成，1秒之內(nèi)完成，看完這個(gè)簡(jiǎn)直要在1秒之內(nèi)完成10w條數(shù)據(jù)的插入的節(jié)奏，逆天，逆天啊。

bulk insert詳解：https://msdn.microsoft.com/zh-cn/library/ms188365.aspx

專(zhuān)業(yè)的點(diǎn)評(píng)：

表值參數(shù)的使用方法與其他基于數(shù)據(jù)集的變量的使用方法相似；但是，頻繁使用表值參數(shù)將比大型數(shù)據(jù)集要快。大容量操作的啟動(dòng)開(kāi)銷(xiāo)比表值參數(shù)大，與之相比，表值參數(shù)在插入數(shù)目少于 1000 的行時(shí)具有很好的執(zhí)行性能。

重用的表值參數(shù)可從臨時(shí)表緩存中受益。這一表緩存功能可比對(duì)等的 BULK INSERT 操作提供更好的伸縮性。使用小型行插入操作時(shí)，可以通過(guò)使用參數(shù)列表或批量語(yǔ)句（而不是 BULK INSERT 操作或表值參數(shù)）來(lái)獲得小的性能改進(jìn)。但是，這些方法在編程上不太方便，并且隨著行的增加，性能會(huì)迅速下降。

表值參數(shù)在執(zhí)行性能上與對(duì)等的參數(shù)陣列實(shí)現(xiàn)相當(dāng)甚至更好。

總結(jié)

接下來(lái)是大家最喜歡的總結(jié)內(nèi)容啦，內(nèi)容有三，如下：

1、希望能關(guān)注我其他的文章。

2、博客里面有沒(méi)有很清楚的說(shuō)明白，或者你有更好的方式，那么歡迎加入左上方的2個(gè)交流群，我們一起學(xué)習(xí)探討。

3、你可以忘記點(diǎn)贊加關(guān)注，但千萬(wàn)不要忘記掃碼打賞哦。

下面是其他網(wǎng)友的補(bǔ)充：

你前面的插入，完全是拼字符串，沒(méi)有用到任何“參數(shù)”，每個(gè)語(yǔ)句SQL SERVER都要解析，沒(méi)有辦法緩存，當(dāng)然慢了；你可以嘗試用用參數(shù)化插入，相信也是幾秒就可以插入完

大批量數(shù)據(jù)插入，首選SqlBulkCopy