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

keykeyk 2010-10-10 10:18:16
用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')";
...全文
903 点赞 收藏 27
写回复
27 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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。
回复
加载更多回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-10 10:18
社区公告
暂无公告