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);


        }

这里只需要对表名称,列名称及类型做修改(只需要写类型及名称即可)

参考网址

posted @ 2020-04-07 21:33  Vincent-yuan  阅读(312)  评论(0编辑  收藏  举报