15

I have about 6500 files for a sum of about 17 GB of data, and this is the first time that I've had to move what I would call a large amount of data. The data is on a network drive, but the individual files are relatively small (max 7 MB).

I'm writing a program in C#, and I was wondering if I would notice a significant difference in performance if I used BULK INSERT instead of SQLBulkCopy. The table on the server also has an extra column, so if I use BULK INSERT I'll have to use a format file and then run an UPDATE for each row.

I'm new to forums, so if there was a better way to ask this question feel free to mention that as well.

SeanVDH
  • 866
  • 1
  • 10
  • 28

1 Answers1

9

By test, BULK INSERT is much faster. After an hour using SQLBulkCopy, I was maybe a quarter of the way through my data, and I had finished writing the alternative method (and having lunch). By the time I finished writing this post (~3 minutes), BULK INSERT was about a third of the way through.

For anyone who is looking at this as a reference, it is also worth mentioning that the upload is faster without a primary key.

It should be noted that one of the major causes for this could be that the server was a significantly more powerful computer, and that this is not an analysis of the efficiency of the algorithm, however I would still recommend using BULK INSERT, as the average server is probably significantly faster than the average desktop computer.

SeanVDH
  • 866
  • 1
  • 10
  • 28
  • 3
    So this doesn't say anything useful about the *intrinsic* differences of both methods. Also it lacks information on the implementation details of both methods. All in all, not too useful IMO. – Gert Arnold Nov 01 '19 at 12:48
  • 1
    Experimentation is the foundation of science. With the resources I had available, including time constraints, I shared my results on a public forum. I would argue that this answer provides data on concrete results for a clearly described scenario, and people in a similar use case might find it useful. – SeanVDH Jun 05 '21 at 22:22