Help to Resolve Timeout Issue While Updating 20,000 Records Using SQL Server and Entity Framework
Hi everyone,
I'm working on a project where I need to update around 20,000 records in SQL Server using the ExecuteSqlRawAsync method in Entity Framework. However, I keep encountering a timeout error, even when I increase the command timeout duration.
I've already tried adjusting the timeout value, but it doesn't seem to resolve the issue. Here's what I've done so far:
Increased the command timeout using the CommandTimeout property.
Tried batching the updates, but still facing issues.
Has anyone faced a similar issue with ExecuteSqlRawAsync or with large-scale updates in SQL Server? If so, what solutions or optimizations did you implement to fix the problem?
Any advice or suggestions would be greatly appreciated!
Thanks in advance!
7 Replies
are you sure table is not being locked by someone else?
use
ExecuteUpdate(Async)
otherwise it's pretty impossible to help without the actual SQL error (if it even is a timeout) and without the query you're running.How can I check that?
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 : (
for sql server i don't really know but it should be relatively easy to find out with a search, it should just be a query
also is this db used much or just by you
it seems really weird to have timeout with just 20000 records, i could understand maybe 1 million records
or maybe you have wrong timeout settings on the connector
I setted 15 minutes for the timeout.
that code is something
use execute update
Ok! Let me try using ExecuteUpdate