MSSQL批量插入数据优化详细

本文整理自网络，侵删。

需求

 现在有一个需求是将10w条数据插入到MSSQL数据库中，表结构如下，你会怎么做，你感觉插入10W条数据插入到MSSQL如下的表中需要多久呢？

或者你的批量数据是如何插入的呢？我今天就此问题做个探讨。

压测mvc的http接口看下数据

首先说下这里只是做个参照，来理解插入数据库的性能状况，与开篇的需求无半毛钱关系。

mvc接口代码如下：

public bool Add(CustomerFeedbackEntity m)    {      using (var conn=Connection)      {        string sql = @"ｉｎｓｅｒｔ 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分钟。那么我们奔着这个目标来做出插入方案。

最常见的ｉｎｓｅｒｔ做法

首先我们的工程师拿到需求后这样写啦段代码，如下：

//执行数据条数    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(@"ｉｎｓｅｒｔ 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(@"ｉｎｓｅｒｔ 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、查看数据库的连接情况

ｓｅｌｅｃｔ * from sysprocesses where dbid in (ｓｅｌｅｃｔ dbid from sysdatabases where name='dbname')--或者ｓｅｌｅｃｔ * FROM[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( ｓｅｌｅｃｔ   [DBID]FROM   [Master].[dbo].[SYSDATABASES]WHERE   NAME='dbname')

2、查看数据库正在执行的sql语句

ｓｅｌｅｃｔ [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 ,

点评：虽然看似得到啦优化，其实与上一个解决方案的执行过程几乎是一样的，所以就不用多说什么啦。

阅读剩余部分

相关阅读 >>

sql语句删除数据的方法

删除testdb数据库的sql命令是什么

sql中游标(cursor)的基本使用实例

navicat和sql的关系

sql中distinct只作用于一个字段的方法

sql连接服务器失败怎么办

深入了解sql注入和预防措施

复习一下sql server的差异备份

史上最全postgresqldba最常用sql

合并sql脚本文件的方法分享

更多相关阅读请进入《sql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。