我应该使用SqlBulkCopy还是Stored Procedure来导入数据 [英] Should I use SqlBulkCopy or Stored Procedure to import data

问题描述

我有一个日志文件是248 MB，它可以扩展到一个GB。所以你可以想象有多少行可以。我需要导入所有的行到SQL Server数据库中的表。为此，我首先创建一个DataTable并将日志文件中的所有行添加到该DataTable中作为新行。这发生得很快。在大约30秒内，超过一百万条记录被添加到表中。在表填充了行之后，然后使用存储过程将DataTable中的记录导入到数据库。但是这个阶段执行得非常严重。现在我不知道，如果我应该使用SqlBulkCopy WriteToServer方法或者我应该保持这种方式？如果SqlBulkCopy是一个更好的选择，那么我应该使用DataTable或IDataReader版本。提前感谢。

I've got a log file which is 248 MB and it can extend up to a GB. So you can imagine how many rows there can be.I need to import all the rows into a table in an SQL Server database. For that I first create a DataTable and add all the lines in the log file into that DataTable as new rows. This happens pretty fast. More than a million records get added to the table in about 30 seconds. After the table is filled with the lines I then import the records in the DataTable to the database using stored procedure. However this phase executes very heavily. Now I wonder, if I should use SqlBulkCopy WriteToServer method instead or should I keep with this way? If SqlBulkCopy is a better choise then should I use DataTable or IDataReader version. Thanks in advance.

推荐答案

我会使用SqlBulkCopy进行任何真实卷的数据导入。与SqlDataAdapter的性能差异可能很大。例如作为效果比较，我发布用于导入10万行：

I would go with SqlBulkCopy for data imports of any real volume like this. The performance difference vs. SqlDataAdapter can be large. e.g. I blogged as performance comparison for importing 100K rows:

SqlBulkCopy：1.5885s

SqlDataAdapter：25.0729s

SqlBulkCopy: 1.5885s
SqlDataAdapter: 25.0729s

如果你使用TableLock选项和SqlBulkCopy，你可以得到更大的吞吐量，在我的测试中，导入然后下降到0.8229s。

You can get even greater throughput if you use a TableLock option with SqlBulkCopy, which in my test took the import then down to 0.8229s.

这也值得注意的是， SqlBulkCopy，您可以有多个实例将一段数据批量加载到并行的同一目标表中，而无需彼此竞争。对另一个外部链接的歉意，但我认为它是相关的。这是关于加载到堆表，没有索引，以获得最佳性能，这可能不是一个选择您当前的情况，但绝对值得知道。

It's also worth noting that with SqlBulkCopy, you can have multiple instances bulk loading a segment of data into the same destination table in parallel without them contending with each other. Apologies for another external link, but I think it's relevant. That's about loading into a heap table, no indexes, for optimal performance, which may not be an option for your current scenario but is definitely worth knowing of.

这篇关于我应该使用SqlBulkCopy还是Stored Procedure来导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！