MSSQL資料批量插入優化詳細

張龍豪發表於2017-02-24
SQL優化

序言 

現在有一個需求是將10w條資料插入到MSSQL資料庫中，表結構如下，你會怎麼做，你感覺插入10W條資料插入到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請求介面。

這個圖告訴我們，最慢的請求只用啦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 + "毫秒。");
            }
        }

執行結果如下：

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 + "毫秒。");
                }
            }
        }

執行結果如下：

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

首先我們看下拼接這麼長的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 ,
            2

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

利於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 + "毫秒。");
                }
            }
        }

執行結果：

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

再來一遍

再來一遍

是的你沒有看錯，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 + "毫秒。");
            }

執行結果：

1秒之內完成，1秒之內完成，看完這個簡直要在1秒之內完成10w條資料的插入的節奏，逆天，逆天啊。

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

專業的點評：

表值引數的使用方法與其他基於資料集的變數的使用方法相似；但是，頻繁使用表值引數將比大型資料集要快。 大容量操作的啟動開銷比表值引數大，與之相比，表值引數在插入數目少於 1000 的行時具有很好的執行效能。

重用的表值引數可從臨時表快取中受益。 這一表快取功能可比對等的 BULK INSERT 操作提供更好的伸縮性。 使用小型行插入操作時，可以通過使用引數列表或批量語句（而不是 BULK INSERT 操作或表值引數）來獲得小的效能改進。 但是，這些方法在程式設計上不太方便，並且隨著行的增加，效能會迅速下降。

表值引數在執行效能上與對等的引數陣列實現相當甚至更好。

總結

接下來是大家最喜歡的總結內容啦，內容有三，如下：

1、希望能關注我其他的文章。

2、部落格裡面有沒有很清楚的說明白，或者你有更好的方式，那麼歡迎加入左上方的2個交流群，我們一起學習探討。

3、你可以忘記點贊加關注，但千萬不要忘記掃碼打賞哦。

相關文章