你的位置:首页 > ASP.net教程

[ASP.net教程]SqlBulkCopy使用心得 (大量数据导入)


文章转载原地址:http://www.cnblogs.com/mobydick/archive/2011/08/28/2155983.html

最近做的项目由于之前的设计人员懒省事,不按照范式来,将一张表的扩展信息存到了一个“键-值”表中。如下图:



 

 

对于主表中的每一条信息,大约有60个“key”,也就是说主表中每插入1条记录,子表中必须要插入60条。

通过预估我们确定主表中最终的数据量大约是20万,也就是说,子表中会有20x60=1200万条记录。同样类型的“主-子”表我们一共有4对,且不说这些表的查询效率,单是每天一次的数据导入对于我们来说就是一项巨大的挑战。

Technorati 标签: SqlBulkCopy

在此我吐槽一下,本来一个十万级的 数据库,就是让这种垃圾“设计师”生生给搞成了个千万级的。而且最初他提出的数据插入方案是将每一条数据都生成一条Insert语句,然后逐条调用 ExecuteNoQuery执行,后果就是测试用的3000条主表记录,共生成3000x60x4=72万条数据,花费7小时执行完毕,性能30条/ 秒。后来他辞职了,换了个人,然后第二任也辞职了,第三任就是在下。

项目到我手里之后,在我的坚持下重新进行了软件结构设计,由于数据库系统已经被另一个子系统使用,所以没办法更改了,只好去寻找一种高效的插入方式。

最开始我使用多线程,开10个线程,使性能提升到300条/秒,测试用记录花费大约40分钟插入完毕,对于多60倍的正式数据来说,40小时执行完毕显然不能满足我们每天一次数据导入工作的要求。

通过Google大神,我找到了SqlBulkCopy。

经过测试,性能我很满意,4000条/秒,那就先用它吧,下一阶段的工作重点就是干掉“键-值”表。

在使用中,我也碰到了一些“莫名其妙”的问题,在此记下,以备查询。

  1.      1.SqlBulkCopy 可以将一个DataTable对象插入到指定名称的数据表中,但是,这个DataTable的架构必须和数据库表一样,尤其要注意一 点,DataTable中列的顺序必须和数据库表一样,而且不允许间隔。比如:数据库表中有A、B、C三列,其中B列有默认值,这时用于插入的 DataTable不能只有A、C两列,如果只有这两列,就会把DataTable里C列的数据插入数据库表B列里,而把DBNull插入C列里。如果是 C列有默认值,DataTable可以只有A、B两列。
  •       2.SqlBulkCopu的默认超时时间是30秒,30x4000=1.2万,无法满足我一次性插入单张表的需要,改成3600秒。