需求
現(xiàn)在有一個需求是將10w條數(shù)據(jù)插入到MSSQL數(shù)據(jù)庫中，表結(jié)構(gòu)如下，你會怎么做，你感覺插入10W條數(shù)據(jù)插入到MSSQL如下的表中需要多久呢？
或者你的批量數(shù)據(jù)是如何插入的呢？我今天就此問題做個探討。
壓測mvc的http接口看下數(shù)據(jù)
首先說下這里只是做個參照，來理解插入數(shù)據(jù)庫的性能狀況，與開篇的需求無半毛錢關(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; } }壓測的此mvc接口單條數(shù)據(jù)插入數(shù)據(jù)庫的聚合數(shù)據(jù)圖。
用例這樣的：5000個請求分500個線程執(zhí)行post請求接口。
這個圖告訴我們，最慢的請求只用啦4毫秒。那么我們做個算法。
如開篇的需求來看，我們用最小的響應(yīng)時間來計(jì)算。
那么插入10w條數(shù)據(jù)到數(shù)據(jù)庫需用時=100000*4毫秒，大致是6.67分鐘。那么我們奔著這個目標(biāo)來做出插入方案。
最常見的insert做法
首先我們的工程師拿到需求后這樣寫啦段代碼，如下：
//執(zhí)行數(shù)據(jù)條數(shù) int cnt = 10 * 10000; //要插入的數(shù)據(jù) CustomerFeedbackEntity m = new CustomerFeedbackEntity() { BusType = 1, CustomerPhone = "1888888888", BackType = 1, Content = "123123dagvhkfhsdjk肯定會撒嬌繁華的撒嬌防護(hù)等級劃分噶哈蘇德高房價盛大開放" }; //第一種 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") + "開始循環(huán)執(zhí)行:" + 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") + "時，執(zhí)行:" + cnt + "條sql語句完成 ! 耗時：" + sw.ElapsedMilliseconds + "毫秒。"); } }執(zhí)行結(jié)果如下：
10w條數(shù)據(jù)，693906毫秒，11分鐘，有沒有感覺還行，或者還可以接受的。親們，我是吐血狀不說話，繼續(xù)寫，你們看MSSQL數(shù)據(jù)庫與.Net配合插入止于哪里？
點(diǎn)評下：
1、不停的創(chuàng)建與釋放sqlcommon對象，會有性能浪費(fèi)。
2、不停的與數(shù)據(jù)庫建立連接，會有很大的性能損耗。
此2點(diǎn)還有執(zhí)行結(jié)果告訴我們，此種方式不可取，即便這是我們最常見的數(shù)據(jù)插入方式。
那么我們針對以上兩點(diǎn)做優(yōu)化，1、創(chuàng)建一次sqlcommon對象，只與數(shù)據(jù)庫建立一次連接。優(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") + "開始循環(huán)拼接:" + 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") + "時，循環(huán)拼接:" + 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") + "開始執(zhí)行:" + cnt + "條sql語句 ..."); cmd.ExecuteNonQuery(); Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss fff") + "時，執(zhí)行:" + cnt + "條sql語句完成 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。"); } } }執(zhí)行結(jié)果如下：
呀，好奇怪啊，為什么跟上一個方案沒有多大區(qū)別呢？
首先我們看下拼接這么長的sql語句是怎么在數(shù)據(jù)庫中是怎么執(zhí)行的。
1、查看數(shù)據(jù)庫的連接情況
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ù)庫正在執(zhí)行的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 ,
點(diǎn)評：雖然看似得到啦優(yōu)化，其實(shí)與上一個解決方案的執(zhí)行過程幾乎是一樣的，所以就不用多說什么啦。
利于MSSQL數(shù)據(jù)庫的用戶自定義表類型做優(yōu)化
依舊先上代碼，或許這樣你才能對用戶自定義表類型產(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") + "開始循環(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") + "時，循環(huán)插入內(nèi)存表:" + cnt + "條數(shù)據(jù)完成 ! 耗時：" + 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") + "時，執(zhí)行:" + cnt + "條數(shù)據(jù)的datatable的數(shù)據(jù)進(jìn)數(shù)據(jù)庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。"); } } }運(yùn)行結(jié)果：
哇抓Q，不到2秒，不到2秒，怎么比每條4毫秒還快，不敢相信，是不是運(yùn)行出問題啦。
再來一遍
再來一遍
是的你沒有看錯，10w條數(shù)據(jù)，不到2秒。是不是迫不及待的要知道為什么？迫不及待的想知道我們用到的用戶自定義表類型是什么？
用戶自定義表類型
首先類型大家應(yīng)該很容易理解，像int，varchar，bit等都是類型，那么這個表類型是個毛線呢？
其實(shí)他就是用戶可以自己定義一個表結(jié)構(gòu)然后把他當(dāng)作一個類型。
創(chuàng)建自定義類型的詳細(xì)文檔：https://msdn.microsoft.com/zh-cn/library/ms175007.aspx
其次自定義類型也有一些限制，安全性：https://msdn.microsoft.com/zh-cn/library/bb522526.aspx
然后就是如何用這個類型，他的使用就是作為表值參數(shù)來使用的。
使用表值參數(shù)，可以不必創(chuàng)建臨時表或許多參數(shù)，即可向 Transact-SQL 語句或例程（如存儲過程或函數(shù)）發(fā)送多行數(shù)據(jù)。
表值參數(shù)與 OLE DB 和 ODBC 中的參數(shù)數(shù)組類似，但具有更高的靈活性，且與 Transact-SQL 的集成更緊密。 表值參數(shù)的另一個優(yōu)勢是能夠參與基于數(shù)據(jù)集的操作。
Transact-SQL 通過引用向例程傳遞表值參數(shù)，以避免創(chuàng)建輸入數(shù)據(jù)的副本。 可以使用表值參數(shù)創(chuàng)建和執(zhí)行 Transact-SQL 例程，并且可以使用任何托管語言從 Transact-SQL 代碼、托管客戶端以及本機(jī)客戶端調(diào)用它們。
優(yōu)點(diǎn)
就像其他參數(shù)一樣，表值參數(shù)的作用域也是存儲過程、函數(shù)或動態(tài) Transact-SQL 文本。 同樣，表類型變量也與使用 DECLARE 語句創(chuàng)建的其他任何局部變量一樣具有作用域。 可以在動態(tài) Transact-SQL 語句內(nèi)聲明表值變量，并且可以將這些變量作為表值參數(shù)傳遞到存儲過程和函數(shù)。
表值參數(shù)具有更高的靈活性，在某些情況下，可比臨時表或其他傳遞參數(shù)列表的方法提供更好的性能。 表值參數(shù)具有以下優(yōu)勢：
首次從客戶端填充數(shù)據(jù)時，不獲取鎖。
提供簡單的編程模型。
允許在單個例程中包括復(fù)雜的業(yè)務(wù)邏輯。
減少到服務(wù)器的往返。
可以具有不同基數(shù)的表結(jié)構(gòu)。
是強(qiáng)類型。
使客戶端可以指定排序順序和唯一鍵。
在用于存儲過程時像臨時表一樣被緩存。 從 SQL Server 2012 開始，對于參數(shù)化查詢，表值參數(shù)也被緩存。
限制
表值參數(shù)有下面的限制：
SQL Server 不維護(hù)表值參數(shù)列的統(tǒng)計(jì)信息。
表值參數(shù)必須作為輸入 READONLY 參數(shù)傳遞到 Transact-SQL 例程。 不能在例程體中對表值參數(shù)執(zhí)行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
不能將表值參數(shù)用作 SELECT INTO 或 INSERT EXEC 語句的目標(biāo)。 表值參數(shù)可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存儲過程中。
常見的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") + "開始循環(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") + "時，循環(huán)插入內(nèi)存表:" + cnt + "條數(shù)據(jù)完成 ! 耗時：" + 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") + "時，執(zhí)行:" + cnt + "條數(shù)據(jù)的datatable的數(shù)據(jù)進(jìn)數(shù)據(jù)庫 ! 耗時：" + sw1.ElapsedMilliseconds + "毫秒。"); }執(zhí)行結(jié)果：
1秒之內(nèi)完成，1秒之內(nèi)完成，看完這個簡直要在1秒之內(nèi)完成10w條數(shù)據(jù)的插入的節(jié)奏，逆天，逆天啊。
bulk insert詳解：https://msdn.microsoft.com/zh-cn/library/ms188365.aspx
專業(yè)的點(diǎn)評：
表值參數(shù)的使用方法與其他基于數(shù)據(jù)集的變量的使用方法相似；但是，頻繁使用表值參數(shù)將比大型數(shù)據(jù)集要快。 大容量操作的啟動開銷比表值參數(shù)大，與之相比，表值參數(shù)在插入數(shù)目少于 1000 的行時具有很好的執(zhí)行性能。
重用的表值參數(shù)可從臨時表緩存中受益。 這一表緩存功能可比對等的 BULK INSERT 操作提供更好的伸縮性。 使用小型行插入操作時，可以通過使用參數(shù)列表或批量語句（而不是 BULK INSERT 操作或表值參數(shù)）來獲得小的性能改進(jìn)。 但是，這些方法在編程上不太方便，并且隨著行的增加，性能會迅速下降。
表值參數(shù)在執(zhí)行性能上與對等的參數(shù)陣列實(shí)現(xiàn)相當(dāng)甚至更好。
總結(jié)
接下來是大家最喜歡的總結(jié)內(nèi)容啦，內(nèi)容有三，如下：
1、希望能關(guān)注我其他的文章。
2、博客里面有沒有很清楚的說明白，或者你有更好的方式，那么歡迎加入左上方的2個交流群，我們一起學(xué)習(xí)探討。
3、你可以忘記點(diǎn)贊加關(guān)注，但千萬不要忘記掃碼打賞哦。
下面是其他網(wǎng)友的補(bǔ)充：
你前面的插入，完全是拼字符串，沒有用到任何“參數(shù)”，每個語句SQL SERVER都要解析，沒有辦法緩存，當(dāng)然慢了；你可以嘗試用用參數(shù)化插入，相信也是幾秒就可以插入完
大批量數(shù)據(jù)插入，首選SqlBulkCopy
新聞熱點(diǎn)
疑難解答
圖片精選
