SqlBulkCopy 的 Timeout 和 BatchSize

在测试中发现 SqlBulkCopy 在数据量大的时候会出现超时报错。

http://stackoverflow.com/questions/4535536/timeout-expired-with-sqlbulkcopy

Timeout expired with SqlBulkCopy

...

There are two ways to fix this error:

1) Increase Timeout by default it is 30 second

   sqlBulkCopy.BulkCopyTimeout = {time in seconds}

2) Decrease BatchSize by default it try to insert all rows in one batch

    sqlBulkCopy.BatchSize  = {no of rows you want to insert at once}

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout.aspx

说得很清楚。BulkCopyTimeout 的缺省值为 30 秒,这样处理数据量大了容易发生超时。碰巧 BatchSize 的缺省值为 0,意思是会把所有操作塞到一轮中处理,这样更加剧了一轮处理所耗费的时间长度。解决办法就是修改上述两个量中的一个即可。比如 batch 尺寸变小了,每轮的时间也就缩短了。

那么每轮处理多少量合适呢?这涉及到性能问题:

http://stackoverflow.com/questions/779690/what-is-the-recommended-batch-size-for-sqlbulkcopy

What is the recommended batch size for SqlBulkCopy?

...

Given this scenario, I found a batch size of 5,000 to be the best compromise of speed and memory consumption. I started with 500 and experimented with larger. I found 5000 to be 2.5x faster, on average, than 500. Inserting the 6 million rows takes about 30 seconds with a batch size of 5,000 and about 80 seconds with batch size of 500.

...

I found that 500 was the optimal value for my application. The 500 value for SqlBulkCopy.BatchSize is also recommended here.


转载于:https://my.oschina.net/dpxietao/blog/413114

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值