懂的入-->关于如何提高BULK INSERT的执行效率问题

2010-10-10 10:18:16 1.3k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

C# BULK INSERT导入大数据文件数据到SqlServer 优先选择 SqlBulkCopy：适用于客户端数据源，无需处理文件权限。BULK INSERT 补充场景：适合服务器端已有标准化数据文件的定期导入。优化核心参数：合理设置批处理大小、锁机制及错误容忍度，结合业务需求选择编码与格式控制策略。阅读详情

用C#写了个收集网页信息的小程序，基本思路是以正则表达式批量抓取网页数据，存储为CSV文件，边抓取边循环用BULK INSERT批量导入SQL SERVER 2005中去。
印象中BULK INSERT效率是很高的即使百万数量级的记录也可以在30秒内全部导入完毕。但是这个程序的实际运行结果却让我大跌眼镜，根据SQL Server Profiler跟踪的结果，每次导入0至2000条记录情况下，BULK INSERT的执行效率约是400毫秒至20秒！粗略估计平均执行效率在4秒钟左右，仅极个别情况时间少于100毫秒。
已用HDTUNE查硬盘性能基本正常,磁盘测试平均值89M/S，突发数据传输率180M/S，后台除360安全卫士、ZONEALARM、MACFEE
外基本未开其他程序。数据库日志记录模式已改为“简单”。要插入的表只包含3个非聚集索引。断点调试程序，发现程序暂停时，有时也出现硬盘灯常亮15秒以上情况，任务管理器监控到是sqlservr.exe在作大量I/O写入操作，怀疑是后台写日志影响SQL脚本的正常执行。
这个问题已困扰我2个月之久，实在找不到原因，请大家一同分析探讨，如蒙指教，感激不尽。分数不多，全部敬上，请有缘人笑纳！

C#中的SQL脚本如下：

 

                SqlStr = "BULK INSERT "+TbName

                        +" FROM '"+Fname+"'"

                        +" WITH (FIELDTERMINATOR='\t',ROWTERMINATOR='\r\n')";

...全文

Rstan下载安装攻略准备把Rtools删掉，重新下载（胡乱猜测是安装顺序的问题），但是卸载时显示Rtools被占用，但我已经把该关的都关了。这一问题的出现，直接让我卸载掉了Rstudio，又重新安装了一个，不过这次安装在另一个磁盘，因为原来的内存不多了。或者参考知乎，使用清华镜像网址进行下载：（知乎链接）https://zhuanlan.zhihu.com/p/346947595（清华下载链接）https://mirrors.tuna.tsinghua.edu.cn/CRAN/bin/windows/Rtools/ 阅读详情

keykeyk

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

geliph 2011-01-06

打赏
举报

可能是sql server安装配置的不合理，或者就是硬件不行。三个索引应该影响不大，除非索引里有很多column?

作为参考，我用bcp每天导出2千万条交易记录用时不超过3分钟，bulk insert导入不超过7分钟。用的是一般的char模式. （sql server 2005 64 bit)

keykeyk 2010-11-12

打赏
举报

还是没人能帮我解决问题吗？

keykeyk 2010-11-09

打赏
举报

难道真的就没有办法了吗？

keykeyk 2010-11-02

打赏
举报

to claro：谢谢答复。你说的帖子看过了，不过跟我说的情况不一样。我的电脑是家用的，不是服务器，普通AMD双核CPU，希捷硬盘，4G内存，WINXP，桌面编程自用的。当数据表记录数在百万数量级以下时，用BULK INSERT插入十万数量级的数据很快，无索引时也就几秒种（非聚集索引情况下印象中也不算太慢，具体没详细测算过）。问题是当记录数达到千万乃至亿数量级时，如果有（非聚集）索引，BULK INSERT插入数十条记录的效率变得非常慢，硬盘灯狂闪，而删除索引后情况明显好转。请多指教！

claro 2010-11-01

打赏
举报

这位兄台发的帖子也是存在MSSQL的疑惑。

http://topic.csdn.net/u/20100817/16/8daf36b2-cff7-4d87-89b4-2b44a362fb7a.html
事实真的是它不行，也不一定。

上面是举个例子，再看看能否找到问题的关键点。

claro 2010-11-01

打赏
举报

只能叹息MS SQL SERVER在大数据量时性能太次
虽然在某些方面我也不屑MSSQL，但是大数据量的表现跟很多因素有关。

能透露一下，硬件配置吗？（特别是CPU、硬盘和内存如果有Serv型号更好）
软件配置呢？tempdb是在独立的SAS上还是SCSI上呢（这里忽略raid）？

keykeyk 2010-10-31

打赏
举报

前几日工作上实在忙不过来，无暇再顾上优化BULK INSERT效率的问题。那一阵子终于忙过了，重新审视此问题，又有了点新的进展。昨天把三个索引重新建立后，再运行程序，果然硬盘灯常亮的问题再次出现。对比之下，现在已经基本能确认，导致BULK INSERT插入数十条记录时间长达几秒的原因就是因为存在索引！
我的数据表现有记录1亿1千万条左右，共计10个字段。记录数比较多，但是从字段数上看，结构其实很简单。目前我的解决方案是，跑程序提取数据前，先删除所有这三个非聚集索引，等需要分析或做报表时再新建三个索引（新建索引耗时很长很长……）除了尴尬和无奈，只能叹息MS SQL SERVER在大数据量时性能太次。
不知大家还有什么好的建议没？

billpu 2010-10-16

打赏
举报

之所以建议你删除索引再试,是因为我以前碰到过相同的实例和你碰到的差不多,也是索引对某些情况下大容量记录插入的影响实在太大

billpu 2010-10-16

打赏
举报

[Quote=引用 17 楼 keykeyk 的回复:]

把3个索引都删除了，结果BULK INSERT的速度提高到70－500毫秒之间，基本确定最大的影响因素应该是索引。不过由于前期大数据量提取已完毕，目前每轮提取的数据量都在100以下，也不能完全插入数据量的影响。
实质上这涉及索引对巨型数据表插入数据的影响问题。该数据表已经有将近1亿记录，还在不断增长中。至今仍然百思不得其解，照理说，这3个都不是聚集索引，对插入新数据的影响应该不……
[/Quote]
我觉得说sqlserver似乎有失公允,网上前一阵不是有google的工程师嘲笑过现在大部分流行的dbms的索引技术太烂我相信也包括oracle和sqlserver的吧
任何技术都有他的局限性,比如sqlserver的易用性,开发成本包括软件成本上可能更亲民一点,oracle也有他更擅长的方向比如稳定性等(这里不把这些问题铺开了)
另外你提到的聚集和非聚集的问题,非聚集虽然对存储数据顺序没要求,但是编纂数据的索引字典和也需要占用相当系统资源,关键在于你的数据量比较大,可以的话试试2008吧,可能稳定性会更让你满意一些.

keykeyk 2010-10-16

打赏
举报

把3个索引都删除了，结果BULK INSERT的速度提高到70－500毫秒之间，基本确定最大的影响因素应该是索引。不过由于前期大数据量提取已完毕，目前每轮提取的数据量都在100以下，也不能完全插入数据量的影响。
实质上这涉及索引对巨型数据表插入数据的影响问题。该数据表已经有将近1亿记录，还在不断增长中。至今仍然百思不得其解，照理说，这3个都不是聚集索引，对插入新数据的影响应该不大。但是忽略插入几十条数据与上千条记录的差别（个人感觉这属于同一数量级，基本可以忽略不计）之后，删除索引对BULK INSERT效率的提高居然有将近10倍！真是太不可思议了。一直听一个ORACLE大牛说微软数据库很烂，尤其是对索引的使用和维护上简直可以说是一塌糊涂，现在终于有了切身感受。
考虑到这个巨型数据表是用来做分析、报表、统计的，更新时删除索引，分析时再反复添加，这种用法也太囧了点，真是无奈啊！最近连续加班，之后1周估计也会很忙，基本没有时间研究这些问题，烦请大家继续讨论，谢谢大家！

claro 2010-10-16

打赏
举报

关注。

SQLCenter 2010-10-13

打赏
举报

[Quote=引用 14 楼 sqlcenter 的回复:]

to keykeyk:

嗯，你说这些我也略懂，Desktop Database，暂时不考虑这个问题了。

我看了一下你的语句，好像没有DATAFILETYPE选项，加上试试（如果csv是unicode编码用'widechar'）：

with (DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='\r\n')
[/Quote]

应该无关，缺省DATAFILETYPE让BULK自己判断，顶多就是启动导入动作慢一丁点，对后续速度无影响。

billpu 2010-10-12

打赏
举报

索引你先测试一下看看呵呵有结果拿上来大家学习一下

SQLCenter 2010-10-12

打赏
举报

to keykeyk:

嗯，你说这些我也略懂，Desktop Database，暂时不考虑这个问题了。

我看了一下你的语句，好像没有DATAFILETYPE选项，加上试试（如果csv是unicode编码用'widechar'）：

with (DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='\r\n')

keykeyk 2010-10-12

打赏
举报

to SQLCenter:上面说的只是我个人的理解，不一定正确。因为根据我对ACCESS编程的粗浅理解，ACCESS可以把TXT文本直接当成数据库进行操作，而那些TXT的默认格式是有严格限制的，例如第1行必须是标题行且字段名必须严格匹配，分隔符必须是“,”等等，实际上这些TXT文件本质上就是遵循了标准的CSV格式。如果要再灵活些，就必须配上schema.ini文件，具体可以查MSDN的资料。所以我才感觉你贴的材料所讲的BULK操作不支持CSV文件，其含义与我们所讨论的BULK INSERT命令可能是两回事。

SQLCenter 2010-10-12

打赏
举报

to keykeyk:

你解释之后我还是不能理解那段话是什么意思，BULK INSERT不仅可以导入文本，二进制文件都可以，为什么单单拿CSV文件出来说事呢，很费解啊。

keykeyk 2010-10-12

打赏
举报

继续试验和探讨：
to SQLCenter：MSDN上对bulk-import（大容量导入）的字面表述确实很让人费解。仔细阅读以后，个人感觉这是个误解，它指的是不支持直接从标准CSV文件导入数据，而不是说决对不支持CSV文件。理由有2个：
1、材料中说只能从OLE DB Provider for Jet（即ACCESS数据库引擎）导入CSV文件，该操作其实指的是直接把一个CSV或TXT文件作为数据库导入的ACCESS命令，与我们所讲的BULK操作是两回事；
2、BULK INSERT在MSDN的说明里显然是专门针对特定格式的文本文件，而CSV文件只是固定格式文本文件中比较简单的一种，所以说只要满足相关条件，BULK INSERT命令根本不管你是什么后缀名，是否CSV后缀根本没有区别。

根据楼上几位朋友的建议，我在sql server management studio中试验了一把。因为目标数据库太庞大，插入测试数据不好维护，专门建了一个数据结构与目标数据库一样的临时表，用同样的命令BULK INSERT一定量的数据，结果用时40－150ms之间，感觉还是偏慢，但是没有那么夸张。两个数据库结构相同，只有数据量不一样，初步把矛盾焦点定位到索引等方面，这两天加班多，没时间深入，等抽空再继续。

billpu 2010-10-11

打赏
举报

[Quote=引用 3 楼 keykeyk 的回复:]

谢谢2楼的热心帮助！
1、删除索引再导入——每次只插入数十至上千的数据，又没设置聚集索引，感觉索引对插入效率的影响不应该这么大。
2、关掉防火墙——从进程监控中未发现这些程序有多少I/O读写，这个影响也不应很大，以后我会试一下。
3、直接执行SQL语句——每次插入的记录数是不一样的，为有可比性，得再写个例程作验证，暂时没时间。不过我用的是SqlConnection、SqlCommand对象……
[/Quote]
之所以让你直接在sqlserver环境下试一下,是为了排除sqlserver的问题,如果速度快了就可以把注意力放到连接上或者c的程序,如果速度仍然没有提高,那就可以在服务器端找一下问题了
另外注册表键值我看了看是agent服务的应该不针对你那个问题抱歉

yanguohua2008 2010-10-11

打赏
举报

直接拿sql server management studio 工具中的导入导出试试看是不是很久要不是估计在客户端这边了

SQLCenter 2010-10-10

打赏
举报

http://msdn.microsoft.com/en-us/library/ms187042%28v=SQL.100%29.aspx

Restrictions

SQL Server bulk-import operations do not support importing data from comma-separated value (CSV) files. However, On 32-bit systems, it is possible to import CSV data into a SQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet.

-----------

这是MSDN上说的限制，我是不太理解：BULK INSERT和OPENROWSET(BULK)确实可以导入csv文件，但MSDN说不支持（bcp肯定不支持）。

不过MSDN既然有这么一说，我觉得不妨一试，用txt。

加载更多回复（7）

懂的入-->关于如何提高BULK INSERT的执行效率问题

相关推荐