SQL批量插入优化：10万条数据高效入库策略
22 浏览量
更新于2024-08-31
收藏 165KB PDF 举报
"MSSQL批量插入数据优化详细"
在数据库操作中，特别是在处理大量数据时，高效的批量插入是至关重要的。MSSQL Server提供多种方法来优化这一过程，以提高性能并减少插入时间。以下是对MSSQL批量插入数据优化的一些关键点：
1. **使用参数化SQL语句**：
如示例代码所示，使用参数化SQL语句可以避免SQL注入，并且通常比直接拼接字符串更高效。`@BusType`, `@CustomerPhone`, `@BackType`, `@Content` 是参数化的占位符。
2. **事务处理**：
将多个插入操作封装在一个事务中，可以显著减少数据库的锁定和提交开销。在大规模插入时，可以将数据分组，例如每100或1000条数据作为一个事务提交。
3. **批量插入API**：
MSSQL Server 提供了 `SqlBulkCopy` 类，专门用于高效地批量导入数据。它允许一次性将整个数据集（如DataTable或IDataReader）复制到表中，速度远超单条插入。
4. **减少网络往返**：
在示例中，压测结果显示单条插入的最小响应时间为4毫秒。减少网络通信次数是提高性能的关键。通过批量插入，我们可以显著减少客户端与服务器间的通信次数。
5. **预编译存储过程**：
创建存储过程进行批量插入，可以预先编译SQL，提升执行效率。存储过程中可以接受表值参数，这样可以一次性传递大量数据。
6. **禁用索引**：
在进行大规模插入时，可以临时禁用表上的非主键索引，因为创建索引会增加写入操作的时间。插入完成后记得重新启用它们。
7. **优化硬件**：
使用更快的硬盘（如SSD）和足够的内存可以显著提高数据插入速度，因为这将减少磁盘I/O和内存交换。
8. **调整数据库配置**：
增大日志文件大小，优化事务日志的性能；调整数据库的缓冲池大小，增加缓存数据的能力，都能提升性能。
9. **并发执行**：
如果数据库支持，可以考虑并发执行多个插入任务，但这需要谨慎，以防止过度争用资源。
10. **监控和调整**：
实际操作时，需要监控SQL Server的性能计数器，如CPU利用率、磁盘I/O和内存使用情况，以便于调整批量插入的策略和大小。
优化MSSQL批量插入数据涉及到多个层面，包括SQL编写方式、事务管理、使用特定API、硬件优化以及数据库配置调整等。根据具体需求和环境，选择合适的方法可以显著提升数据插入的效率。在10万条数据的例子中，通过以上优化，有望将6.67分钟的插入时间大大缩短。
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))
{
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配合插入止于哪里？
下载后可阅读完整内容，剩余3页未读，立即下载
2021-01-21 上传
2020-09-06 上传
2020-09-09 上传
2020-12-16 上传
2020-12-15 上传
2020-12-15 上传
weixin_38642636
- 粉丝: 12
- 资源: 931
我的内容管理
展开
最新资源
- cpp_from_control_to_objects_8e：从C到对象，从控制结构开始，第8版
- import：R的导入机制
- vue2+vue-router+es6+webpack+node+mongodb的项目.zip
- Golang中的神经网络+培训框架-Golang开发
- 仅在页脚部分的最后一页的最底部打印表格页脚
- mac-config:Brewfile和脚本来设置全新的Mac安装
- writexl：轻巧的便携式数据帧，用于R的xlsx导出器
- Bootstrap模态登录框
- exif_read.rar_图形图像处理_Visual_C++_
- 福橘-股票行情-crx插件
- :magnifying_glass_tilted_right::bug:Golang fmt.Println调试和跟踪工具，能够可视化函数调用路径。-Golang开发
- 投资组合：我的个人投资组合以及由React提供的Dot Net服务器
- streamy-server
- voices:p5.js小实验
- New Tab Wallpaper-crx插件
- xml-website:监控项目的网站
