SqlServer資料插入效能小記

標籤：

　　對於SQL效能，已經很久沒關注了。由於近期項目既沒有幾千萬的海量資料也沒有過多的效能要求，所以在效能上也就沒下太多功夫。然而，前不久和朋友閑談間話題一轉就說到程式上了，他說他用Delphi做了個資料匯入的功能，插入資料的時候感覺很慢。以個人對SQL的瞭解，就建議他使用批量插入的方式，並很認真的告訴他這樣應該會快很多。而在實際工作中，類似大量匯入資料的功能是非常常見的，也許一個不小心我們就搞掛了伺服器。那就究竟要怎麼做才能給伺服器留條活路，讓使用者不用點完上傳看部電影再看上傳結果呢？為此做了個小實驗，並簡單說下自己的看法。

　　環境：

　　　　CPU : i7;  

　　　　記憶體 : 6G;

　　　　資料庫：SqlServer2008;

　　　　資料量：10W

　　

　　實驗內容：

　　　　建立LoopInsert 和BatchInsert兩個函數，LoopInsert中使用拼接的方式產生insert into xxx values(...) insert into xxx values(...) ...形式的sql執行並返回sql的執行時間，BatchInsert中同樣使用拼接sql的方式產生insert into xxx values(...),(...),(...)形似的sql並返回sql的執行時間。利用控制台程式多次執行兩個函數，並輸出執行結果。

 

　　表結構: 

CREATE TABLE TQRCode    (      ID INT PRIMARY KEY  IDENTITY(1, 1) ,      Name NVARCHAR(300) ,      Remark NVARCHAR(300)    )

　　

　　C#實現代碼： 

 1     public class DataInertTest 2     { 3         /// <summary> 4         /// 迴圈插入 5         /// </summary> 6         /// <returns>執行時間（秒）</returns> 7         public double LoopInsert(int count) 8         {  9             StringBuilder sql = new StringBuilder();10             for (int i = 0; i < count; i++)11             {12                 sql.Append(" Insert into TQRCode(Name,Remark) values(‘這是第").Append(i).Append("條資料‘,‘這是第").Append(i).Append("條資料_remark‘) ");13             }14             //時間統計15             var stopwatch = new Stopwatch();16             stopwatch.Start();17             new Helper().Excute(sql.ToString());18             return stopwatch.Elapsed.TotalMilliseconds;19         }20 21         /// <summary>22         /// 批量插入23         /// </summary>24         /// <returns>執行時間（秒）</returns>25         public double BatchInsert(int count)26         { 27             StringBuilder sql = new StringBuilder();28             sql.Append(" Insert into TQRCode(Name,Remark) values ");29             for (int i = 0; i < count; i++)30             {31 32                 sql.Append(" (‘這是第").Append(i).Append("條資料‘,‘這是第").Append(i).Append("條資料_remark‘) ");33                 if (i % 500 == 0)34                 { 35                     sql.Append(" Insert into TQRCode(Name,Remark) values ");36                 }37                 else if (i < count - 1)38                 {39                     sql.Append(",");40                 }41             }42 43             //時間統計44             var stopwatch = new Stopwatch();45             stopwatch.Start(); 46             new Helper().Excute(sql.ToString());47             return stopwatch.Elapsed.TotalMilliseconds;48         }49     }

C#實現代碼

　　註：sqlserver中單次批量插入資料最多1000條否則會提示我們：The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. 　

 

　測試代碼：

 1     class Program 2     { 3         static void Main(string[] args) 4         { 5             for (int i = 0; i < 3; i++) 6             { 7                 var obj = new DataInertTest(); 8                 var t1 = obj.LoopInsert(100000); 9                 var t2 = obj.BatchInsert(100000);10 11                 Console.WriteLine("LoopInsert  : {0}", t1);12                 Console.WriteLine("BatchInsert : {0}", t2);13                 Console.WriteLine("--------------------------------------------------");14               15             } 16             Console.ReadLine();17         } 18     }

測試代碼

　　

　　測試結果：

 　　執行了3次結果很明顯，效率差距在10倍以上。批量插入的方式之所以比迴圈插入快，主要因為sqlserver中每個insert into 都是一個獨立的事務，迴圈插入500條資料就是500個事務，而一次插入500條資料，就只有一個事務。事務減少了消耗自然也就小了。且頻繁的事務提交相當影響資料庫的效能，也就起到了影響整個系統效能的作用（嘿嘿，一不小心也許伺服器就掛了）。

　　需要注意的是，測試中因為資料量不大所以兩種方式都是採用的一次入庫的方式，這樣做可以減少資料庫連接次數。但是這樣做有個很大的弊端：記憶體消耗會很大。10w資料的sql拼接還好，如果是100w行那就未必了。所以，如果單條資料較大，建議每幾百或幾千行的時候提交一次，這個數字具體多大需要量體裁衣，平衡記憶體消耗。

　　 

 

SqlServer資料插入效能小記