在C＃/ .NET中执行批量更新的最快方法 [英] Fastest way of performing Bulk Update in C# / .NET

问题描述

我正在尝试通过SQL Server中的迷你控制台应用程序执行批量更新的最佳方法是什么。我已经编写了自己的批量更新方式，如下所示：

I'm trying to figure out whats the best possible way to perform a bulk update via my mini console application in SQL server. I have written my own way of bulk update like following:

  SqlCommand command = new SqlCommand();
  command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI");
  command.Connection.Open();

  for (int i = 0; i < items.Count; i = i + 1000)
  {
     var batchList = items.Skip(i).Take(1000).ToList();
     for (int j = 0; j < batchList.Count(); j++)
     {
       command.CommandText += string.Format("update Items set QuantitySold=@s_id{0} where ItemID = @id{0};", j);
       command.Parameters.AddWithValue("@s_id" + j, batchList[j].QuantitySold);
       command.Parameters.AddWithValue("@id" + j, batchList[j].ItemID);
      }
     command.ExecuteNonQuery();
     command = new SqlCommand();
     command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI");
     command.Connection.Open();
            }
     command.Connection.Close();

但是我对此的性能并不满意，更新了我的50000-100000条记录这样执行时，DB会变得非常缓慢，即使它以1000的批数来执行它们。...

But I'm not so happy with the performance of this one, updating 50000-100000 records in my DB gets quite slow when doing it like this, even tho it does them in batches of 1000....

是否有任何可以加速的库/解决方案事情？

Is there any library/solution out there that could "speed things up"?

有人可以帮我吗？

推荐答案

最快的方法是使用内置的 SqlBulkCopy 类将数据批量插入到临时表中，然后使用连接到该表进行更新

The fastest way would be to bulk insert the data into temporary table using the built in SqlBulkCopy Class, and then update using join to that table

或者您可以使用 SqlBulkTools 即可轻松完成此操作。

Or you can use a tool such as SqlBulkTools which does exactly this in an easy way.

var bulk = new BulkOperations();

using (TransactionScope trans = new TransactionScope())
{
    using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI")
    {
        bulk.Setup()
            .ForCollection(items)
            .WithTable("Items")
            .AddColumn(x => x.QuantitySold)
            .BulkUpdate()
            .MatchTargetOn(x => x.ItemID) 
            .Commit(conn);
    }

    trans.Complete();
}

这篇关于在C＃/ .NET中执行批量更新的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！