Fastest way of performing Bulk Update in C# / .NET

Question

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();

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"?

Can someone help me out ?

Answer 1

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

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();
}

Answer 2

You can use Kros.KORM for bulk operation.

using (var database = new Database("connectionstring ...", "ado client name ..."))
{
    database
       .Query<Movie>()
       .AsDbSet()
       .BulkUpdate(_data);
}

Or if you do not need to use ORM and have the source data reader available, you can use the SqlServerBulkInsert / SqlServerBulkUpdate or MsAccessBulkInsert / MsAccessBulkUpdate classes to perform bulk operations.

For example:

using (var bulkInsert = new SqlServerBulkInsert("connection string"))
{
    bulkInsert.Insert(reader);
}

You can see comparison with pure ADO.NET commands https://github.com/Kros-sk/Kros.Libs/wiki

Answer 3

I don't know what items is in your code so I don't know how to get the items into a table valued parameter. I can help with that if you need it but I would need to know what that object is.

Regardless you could do something like this on the sql side. Then you simply execute this procedure with your items collection as inbound parameter.

create type Items as TABLE
(
    ItemID int
    , Quantity int
)

GO

create procedure UpdateItemsBulk
(
    @Items Items READONLY
) as

    set nocount on;

    Update i
    set QuantitySold = items.Quantity
    from items i
    join @Items items on items.ItemID = i.ItemID

GO