序言

現在有一個需求是将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、你可以忘記點贊加關注，但千萬不要忘記掃碼打賞哦。

MSSQL資料批量插入優化詳細常見的BULK INSERT 資料集插入優化

序言

壓測mvc的http接口看下資料

最常見的insert做法

利于MSSQL資料庫的使用者自定義表類型做優化

使用者自定義表類型

常見的BULK INSERT 資料集插入優化

總結

繼續閱讀

寶馬iX1内飾諜照曝光，或将搭載iDrive 8系統，有望年内正式亮相

大學生吸煙檢讨書

BCP導出導入大容量資料實踐

Bulk Insert：将文本資料（csv和txt）導入到資料庫中

SqlServer優化之批量插入（SqlBulkCopy、表值參數）

CSV 導入SQL Server（bulk insert方式）資料一：資料二：

MSSQL資料批量插入優化詳細 常見的BULK INSERT 資料集插入優化

序言

壓測mvc的http接口看下資料

最常見的insert做法

利于MSSQL資料庫的使用者自定義表類型做優化

使用者自定義表類型

常見的BULK INSERT 資料集插入優化

總結

繼續閱讀

MSSQL資料批量插入優化詳細常見的BULK INSERT 資料集插入優化