MSSQL批量插入数据优化详细(2)-木庄网络博客

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

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

创建自定义类型的详细文档：https://msdn.microsoft.com/zh-cn/library/ms175007.aspx

其次自定义类型也有一些限制，安全性：https://msdn.microsoft.com/zh-cn/library/bb522526.aspx

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

使用表值参数，可以不必创建临时表或许多参数，即可向 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详解：https://msdn.microsoft.com/zh-cn/library/ms188365.aspx

专业的点评：

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

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

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

总结

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

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

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

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