MSSQL批量插入數據優化詳細

2024-08-31 01:04:59

字體：大中小

來源：轉載

供稿：網友

需求

現在有一個需求是將10w條數據插入到MSSQL數據庫中，表結構如下，你會怎么做，你感覺插入10W條數據插入到MSSQL如下的表中需要多久呢？

或者你的批量數據是如何插入的呢？我今天就此問題做個探討。

MSSQL,批量插入

壓測mvc的http接口看下數據

首先說下這里只是做個參照，來理解插入數據庫的性能狀況，與開篇的需求無半毛錢關系。

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;      }    }

壓測的此mvc接口單條數據插入數據庫的聚合數據圖。

用例這樣的：5000個請求分500個線程執行post請求接口。

MSSQL,批量插入

這個圖告訴我們，最慢的請求只用啦4毫秒。那么我們做個算法。

如開篇的需求來看，我們用最小的響應時間來計算。

那么插入10w條數據到數據庫需用時=100000*4毫秒，大致是6.67分鐘。那么我們奔著這個目標來做出插入方案。

最常見的insert做法

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

//執行數據條數    int cnt = 10 * 10000;    //要插入的數據    CustomerFeedbackEntity m = new CustomerFeedbackEntity() { BusType = 1, CustomerPhone = "1888888888", BackType = 1, Content = "123123dagvhkfhsdjk肯定會撒嬌繁華的撒嬌防護等級劃分噶哈蘇德高房價盛大開放" };    //第一種    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") + "開始循環執行:" + cnt + "條sql語句 ...");        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") + "時，執行:" + cnt + "條sql語句完成 ! 耗時：" + sw.ElapsedMilliseconds + "毫秒。");      }    }

執行結果如下：

MSSQL,批量插入

10w條數據，693906毫秒，11分鐘，有沒有感覺還行，或者還可以接受的。親們，我是吐血狀不說話，繼續寫，你們看MSSQL數據庫與.Net配合插入止于哪里？

點評下：

1、不停的創建與釋放sqlcommon對象，會有性能浪費。

2、不停的與數據庫建立連接，會有很大的性能損耗。

此2點還有執行結果告訴我們，此種方式不可取，即便這是我們最常見的數據插入方式。

那么我們針對以上兩點做優化，1、創建一次sqlcommon對象，只與數據庫建立一次連接。優化改造代碼如下：

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") + "開始循環拼接:" + cnt + "條sql語句 ...");        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") + "時，循環拼接:" + cnt + "條sql語句完成 ! 耗時：" + 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") + "開始執行:" + cnt + "條sql語句 ...");          cmd.ExecuteNonQuery();          Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執行:" + cnt + "條sql語句完成 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");        }      }    }

執行結果如下：

MSSQL,批量插入

呀，好奇怪啊，為什么跟上一個方案沒有多大區別呢？

首先我們看下拼接這么長的sql語句是怎么在數據庫中是怎么執行的。

1、查看數據庫的連接情況

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、查看數據庫正在執行的sql語句

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 ,

點評：雖然看似得到啦優化，其實與上一個解決方案的執行過程幾乎是一樣的，所以就不用多說什么啦。

利于MSSQL數據庫的用戶自定義表類型做優化
依舊先上代碼，或許這樣你才能對用戶自定義表類型產生興趣。

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") + "開始循環插入內存表中:" + cnt + "條數據 ...");          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") + "時，循環插入內存表:" + cnt + "條數據完成 ! 耗時：" + 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") + "時，執行:" + cnt + "條數據的datatable的數據進數據庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");        }      }    }

運行結果：

MSSQL,批量插入

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

再來一遍

MSSQL,批量插入

再來一遍

MSSQL,批量插入

是的你沒有看錯，10w條數據，不到2秒。是不是迫不及待的要知道為什么？迫不及待的想知道我們用到的用戶自定義表類型是什么？

用戶自定義表類型

首先類型大家應該很容易理解，像int，varchar，bit等都是類型，那么這個表類型是個毛線呢？

其實他就是用戶可以自己定義一個表結構然后把他當作一個類型。

創建自定義類型的詳細文檔：https://msdn.microsoft.com/zh-cn/library/ms175007.aspx

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

然后就是如何用這個類型，他的使用就是作為表值參數來使用的。

使用表值參數，可以不必創建臨時表或許多參數，即可向 Transact-SQL 語句或例程（如存儲過程或函數）發送多行數據。

表值參數與 OLE DB 和 ODBC 中的參數數組類似，但具有更高的靈活性，且與 Transact-SQL 的集成更緊密。表值參數的另一個優勢是能夠參與基于數據集的操作。

Transact-SQL 通過引用向例程傳遞表值參數，以避免創建輸入數據的副本。可以使用表值參數創建和執行 Transact-SQL 例程，并且可以使用任何托管語言從 Transact-SQL 代碼、托管客戶端以及本機客戶端調用它們。

優點

就像其他參數一樣，表值參數的作用域也是存儲過程、函數或動態 Transact-SQL 文本。同樣，表類型變量也與使用 DECLARE 語句創建的其他任何局部變量一樣具有作用域。可以在動態 Transact-SQL 語句內聲明表值變量，并且可以將這些變量作為表值參數傳遞到存儲過程和函數。

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

首次從客戶端填充數據時，不獲取鎖。

提供簡單的編程模型。

允許在單個例程中包括復雜的業務邏輯。

減少到服務器的往返。

可以具有不同基數的表結構。

是強類型。

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

在用于存儲過程時像臨時表一樣被緩存。從 SQL Server 2012 開始，對于參數化查詢，表值參數也被緩存。

限制

表值參數有下面的限制：

SQL Server 不維護表值參數列的統計信息。

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

不能將表值參數用作 SELECT INTO 或 INSERT EXEC 語句的目標。表值參數可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存儲過程中。

常見的BULK INSERT 數據集插入優化

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") + "開始循環插入內存表中:" + cnt + "條數據 ...");        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") + "時，循環插入內存表:" + cnt + "條數據完成 ! 耗時：" + 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") + "時，執行:" + cnt + "條數據的datatable的數據進數據庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。");      }

執行結果：

MSSQL,批量插入