MSSQL批量插入数据优化详细

需求

 现在有一个需求是将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 ,

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

利于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等都是类型，那么这个表类型是个毛线呢？

其实他就是用户可以自己定义一个表结构然后把他当作一个类型。

创建自定义类型的详细文档：

其次自定义类型也有一些限制，安全性：

然后就是如何用这个类型，他的使用就是作为表值参数来使用的。

使用表值参数，可以不必创建临时表或许多参数，即可向 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详解：

专业的点评：

表值参数的使用方法与其他基于数据集的变量的使用方法相似；但是，频繁使用表值参数将比大型数据集要快。 大容量操作的启动开销比表值参数大，与之相比，表值参数在插入数目少于 1000 的行时具有很好的执行性能。

重用的表值参数可从临时表缓存中受益。 这一表缓存功能可比对等的 bulk insert 操作提供更好的伸缩性。 使用小型行插入操作时，可以通过使用参数列表或批量语句（而不是 bulk insert 操作或表值参数）来获得小的性能改进。 但是，这些方法在编程上不太方便，并且随着行的增加，性能会迅速下降。

表值参数在执行性能上与对等的参数阵列实现相当甚至更好。

总结

接下来是大家最喜欢的总结内容啦，内容有三，如下：

1、希望能关注我其他的文章。

2、博客里面有没有很清楚的说明白，或者你有更好的方式，那么欢迎加入左上方的2个交流群，我们一起学习探讨。

3、你可以忘记点赞加关注，但千万不要忘记扫码打赏哦。

下面是其他网友的补充：

你前面的插入，完全是拼字符串，没有用到任何“参数”，每个语句sql server都要解析，没有办法缓存，当然慢了；你可以尝试用用参数化插入，相信也是几秒就可以插入完

大批量数据插入，首选sqlbulkcopy