The fastest way to UPDATE multiple rows in C# ADO .NET

Question

I'm trying to improve this query perfromance. I have to update multiple rows in once and this take too long for me. For example 4k rows for update takes 15 seconds.

My code:

using (var db = new DbConnection())
{
    int counter = 0;

    foreach (var item in itemsToUpdate)
    {
        if (!db.IsTransactionOpen)
        { 
            db.OpenTransaction();
        }

        DbQuery q = db.CreateQuery();

        q += "UPDATE Product SET Name = @Name WHERE Id = @Id";
        q.AddParameter("@Name", item.Name);
        q.AddParameter("@Id", item.Id);

        db.ExecuteQuery();
        counter++;

        if (counter > 100)
        {
            db.CommintTransaction();
        }
    }

    if (!db.IsTransactionOpen)
    { 
        db.CommintTransaction();
    }
}

I'm not satisfied with that update time. There is any way to speed up this? I used simplified update query in example, I tested without transaction and it takes much more time.

Answer 1

Easiest way is to send the data in JSON and join it in a single SQL statement, eg with a nvarchar(max) parameter set to

[{"id":1,"name":"a"},{"id":2,"name":"b"},{"id":4,"name":"c"}]

run

with newValues as
(
  select * from openjson(@json) with (id int, name nvarchar(200))
),
q as
(
  select p.Name, p.Id, n.name NewName
  from Product p
  join  newValues n
    on p.id = n.id
)
update q set Name = NewName

Answer 2

To improve the performance of your SQL update operation, you can consider using a batch update approach rather than updating each row individually. Here's how you can modify your code to achieve this:

using (var db = new DbConnection())
{
    
    db.OpenTransaction();

    foreach (var item in itemsToUpdate)
    {
        DbQuery q = db.CreateQuery();

        q += "UPDATE Product SET Name = @Name WHERE Id = @Id";
        q.AddParameter("@Name", item.Name);
        q.AddParameter("@Id", item.Id);
        
        db.AddQueryToBatch(q);

        counter++;

        if (counter % 1000 == 0)
        {
            // Execute the batch and clear it every 1000 queries
            db.ExecuteBatch();
        }
    }
    db.ExecuteBatch();
    db.CommitTransaction();
}

Answer 3

You should use a Table Valued Parameter.

First create a Table Type

CREATE TYPE dbo.IdString (Id bigint PRIMARY KEY, Value varchar(500));

Then you can do

var table = new DataTable { Columns = {
    { "Id", typeof(long) },
    { "Value", typeof(string) },
} };

foreach (var item in itemsToUpdate)
    table.Add(item.Id, item.Name);

using var db = new DbConnection();
const string query = @"
UPDATE p
SET Name = i.Value
FROM Product p
JOIN @items i ON i.Id = p.Id;
";

DbQuery q = db.CreateQuery();
q += query;

p = q.AddParameter("@items", SqlDbType.Structured);
p.Value = table;
p.TypeName = "dbo.IdString";

db.ExecuteQuery();