SqlBulkCopy.BulkCopyTimeout property

views:

302

answers:

SqlBulkCopy.BulkCopyTimeout property

Hello everyone,

I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net to develop a console application to do bulk insert copy.

I want to use both bulk insert batch and bulk insert timeout property. For the BulkCopyTimeout property, I am confused and want to know whether it applies for the whole bulk or applies to only each batch of the bulk?

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

thanks in advance, George

+1 A:

Searching and reading some blog postings and forum posts on the web appears to indicate the the SqlBulkCopyTimeout actually does apply not to the entire operation, but to each batch in the operation.

The MSDN docs isn't totally clear on this, but most posts seem to indicate that the timeout applies to the batch. Decreasing the batch size and/or increasing the SqlBulkCopyTimeout seems to be the solution to timeout problems in most cases.

See this forum post as an example.

Marc

marc_s 2009-09-27 10:29:05

I set the timeout to 120 seconds. My whole bulk operation will last for 1 hour and each bulk batch takes about 20 seconds. I never experienced timeout issue. In your comments, I should experience timeout because 1 hour > 120 seconds?

George2 2009-09-27 10:33:33

Thanks for your update, Marc! My further confusion is, bulk copy has two related phases for each batch -- (1) read from source and (2) write to desination. Do you think it means both (1) and (2) of each batch can not exceed BulkCopyTimeout? Or it means the sum of (1) and (2) of each batch can not exceed BulkCopyTimeout?

George2 2009-09-27 10:47:29

@George2: quite honestly: I don't know. The docs aren't very conclusive on this one, sorry.

marc_s 2009-09-27 13:04:59

Typically, though, if you cannot set the timeout for the read and write part separately, then the timeout applies to the whole operation (read+write)

marc_s 2009-09-27 13:09:05

Thanks Marc, I am also interested in the connection timeout. Does the connection timeout impacts after we begin bulk copy? I think connection timeout only matters when we open the connection to database at the first time, once we established the connection, the connection timeout is no impact to bulk copy (read + write).BTW: connection timeout I mean http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

George2 2009-09-27 13:15:54

Connection Timeout only applies to the time it takes to establish the connection to the SQL Server. Once connected, it's no longer relevant.

marc_s 2009-09-27 13:26:36

Thanks Marc, in ADO.Net, there is no idle connection timeout setting (idle I mean if no operation on the connection, the connection will timeout)?

George2 2009-09-27 16:50:17

@George2: no, once the connection is open, it remains open 'til you specifically close it.

marc_s 2009-09-27 17:09:44

Thanks Marc, question answered!

George2 2009-09-27 17:19:28

+1 A:

It applies to the entire operation for one batch. You have to make sure your connections timeout is set as well. I've hit the timeout before, set it on the bulk copy and found out the hard way that the connection time out matters also.

It seems that it's concept of entire operation is funny. If it ends up waiting for more than the timeout at any one time it will fail. In other words if the read of a batch takes more than the time out it will fail, but if the sum of all the reads is > timeout you're okay. If the write of a batch takes too long it will also fail.

But it seems to be batch by batch, not the whole thing.

Mike Two 2009-09-27 10:32:53

George2 2009-09-27 10:34:43

Mike, are there any official documents mention the meaning of BulkCopyTimeout applies to both each batch read and each batch write? Appreciate if you could provide a link. Thanks!

George2 2009-09-27 10:40:40

@George2 not that I know of. I'm basing this on empirical evidence learned through hitting the timeout. Sorry that my first pass at the answer was misleading.

Mike Two 2009-09-27 10:41:54

Thanks! I am also interested in your comments -- "You have to make sure your connections timeout is set as well.". Could you explain how connection timeout impacts bulk copy please? My confusion is, once we connect to source/destination database, connection timeout will never take any effect. Connection timeout only charges how long it allows us to establish the connection. Please correct me if I am wrong.

George2 2009-09-27 10:45:28

@George2 - you're right the connection time out behaves as you say. I was just remembering all the things I tried and not thinking about which ones actually helped.

Mike Two 2009-10-13 09:52:44

ansaurus

tags:

views:

answers:

SqlBulkCopy.BulkCopyTimeout property

related questions