本文整理自网络,侵删。
需求
现在有一个需求是将10w条数据插入到MSSQL数据库中,表结构如下,你会怎么做,你感觉插入10W条数据插入到MSSQL如下的表中需要多久呢?
或者你的批量数据是如何插入的呢?我今天就此问题做个探讨。
压测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请求接口。
这个图告诉我们,最慢的请求只用啦4毫秒。那么我们做个算法。
如开篇的需求来看,我们用最小的响应时间来计算。
那么插入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 + "毫秒。"); } }
执行结果如下:
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 + "毫秒。"); } } }
执行结果如下:
呀,好奇怪啊,为什么跟上一个方案没有多大区别呢?
首先我们看下拼接这么长的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 ,
点评:虽然看似得到啦优化,其实与上一个解决方案的执行过程几乎是一样的,所以就不用多说什么啦。
相关阅读 >>
sqlserver使用t-sql进阶之公用表表达式(cte)
mysql和oracle的区别小结(功能性能、选择、使用它们时的sql等对比)
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。