bcp / BULK INSERT与表值参数的性能 [英] Performance of bcp/BULK INSERT vs. Table-Valued Parameters

问题描述

我将不得不使用SQL Server的 BULK INSERT 命令重写一些相当旧的代码，因为架构已更改，并且我想到也许我应该考虑关于使用TVP切换到存储过程的信息，但是我想知道它可能会对性能产生什么影响。

I'm about to have to rewrite some rather old code using SQL Server's BULK INSERT command because the schema has changed, and it occurred to me that maybe I should think about switching to a stored procedure with a TVP instead, but I'm wondering what effect it might have on performance.

一些背景信息可能有助于解释我为什么问这个问题：

Some background information that might help explain why I'm asking this question:

• 数据实际上是通过Web服务输入的。 Web服务将文本文件写入数据库服务器上的共享文件夹，该服务器随后执行批量插入。此过程最初是在SQL Server 2000上实现的，当时除了将几百个 INSERT 语句插入服务器之外，实际上没有其他选择，而这实际上是原始过程。

• The data actually comes in via a web service. The web service writes a text file to a shared folder on the database server which in turn performs a BULK INSERT. This process was originally implemented on SQL Server 2000, and at the time there was really no alternative other than chucking a few hundred INSERT statements at the server, which actually was the original process and was a performance disaster.

数据被批量插入到永久登台表中，然后合并到一个更大的表中（此后，该数据将从临时表）。

The data is bulk inserted into a permanent staging table and then merged into a much larger table (after which it is deleted from the staging table).

要插入的数据量为大，但不是巨大-通常为几百行，可能是5-10k行在极少数情况下排名最高。因此，我的直觉是 BULK INSERT 是未记录的操作不会使那有很大的不同（但我当然不是

The amount of data to insert is "large", but not "huge" - usually a few hundred rows, maybe 5-10k rows tops in rare instances. Therefore my gut feeling is that BULK INSERT being a non-logged operation won't make that big a difference (but of course I'm not sure, hence the question).

插入实际上是更大的流水线批处理过程的一部分，需要连续多次进行；因此性能至关重要。

The insertion is actually part of a much larger pipelined batch process and needs to happen many times in succession; therefore performance is critical.

我想替换<$ c的原因使用TVP的$ c>大容量插入是：

• 通过NetBIOS编写文本文件可能是

• Writing the text file over NetBIOS is probably already costing some time, and it's pretty gruesome from an architectural perspective.

我相信登台表可以（并且应该）消除。造成这种情况的主要原因是，插入的数据需要在插入的同时用于其他两次更新，而尝试从大量生产表进行更新要比使用几乎为空的登台要昂贵得多表。使用TVP，参数基本上是 登台表，我可以在主插入之前/之后对它进行任何操作。

I believe that the staging table can (and should) be eliminated. The main reason it's there is that the inserted data needs to be used for a couple of other updates at the same time of insertion, and it's far costlier to attempt the update from the massive production table than it is to use an almost-empty staging table. With a TVP, the parameter basically is the staging table, I can do anything I want with it before/after the main insert.

我几乎可以消除重复检查，清理代码以及与批量插入相关的所有开销。

I could pretty much do away with dupe-checking, cleanup code, and all of the overhead associated with bulk inserts.

无需担心锁定如果服务器一次获得了一些这样的事务，则在登台表或tempdb上发生争用（我们尝试避免这种情况，但是确实发生了）。

No need to worry about lock contention on the staging table or tempdb if the server gets a few of these transactions at once (we try to avoid it, but it happens).

我显然要在将任何东西投入生产之前对此进行概要分析，但是我认为在我度过所有时间之前先问一问，看看是否有人发出严厉警告可能是个好主意。关于为此目的使用TVP。

I'm obviously going to profile this before putting anything into production, but I thought it might be a good idea to ask around first before I spend all that time, see if anybody has any stern warnings to issue about using TVPs for this purpose.

所以-对于那些足够熟悉SQL Server 2008尝试过或至少对此进行过调查的人，您的结论是什么？对于插入（例如几百到几千行）的情况，TVP会切芥菜吗？

So - for anyone who's cozy enough with SQL Server 2008 to have tried or at least investigated this, what's the verdict? For inserts of, let's say, a few hundred to a few thousand rows, happening on a fairly frequent basis, do TVPs cut the mustard? Is there a significant difference in performance compared to bulk inserts?

（又称测试结果）

感觉就像一个36阶段的部署过程。两种解决方案都经过了广泛的测试：

The end result is now in production after what feels like a 36-stage deployment process. Both solutions were extensively tested:

• 提取出共享文件夹代码并使用 SqlBulkCopy 直接上课；


• 使用TVP切换到存储过程。

• Ripping out the shared-folder code and using the SqlBulkCopy class directly;
• Switching to a Stored Procedure with TVPs.

可以了解确切经过了什么测试，以消除对该数据可靠性的任何疑问，这是该导入过程实际执行功能的更详细说明：

Just so readers can get an idea of what exactly was tested, to allay any doubts as to the reliability of this data, here is a more detailed explanation of what this import process actually does:

1. 从时间数据序列开始，该时间序列通常约为20-50个数据点（尽管有时可能会增加几百个） ）;

1. Start with a temporal data sequence that is ordinarily about 20-50 data points (although it can sometimes be up a few hundred);

对其进行大量疯狂的处理，而这些处理大多与数据库无关。此过程是并行的，因此（1）中大约8-10个序列被同时处理。每个并行过程会生成3个附加序列。

Do a whole bunch of crazy processing on it that's mostly independent of the database. This process is parallelized, so about 8-10 of the sequences in (1) are being processed at the same time. Each parallel process generates 3 additional sequences.

将所有3个序列和原始序列取为一个批处理。

Take all 3 sequences and the original sequence and combine them into a batch.

将所有现在完成的8-10个处理任务的批处理合并为一个大的超级批处理。

Combine the batches from all 8-10 now-finished processing tasks into one big super-batch.

使用导入批量插入策略（请参阅下一步）或TVP策略（跳至步骤8）。

Import it using either the BULK INSERT strategy (see next step), or TVP strategy (skip to step 8).

使用 SqlBulkCopy 类将整个超级批处理转储到4个永久临时表中。

Use the SqlBulkCopy class to dump the entire super-batch into 4 permanent staging tables.

运行一个存储过程，该存储过程是（a）在两个表上执行一系列聚合步骤，包括几个 JOIN 条件，然后（b）执行 MERGE 。 （完成）

Run a Stored Procedure that (a) performs a bunch of aggregation steps on 2 of the tables, including several JOIN conditions, and then (b) performs a MERGE on 6 production tables using both the aggregated and non-aggregated data. (Finished)

OR

生成4 DataTable 对象包含要合并的数据；其中3个包含CLR类型，不幸的是ADO.NET TVP没有正确支持它们，因此必须将它们作为字符串表示形式使用，这会有点影响性能。

Generate 4 DataTable objects containing the data to be merged; 3 of them contain CLR types which unfortunately aren't properly supported by ADO.NET TVPs, so they have to be shoved in as string representations, which hurts performance a bit.

将TVP馈送到存储过程，该过程基本上与（7）相同，但直接与接收到的表进行处理。 （完成）

Feed the TVPs to a Stored Procedure, which does essentially the same processing as (7), but directly with the received tables. (Finished)

结果相当接近，但TVP方法最终平均表现更好，甚至当数据少量超过1000行时。

请注意，此导入过程连续运行了数千次，因此很容易只需计算完成所有合并所需的时间（是，小时）即可获得平均时间。

Note that this import process is run many thousands of times in succession, so it was very easy to get an average time simply by counting how many hours (yes, hours) it took to finish all of the merges.

最初，平均合并几乎要花8秒才能完成完成（在正常负载下）。消除NetBIOS合并并切换到 SqlBulkCopy 可以将时间减少到几乎恰好7秒。切换到TVP进一步将时间缩短为每批 5.2秒。对于运行时间以小时为单位的流程来说，这将使吞吐量提高 35％-一点也不差。与 SqlBulkCopy 相比，它还有〜25％的改善。

Originally, an average merge took almost exactly 8 seconds to complete (under normal load). Removing the NetBIOS kludge and switching to SqlBulkCopy reduced the time to almost exactly 7 seconds. Switching to TVPs further reduced the time to 5.2 seconds per batch. That's a 35% improvement in throughput for a process whose running time is measured in hours - so not bad at all. It's also a ~25% improvement over SqlBulkCopy.

我实际上相当有信心，真正的改善要多得多比这个。在测试过程中，很明显，最终的合并不再是关键的路径。相反，进行所有数据处理的Web服务开始受到输入请求数量的限制。CPU和数据库I / O都没有真正达到极限，并且没有明显的锁定活动。在某些情况下，我们发现连续合并之间存在几秒钟的空闲间隔。使用 SqlBulkCopy 时，差距很小，但要小得多（半秒左右）。但是我想这将成为另一天的故事。

I am actually fairly confident that the true improvement was significantly more than this. During testing it became apparent that the final merge was no longer the critical path; instead, the Web Service that was doing all of the data processing was starting to buckle under the number of requests coming in. Neither the CPU nor the database I/O were really maxed out, and there was no significant locking activity. In some cases we were seeing a gap of a few idle seconds between successive merges. There was a slight gap, but much smaller (half a second or so) when using SqlBulkCopy. But I suppose that will become a tale for another day.

结论：表值参数的性能确实比 BULK INSERT 操作，适用于在中型数据集上运行的复杂导入+转换过程。

Conclusion: Table-Valued Parameters really do perform better than BULK INSERT operations for complex import+transform processes operating on mid-sized data sets.

我想补充一点，只是为了缓解对部分赞成登台的人的担忧。从某种意义上说，整个服务是一个巨大的升级过程。该过程的每一步都经过了严格的审核，因此我们不需要临时表来确定为什么某些特定合并失败的原因（尽管实际上几乎从未发生过）。我们要做的就是在服务中设置一个调试标志，它将中断调试器或将其数据转储到文件而不是数据库中。

I'd like to add one other point, just to assuage any apprehension on part of the folks who are pro-staging-tables. In a way, this entire service is one giant staging process. Every step of the process is heavily audited, so we don't need a staging table to determine why some particular merge failed (although in practice it almost never happens). All we have to do is set a debug flag in the service and it will break to the debugger or dump its data to a file instead of the database.

，我们已经对流程有足够的洞察力，不需要临时表的安全性；我们首先拥有登台表的唯一原因是避免在所有 INSERT 和 UPDATE 语句上崩溃否则我们将不得不使用。在原始过程中，暂存数据无论如何仅停留在暂存表中几分之一秒，因此它在维护/可维护性方面没有任何价值。

In other words, we already have more than enough insight into the process and don't need the safety of a staging table; the only reason we had the staging table in the first place was to avoid thrashing on all of the INSERT and UPDATE statements that we would have had to use otherwise. In the original process, the staging data only lived in the staging table for fractions of a second anyway, so it added no value in maintenance/maintainability terms.

还请注意，我们已未用TVP替换了每个 BULK INSERT 操作。进行一些处理大量数据和/或无需对数据进行特殊处理的操作（除了将其扔到数据库之外）仍然使用 SqlBulkCopy 。 我并不是说TVP是性能的灵丹妙药，只是在这种特定情况下，它们在 SqlBulkCopy 上取得了成功，它涉及了初始阶段和最终合并之间的几种转换。 / strong>

Also note that we have not replaced every single BULK INSERT operation with TVPs. Several operations that deal with larger amounts of data and/or don't need to do anything special with the data other than throw it at the DB still use SqlBulkCopy. I am not suggesting that TVPs are a performance panacea, only that they succeeded over SqlBulkCopy in this specific instance involving several transforms between the initial staging and the final merge.

所以您拥有了它。 Point会去TToni寻找最相关的链接，但是我也很感谢其他回复。再次感谢！

So there you have it. Point goes to TToni for finding the most relevant link, but I appreciate the other responses as well. Thanks again!

推荐答案

我还没有使用TVP的经验，但是与BULK有一个不错的性能比较表在MSDN中插入此处。

I don't really have experience with TVP yet, however there is an nice performance comparison chart vs. BULK INSERT in MSDN here.

他们说BULK INSERT的启动成本较高，但此后速度更快。在远程客户端方案中，他们在大约1000行处绘制线（用于简单服务器逻辑）。从他们的描述来看，我想您应该使用TVP。性能上的损失-如果有的话-可以忽略不计，并且体系结构上的好处似乎非常好。

They say that BULK INSERT has higher startup cost, but is faster thereafter. In a remote client scenario they draw the line at around 1000 rows (for "simple" server logic). Judging from their description I would say you should be fine with using TVP's. The performance hit - if any - is probably negligible and the architectural benefits seem very good.

编辑：顺便说一句，您可以避免服务器本地文件并仍然使用通过使用SqlBulkCopy对象进行批量复制。只需填充一个DataTable，并将其输入到SqlBulkCopy实例的 WriteToServer -Method中即可。易于使用，而且非常快。

On a side note you can avoid the server-local file and still use bulk copy by using the SqlBulkCopy object. Just populate a DataTable, and feed it into the "WriteToServer"-Method of an SqlBulkCopy instance. Easy to use, and very fast.

这篇关于bcp / BULK INSERT与表值参数的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！