Timeout on execution procedure

ming-tsai commented 1 year ago

Here is what to include in your request to make sure we implement a solution as quickly as possible.

1. Description

Hi, Hope you are doing well, I just trying to using the ExecuteProcedureAsync but it launch the timeout exception in 1 minutes, however my dbcontext is configured in 30 minutes.

            services.AddDbContextPool<MyDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("MyDb"),
                sqlOptions => sqlOptions.CommandTimeout(60 * 30))
                .AddInterceptors(new AppInterceptor())
            );

2. Exception

If you are seeing an exception, include the full exception details (message and stack trace).

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): Unknown error 258    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)    at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)    at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)    at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) --- End of stack trace from previous location ---    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)    at EntityFrameworkExtras.EFCore.DatabaseExtensions.ExecuteStoredProcedureAsync(DatabaseFacade database, Object storedProcedure, CancellationToken cancellationToken)    at

3. Fiddle or Project

I couldn't add my project because is organization project, but I'm running the procedure using the BackgroundService and calling the method as below

        public static Task ExecuteStoredProcedureAsync(this DatabaseFacade database, object storedProcedure)
        {
            Check.NotNull(database, nameof(database));
            Check.NotNull(storedProcedure, nameof(storedProcedure));
            return Task.Run(() => database.ExecuteStoredProcedure(storedProcedure));
        }

We recently make the upgrade of the framework from 3.1 to 6, so we have this extension to make sure is asyncronous, I know that the package has this method ExecuteStoredProcedureAsync in the new version.

4. Any further technical details

The procedure will take kind of 10~15 minutes dependes the data, and the package version I used is.

    <PackageReference Include="EntityFrameworkExtras.EFCore" Version="6.0.7" />

JonathanMagnan commented 1 year ago

Hello @ming-tsai ,

Do you think you could create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue.

We did some testing and didn't find any issues. The timeout we specified was always taken by either our library or EF Core when we directly used their method. For example: https://github.com/zzzprojects/EntityFrameworkExtras/blob/master/EntityFrameworkExtras.Shared/DatabaseExtensions.EFCore3x.cs#L109

If you can reproduce it, we will be happy to look at it.

In your stored procedure, you can simply use a WAITFOR DELAY '00:02:00'; to reproduce a stored procedure that take a lot of time to get executed.

Best Regards,

Jon

Sponsorship Help us improve this library

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function

ming-tsai commented 1 year ago

Yes, I have tested it, and actually, it works, but I don't know why in my project launch that exception, Let me make some changes, and I will let you know.

JonathanMagnan commented 1 year ago

Sure, we will re-open this issue as soon as you can provide a project that reproduce it.

zzzprojects / EntityFrameworkExtras