H3C4
Help to Resolve Timeout Issue While Updating 20,000 Records Using SQL Server and Entity Framework
Okay, I'll provide more details shortly. In the meantime, here is the exception I'm encountering: Execution Timeout Expired. The timeout period elapsed before the operation could complete, or the server is not responding.
Also, I'm batching the updates in groups of 100 records.
My query looks something like this:
List<string> updates = new();
// START LOOP
{
// Some logic before....
var updateQuery = @$"
UPDATE dbo.SomeDatabase
SET
A = '{variable.A}',
B = '{variable.B}',
C = '{variable.C}',
D = '{variable.D}',
E = '{variable.E}',
F = '{variable.F}',
G = {(variable.G != null ? $"'{variable.G}'" : "NULL")},
H = {(variable.H != null ? $"'{variable.H}'" : "NULL")},
I = {(variable.I != null ? $"'{variable.I}'" : "NULL")},
J = {(variable.J != null ? $"'{variable.J}'" : "NULL")},
K = {(variable.K != null ? $"'{variable.K}'" : "NULL")}
WHERE Id = {variable.Id}";
updates.Add(updateQuery);
}
// END LOOP
int batchSize = 100;
var totalRecords = updates.Count();
for(int i = 0; i < totalRecords; i += batchSize)
{
await _context.Database.ExecuteSqlRawAsync(
string.Join(";", updates.Skip(i).Take(batchSize)));
Console.WriteLine($"Finished block {i}");
}
But It hangs up every time during the batch 500 : (
11 replies