MSSQL数据批量插入优化详细

MSSQL数据批量插⼊优化详细
序⾔
现在有⼀个需求是将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))
{
mandTimeout =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))
{
mandTimeout =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 BY1 ,
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))
{
mandTimeout = 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 + "毫秒。

}
}
}
运⾏结果：
哇抓Q，不到2秒，不到2秒，怎么⽐每条4毫秒还快，不敢相信，是不是运⾏出问题啦。

再来⼀遍
再来⼀遍
是的你没有看错，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 2012 开始，对于参数化查询，表值参数也被缓存。

限制
表值参数有下⾯的限制：
SQL Server 不维护表值参数列的统计信息。

表值参数必须作为输⼊ READONLY 参数传递到 Transact-SQL 例程。

不能在例程体中对表值参数执⾏诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。

不能将表值参数⽤作 SELECT INTO 或 INSERT EXEC 语句的⽬标。

表值参数可以在 SELECT INTO 的 FROM ⼦句中，也可以在 INSERT EXEC 字符串或存储过程中。

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详解：
专业的点评：
表值参数的使⽤⽅法与其他基于数据集的变量的使⽤⽅法相似；但是，频繁使⽤表值参数将⽐⼤型数据集要快。

⼤容量操作的启动开销⽐表值参数⼤，与之相⽐，表值参数在插⼊数⽬少于1000 的⾏时具有很好的执⾏性能。

重⽤的表值参数可从临时表缓存中受益。

这⼀表缓存功能可⽐对等的 BULK INSERT 操作提供更好的伸缩性。

使⽤⼩型⾏插⼊操作时，可以通过使⽤参数列表或批量语句（⽽不是 BULK INSERT 操作或表值参数）来获得⼩的性能改进。

但是，这些⽅法在编程上不太⽅便，并且随着⾏的增加，性能会迅速下降。

表值参数在执⾏性能上与对等的参数阵列实现相当甚⾄更好。

总结
接下来是⼤家最喜欢的总结内容啦，内容有三，如下：
1、希望能关注我其他的⽂章。

2、博客⾥⾯有没有很清楚的说明⽩，或者你有更好的⽅式，那么欢迎加⼊左上⽅的2个交流群，我们⼀起学习探讨。

3、你可以忘记点赞加关注，但千万不要忘记扫码打赏哦。