Bulk Insert or SQLBulkCopy

bulkinsert c# performance sql sqlbulkcopy

Question

This is the first time I've had to relocate what I would consider a significant quantity of material; I have roughly 6500 files totaling about 17 GB. The data is on a network disk, however each file is just a modest amount (max 7 MB).

I was wondering whether using BULK INSERT instead of SQLBulkCopy would significantly improve the speed of a C# software I'm working on. The server table also contains an additional field, so if I use BULK INSERT, I'll need to utilize a format file and then execute a UPDATE for every entry.

Since I'm new to forums, if there is another method to pose my question, please let me know.

1
13
2/16/2011 9:59:27 PM

Accepted Answer

BULK INSERT is tested to be zzz-5 zzz quicker. I spent an hour using SQLBulkCopy, and by the time I had done creating the alternative approach, I had probably gone through around 25% of my data (and having lunch). BULK INSERT had completed roughly a third of its writing by the time I finished this article, which took around three minutes.

It's also important to note that the upload is quicker without a main key for anybody using this as a reference.

It should be noted that this is not an analysis of the effectiveness of the algorithm and that one of the major causes of this could be that the server was a significantly more powerful computer. Nevertheless, I would still advise using BULK INSERT because the average server is probably a lot faster than the average desktop computer.

8
8/10/2012 1:18:53 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow