MSSQL数据批量插入优化详细
插入10w条数据的操作
最常见的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分钟
点评下：
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 + "毫秒。"); } } }
执行结果如下：
呀，好奇怪啊，为什么跟上一个方案没有多大区别呢？
点评：虽然看似得到啦优化，其实与上一个解决方案的执行过程几乎是一样的，所以就不用多说什么啦。
利于MSSQL数据库的用户自定义表类型做优化
依旧先上代码
CREATE TYPE CustomerFeedbackTemp AS TABLE( BusType int NOT NULL, CustomerPhone varchar(40) NOT NULL, BackType int NOT NULL, Content nvarchar(1000) NOT NULL )
上面的脚本是在创建了 CustomerFeedback 表之后 额外创建的。
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 + "毫秒。"); } } }
运行结果：
是的你没有看错，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 文本。 同样，表类型变量也与使用 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 的行时具有很好的执行性能。
另外，这里提供下GetTable()方法的类似实现
public static DataTable GetTable() { // Create a new DataTable. System.Data.DataTable table = new DataTable("ParentTable"); // Declare variables for DataColumn and DataRow objects. DataColumn column; DataRow row; // Create new DataColumn, set DataType, // ColumnName and add to DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "id"; column.ReadOnly = true; column.Unique = true; // Add the Column to the DataColumnCollection. table.Columns.Add(column); // Create second column. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ParentItem"; column.AutoIncrement = false; column.Caption = "ParentItem"; column.ReadOnly = false; column.Unique = false; // Add the column to the table. table.Columns.Add(column); }
这里只需要对表名称，列名称及类型做修改(只需要写类型及名称即可)
