Getting timeout exception on Count()

Question

Getting timeout exception on Count()

Stesvis 1,041

I am performing a query to the Customers table with some filters:

   var customers = Context.Customers  
                   .AsNoTracking()  
                   .Include(x => x.Client)  
                   .Include(x => x.CreatedByUser)  
                   .Include(x => x.TattleDevices)  
                   .Where(x =>  
                       x.ClientId == clientId  
                       && x.Status == Status.Active);

Then I want to get the count (for pagination labels):

   var totalCount = customers.Count();

And when the query returns a high number (3-4 thousands records), it often times out with this error (from the logs):

EXCEPTION TYPE: Microsoft.Data.SqlClient.SqlException
EXCEPTION MESSAGE: Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.CheckResetConnection(TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParserStateObject.ExecuteFlush() at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteTState,TResult
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable1 source, Boolean& found)
at lambda_method2338(Closure , QueryContext )
at System.Linq.Queryable.CountTSource
at LiveDispatchApi.Services.Customers.CustomersService.Filter(Int32 clientId, Int32& recordsTotal, Nullable1 takeFrom, Nullable1 takeCount, String searchValue, Nullable1 orderByIndex, String orderDirection) in R:\NorthStar\live-dispatch-api\LiveDispatchApi\Services\Customers\CustomersService.cs:line 41 at LiveDispatchApi.Controllers.v3._1.CustomersController.Filter(Int32 clientId, SearchRequest request) in R:\NorthStar\live-dispatch-api\LiveDispatchApi\Controllers\v3.1\CustomersController.cs:line 30 ClientConnectionId:68b91fd8-cd84-47b1-b6a6-e358b4df7639 Error Number:-2,State:0,Class:11 STACK TRACE: at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.CheckResetConnection(TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
at Microsoft.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext()
at System.Linq.Enumerable.TryGetSingleTSource
at lambda_method2338(Closure , QueryContext )
at System.Linq.Queryable.CountTSource
at LiveDispatchApi.Services.Customers.CustomersService.Filter(Int32 clientId, Int32& recordsTotal, Nullable1 takeFrom, Nullable1 takeCount, String searchValue, Nullable`1 orderByIndex, String orderDirection) in R:\NorthStar\live-dispatch-api\LiveDispatchApi\Services\Customers\CustomersService.cs:line 41
at LiveDispatchApi.Controllers.v3._1.CustomersController.Filter(Int32 clientId, SearchRequest request) in R:\NorthStar\live-dispatch-api\LiveDispatchApi\Controllers\v3.1\CustomersController.cs:line 30

INNER EXCEPTION: Microsoft.Data.SqlClient.SqlException Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Void OnError(Microsoft.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
Win32Exception The wait operation timed out.

IMPORTANT:

I call this code from an API using Postman. Most times it works, and when it does it's basically instant (~30ms) and when it fails it times out after many seconds (for the same records)
The same approach never times out in the old Entity Framework 6, I only noticed it in EF Core 6 because I am rebuilding it with the new .NET Core.

Any ideas?

I am using EF Core 6.0.9

Karen Payne MVP 35,586 Volunteer Moderator

Perhaps using Connection Resiliency, in the following code sample replace ConfigurationHelper.ConnectionString() with your connextion string

optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString(), options =>  
{  
    options.EnableRetryOnFailure(  
        maxRetryCount: 3,  
        maxRetryDelay: TimeSpan.FromSeconds(10),  
        errorNumbersToAdd: new List<int> { 4060 }); //additional error codes to treat as transient  
});

Viorel 122.6K Reputation points

2022-09-30T04:47:09.02+00:00

Is it possible to determine the corresponding SQL query and to ensure that Count is executed on SQL Server?

1 answer

Your answer

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-09-28T21:41:06.163+00:00

Perhaps using Connection Resiliency, in the following code sample replace ConfigurationHelper.ConnectionString() with your connextion string

optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString(), options => { options.EnableRetryOnFailure( maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: new List<int> { 4060 }); //additional error codes to treat as transient });
Viorel 122.6K Reputation points

2022-09-30T04:47:09.02+00:00

Is it possible to determine the corresponding SQL query and to ensure that Count is executed on SQL Server?

Answer 1

Bruce (SqlWork.com) 77,686 Volunteer Moderator

if the time varies a lot for the same client, then you are probably hitting locks, and are using different isolation levels between the two version.

if locks are an issue, switch to row versioning.

Stesvis 1,041 Reputation points

2022-09-29T15:04:00.683+00:00

No I was just testing this locally against my local DB.

Share via

Getting timeout exception on Count()

1 answer

Your answer