利用MSSQL数据库的用户自定义表类型做插入优化 2秒插入10w条数据

原文来自.
https://www.cnblogs.com/knowledgesea/p/6232461.html

有两种比较快速的插入方法.
第一种 自定义表类型 2秒插入10w
第二种 常见的BULK INSERT 数据集插入优化 1秒插入10w

先看下自定义表类型的优化方法
SQL Server 2012 之后 10w条数据，不到2秒,即可插入。

先在数据库中 新建一个类型.

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 + "毫秒。");
                }
            }
        }

首先类型大家应该很容易理解，像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 + "毫秒。");
            }