SqlBulkCopy的或批量插入 [英] SQLBulkCopy or Bulk Insert

问题描述

我有大约17 GB的数据的总和约为6500文件，这是第一次，我已经不得不搬到我所说的大量数据。该数据是网络驱动器上，但个别文件相对较小（最大7 MB）。

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).

我写在C＃程序，我在想，如果，如​​果我使用BULK INSERT，而不是使用SqlBulkCopy我会注意到性能显著差异。在服务器上的表还有一个额外的列，所以如果我使用BULK INSERT我将不得不使用格式文件，然后运行为每行一个UPDATE。

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.

推荐答案

通过测试，BULK INSERT为多更快。使用SqlBulkCopy的一个小时后，我也许通过我的数据的方式的四分之一，我已经写完了另一种方法（和吃午饭）。到时候我写完这篇文章（〜3分钟），BULK INSERT正要通过了三分之一。

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.

应当指出的是，这种情况的主要原因之一可能是该服务器是一个显著更强大的电脑，并认为这是不是算法的效率进行分析，但是我仍然会建议使用BULK INSERT，因为一般的服务器很可能比一般的台式电脑显著更快。

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.

这篇关于SqlBulkCopy的或批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！