澳门网络娱乐游戏平台-澳门电子游戏娱乐网址-官方直营

澳门所有电子游戏网站:MSSQL数据批量布置优化详细

序言 

前不久有一个供给是将10w条数据插入到MSSQL数据库中,表布局如下,你会如何是好,你认为插入10W条数据插入到MSSQL如下的表中须求多长期呢?

或许你的批量数码是什么插入的呢?小编前些天就此难题做个研商。

 澳门在线游戏平台网 1

压测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供给接口。

澳门在线游戏平台网 2

其风度翩翩图告诉大家,最慢的央浼只用啊4微秒。那么我们做个算法。

如开篇的要求来看,我们用小小的响适当时候间来计量。

澳门所有电子游戏网站:MSSQL数据批量布置优化详细。那就是说插入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 + "毫秒。");
            }
        }

实施结果如下:

澳门在线游戏平台网 3

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

实施结果如下:

澳门在线游戏平台网 4

哟,好诡异啊,为啥跟上二个方案还未多大分别呢?

首先大家看下拼接这么长的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 ,
            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 + "毫秒。");
                }
            }
        }

运行结果:

澳门在线游戏平台网 5

哇抓Q,不到2秒,不到2秒,怎么比每条4飞秒还快,不敢相信,是或不是运营出标题啊。

再来一回

澳门在线游戏平台网 6

再来贰次

澳门在线游戏平台网 7

是的你未曾看错,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 二〇一三 早先,对于参数化查询,表值参数也被缓存。

限制

表值参数有上边的范围:

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

奉行结果:

澳门在线游戏平台网 8

澳门在线游戏平台网 9

澳门在线游戏平台网 10

澳门在线游戏平台网 11

1秒之内完毕,1秒之内达成,看完这些几乎要在1秒之内实现10w条数据的插入的点子,逆天,逆天啊。

bulk insert详解:

专门的学问的点评:

表值参数的行使方法与其他依照数据集的变量的接受办法经常;不过,频繁使用表值参数将比大型数据集要快。 大容积操作的起步开辟比表值参数大,与之相比较,表值参数在插入数目少于 1000 的行时具备很好的奉行质量。

接纳的表值参数可从不时表缓存中收益。 这一表缓存功效可比对等的 BULK INSERT 操作提供更好的伸缩性。 使用微型行插入操作时,能够透过动用参数列表或批量言语(并不是 BULK INSERT 操作或表值参数)来赢得小的品质改正。 可是,这么些措施在编制程序上不太有利,况且随着行的扩充,质量会急迅跌落。

表值参数在施行品质上与对等的参数阵列完结特别以至越来越好。

总结

接下去是富贵人家最赏识的总括内容啦,内容有三,如下:

澳门在线游戏平台网,1、希望能关注本人别的的随笔。

2、博客里面有未有很清楚的说领会,可能您有越来越好的点子,那么招待出席左上方的2个沟通群,大家联合念书研究。

3、你能够忘记点赞加关怀,但相对不要遗忘扫码打赏哦。

本文由澳门网络娱乐游戏平台发布于数据库,转载请注明出处:澳门所有电子游戏网站:MSSQL数据批量布置优化详细

相关阅读