CODEWITHSUNDEEP
c#entity-framework
gosr
gosr

Reputation: 4708

EF SaveChangesAsync extremely slow

I have a table in which I want to update 100 rows at a time. I have a list of the 100 IDs that I use to find the specific rows. Once found, I update a single column (SyncOk) for each row.

The problem is that updating 100 rows takes around 23 to 30 seconds.

dbContext.Configuration.ValidateOnSaveEnabled = false;
var count = ids.Count;
for (var i = 0; i < count; i++)
{
    var id = ids[i];
    var record = await dbContext.History
        .FirstOrDefaultAsync(r => r.Id == id);
    record.SyncOk = syncOk;
}
await dbContext.SaveChangesAsync();

Some notes:

Why is SaveChangesAsync() so slow - how can I increase the speed of the above functionality? I'm afraid that the table is locked during the 23-30 seconds and will make other services (that use the same table) unable to update it as well.

Upvotes: 1

Views: 4634

Answers (3)

gosr
gosr

Reputation: 4708

I tried implementing the changes suggested by the two other answers - but with the same performance result (i.e., no change in speed).

I increased the performance greatly (and fixed my issue) by using a raw SQL command:

var stringOfIds = string.Join(",", ids);
await dbContext.Database.ExecuteSqlCommandAsync(
    $"UPDATE dbo.History SET SyncOk = 1 WHERE Id IN ({stringOfIds})");

Upvotes: 1

Michael Mairegger
Michael Mairegger

Reputation: 7301

You are executing a total of ids.Count SELECT statements to the database. This can be seen if you add the code:

dbContext.Database.Log += Console.WriteLine;

Try to minimize the access to the SQL-Instance by fetching all the data at once:

var records = await dbContext.History.Where(i => ids.Contains(i.Id)).ToListAsync();

Then you should perform the modification you need:

foreach(var record in records)
{
    record.SyncOk = syncOk;
}
await dbContext.SaveChangesAsync();

you could also use the ForEachAsync which will query the result like the above portion of code also only once:

await dbContext.History.Where(i => ids.Contains(i.Id))
                       .ForEachAsync(i => i.SyncOk = syncOk);
await dbContext.SaveChangesAsync();

Upvotes: 5

Eldho
Eldho

Reputation: 8273

IMHO Select * from History where Id in (YourList) is performed below.

var listOfRecordsToBeUpdated = await dbContext.History
        .Where(r => ids.Contains(r.Id)).ToListAsync();

//It will detect the changes each time when you update the entity
// Make sure you re-enable this after your bulk operation
DataContext.Configuration.AutoDetectChangesEnabled = false;

//Iterate through the records and assign your value
listOfRecordsToBeUpdated.Foreach(x=>x.SyncOk = syncOk);

DataContext.Configuration.AutoDetectChangesEnabled = true;

await conn.SaveChangesAsync();

Increase performance by disabling AutoDetectChangesEnabled

Upvotes: 1

Related Questions