SQL Server的最佳插入/更新批处理

I'm making frequent inserts and updates in large batches from c# code and I need to do it as fast as possible, please help me find all ways to speed up this process.

我经常从c#代码中批量插入和更新,我需要尽快完成,请帮我找到加速这个过程的所有方法。

Build command text using StringBuilder, separate statements with ;

使用StringBuilder构建命令文本,使用单独的语句;

Don't use String.Format or StringBuilder.AppendFormat, it's slower then multiple StringBuilder.Append calls

不要使用String.Format或StringBuilder.AppendFormat,它比多个StringBuilder.Append调用慢

Reuse SqlCommand and SqlConnection

重用SqlCommand和SqlConnection

Don't use SqlParameters (limits batch size)

不要使用SqlParameters(限制批量)

Use insert into table values(..),values(..),values(..) syntax (1000 rows per statement)

使用insert到表值(..),值(..),值(..)语法(每个语句1000行)

Use as few indexes and constraints as possible

使用尽可能少的索引和约束

Use simple recovery model if possible

尽可能使用简单的恢复模型

Here are questions to help update the list above

以下是有助于更新上述列表的问题

What is optimal number of statements per command (per one ExecuteNonQuery() call)?

什么是每个命令的最佳语句数(每个ExecuteNonQuery()调用)?

Is it good to have inserts and updates in the same batch, or it is better to execute them separately?

在同一批次中进行插入和更新是否合适,或者最好单独执行它们?

My data is being received by tcp, so please don't suggest any Bulk Insert commands that involve reading data from file or external table.

我的数据正由tcp接收,因此请不要建议任何涉及从文件或外部表读取数据的批量插入命令。

Insert/Update statements rate is about 10/3.

插入/更新语句的速率约为10/3。

3 个解决方案

#1

Use table-valued parameters. They can scale really well when using large numbers of rows, and you can get performance that approaches BCP level. I blogged about a way of making that process pretty simple from the C# side here. Everything else you need to know is on the MSDN site here. You will get far better performance doing things this way rather than making little tweaks around normal SQL batches.

使用表值参数。当使用大量行时,它们可以很好地扩展,并且您可以获得接近BCP级别的性能。我在博客上写了一篇关于在C#方面使这个过程变得非常简单的方法。您需要知道的其他所有内容都在MSDN网站上。您可以通过这种方式获得更好的性能,而不是围绕正常的SQL批处理进行一些调整。

#2

As of SQLServer2008 TableParameters are the way to go. See this article (step four)

从SQLServer2008开始,TableParameters是最佳选择。看这篇文章(第四步)

http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

I combined this with parallelizing the insertion process. Think that helped also, but would have to check ;-)

我将此与并行插入过程相结合。认为这也有帮助,但必须检查;-)

#3

Use SqlBulkCopy into a temp table and then use the MERGE SQL command to merge the data.

将SqlBulkCopy用于临时表,然后使用MERGE SQL命令合并数据。

#1

#2