2.1w+
社区成员
懂的入-->关于如何提高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脚本如下：
印象中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')";
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
geliph 2011-01-06
可能是sql server安装配置的不合理，或者就是硬件不行。三个索引应该影响不大，除非索引里有很多column?
作为参考，我用bcp每天导出2千万条交易记录用时不超过3分钟，bulk insert导入不超过7分钟。用的是一般的char模式. （sql server 2005 64 bit)
作为参考，我用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
事实真的是它不行，也不一定。
上面是举个例子，再看看能否找到问题的关键点。
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）？
虽然在某些方面我也不屑MSSQL，但是大数据量的表现跟很多因素有关。
能透露一下，硬件配置吗？（特别是CPU、硬盘和内存如果有Serv型号更好）
软件配置呢？tempdb是在独立的SAS上还是SCSI上呢（这里忽略raid）？
keykeyk 2010-10-31
前几日工作上实在忙不过来，无暇再顾上优化BULK INSERT效率的问题。那一阵子终于忙过了，重新审视此问题，又有了点新的进展。昨天把三个索引重新建立后，再运行程序，果然硬盘灯常亮的问题再次出现。对比之下，现在已经基本能确认，导致BULK INSERT插入数十条记录时间长达几秒的原因就是因为存在索引！
我的数据表现有记录1亿1千万条左右，共计10个字段。记录数比较多，但是从字段数上看，结构其实很简单。目前我的解决方案是，跑程序提取数据前，先删除所有这三个非聚集索引，等需要分析或做报表时再新建三个索引（新建索引耗时很长很长……）除了尴尬和无奈，只能叹息MS SQL SERVER在大数据量时性能太次。
不知大家还有什么好的建议没？
我的数据表现有记录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吧,可能稳定性会更让你满意一些.
把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周估计也会很忙，基本没有时间研究这些问题，烦请大家继续讨论，谢谢大家！
实质上这涉及索引对巨型数据表插入数据的影响问题。该数据表已经有将近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自己判断，顶多就是启动导入动作慢一丁点，对后续速度无影响。
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')
嗯，你说这些我也略懂，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文件出来说事呢，很费解啊。
你解释之后我还是不能理解那段话是什么意思，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之间，感觉还是偏慢，但是没有那么夸张。两个数据库结构相同，只有数据量不一样，初步把矛盾焦点定位到索引等方面，这两天加班多，没时间深入，等抽空再继续。
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服务的 应该不针对你那个问题 抱歉
谢谢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。
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。
加载更多回复
PL/SQL ;--> ;动态SQL 很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只能...
深入浅出Mysql - 优化篇(Sql语句) 定期优化表 如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则...mysql> analyze table fa_order; +------------------------+---------+-------
Hadoop期末考试选择题整合--助力期末绩点提高！ HDFS默认的当前工作目录是/user/$USER, fs.default.name的值需要在哪个配置文件内说明？ A、mapred-site.xml B、hdfs_site.xml C、core-site.xml ...关于服务启动正确的是（） A、sbin/hadoop-dae..
Elasticsearch入门、Kibana 索引管理（elasticserch-head 插件使用， Kibanan 安装和使用） Solr 在传统的搜索应用中表现好于 Elasticsearch，但在处理实时搜索应用时效率明显低于 Elasticsearch 4、ElasticSearch架构图以及基本概念(术语) 1、es概述 Elasticsearch是面向文档(document oriented)的，这意味...
Flink批量写入HBase效率问题 Flink批量写入HBase效率问题 标题 HBase批量写入单次提交数据量大小问题 DataStream<;List<;Put>> sourceList = dataStream.timeWindowAll(Time.seconds(2)).apply(new OrderToHBaseFunctionOutPutFormat...
ABAP-1-会计凭证批量数据导入本地ACCESS 公司会计凭证导入ACCESS数据库，需要发送给审计，原先的方案是采用DEPHI开发的功能（调用函数获取会计凭证信息，然后INSERT到ACCESS数据表），运行速度非常慢，业务方要求对该功能进行优化，需要对原先方案进行整体...
shell实例手册[强烈推荐]2021-4-10 shell实例手册 0说明{ 手册制作: 雪松 更新日期: 2013-12-06 欢迎系统运维加入Q群: 198173206 ... 请使用"notepad++"打开此文档,"alt+0"将函数折叠后方便查阅 请勿删除信息，转载请说明出处，抵制不道德行为。...
PHP_Nosql数据库-mongodb 可以通过索引的重建,减少索引文件碎片,并提高索引的效率. 类似mysql中的optimize table mysql里面使用optimize table语法：optimize table 表名 语法：db.集合名.reIndex() 8、索引使用注意事项 （1）创建索引的...
Expert_PL_SQL_Practices-----Do Not Use 1、Row-by-Row Processing 1 DECLARE 2 CURSOR C1 IS 3 SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold 4 FROM Sales 5 WHERE Amount_Sold > 100; 6 C1_Rec C1%ROWT...
PL/SQL --> 动态SQL -- PL/SQL --> 动态SQL --==================== 使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行 SQL查询语句，对于这种...
Django-wed开发框架-练习题 批量创建数据使用for循环,创建数据的时候尽量最后执行创建语句，使用django的语法 bulk_create product_list_to_insert = list() for x in range(10 ): product_list_to_insert.append(Product(name = ' ...
mysql 参数优化压力测试-锁优化-主从优化 1 参数优化结果 [mysqld] basedir=/data/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock log-error=/var/log/mysql.log log_bin=/data/binlog/mysql-bin binlog_format=row skip-name-resolve ...gt...
2018-5-28-MySql配置文件解析 layout title date author desc in_head post Mysql配置文件解析 2018-05-28 10:00:02 +0800 南丞 Mysql高可用配置文件解析 ...style> .article-content p{ text-indent: 2em; line-h...
java中使用mongo-java-driver.jar操作mongo 有时候，我们并不需要一条数据中所有的内容，只是需要一部分而已，mongoDB 提供了一个projection方法，解决了这个问题。 collection. find ().projection(fields(include( "x" , "y" ), excludeId())) 使用forEach ...
Models-查询详细操作 # 单表简单查询13种方法 1.all(): 查询所有结果 all： models.... book_all=models.Book.objects.all() # 结果是querySet集合 [model对象，....] ...print(book_all) # <;...Book: Book object>, <;B...
大数据开发面试题总结-超详细 客户端上传请求--->namenode检查，返回响应--->客户端真正的文件上传请求，包括文件名，文件大小--->namenode返回上传节点--->客户端准备上传，进行块的逻辑切分--->客户端构建pipline流--->...
