你的位置:首页 > ASP.net教程

[ASP.net教程]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行那就未必了。所以,如果单条数据较大,建议每几百或几千行的时候提交一次,这个数字具体多大需要量体裁衣,平衡内存消耗。