Executing SQL store procedure by EF Core 2.2 gets a timeout while executing it successfully in SQL Server Management

Question

I've got a strange behaviour. When I trying to execute SQL store procedure by EF Core 2.2. I get a timeout (30 seconds). But when I execute the same procedure within SQL Server Management Studio it runs successfully - it runs it about 1 or 2 sec.

UPDATE: This problem occured around 2 month ago. Before that the service was able to run this proc.

Here is a DbContext that I use:

public class MyContext : DbContext
{
    public MyContext(string connectionString)
        : base(new DbContextOptionsBuilder().UseSqlServer(connectionString).Options)
    {
    }

    // some DbSets omitted here ...

    public async Task<string> ProcExecuteAsync(string login, string xml)
    {
        // get some data from db (omitted)

        var xmlParameter = new SqlParameter("@XML", SqlDbType.Xml) { Value = xml };
        var xmlOutputParameter = new SqlParameter("@XMLoutput", SqlDbType.Xml) { Direction = ParameterDirection.Output };

        await Database.ExecuteSqlCommandAsync(
            "MyProc_set @idUser=@idUser,@XML=@XML,@XMLoutput=@XMLoutput OUT",
            new SqlParameter[] { userIdParameter, xmlParameter, xmlOutputParameter });

        return (string)xmlOutputParameter.Value;
    }
}

Then I have a handler (I use Mediator library):

public class AddToClientCommandHandler : IRequestHandler<AddToClientCommand, AddToClientResponse>
{
    private readonly IContextBuilder _contextBuilder; 

    public AddToClientCommandHandler(IContextBuilder contextBuilder)
    {
        _contextBuilder = contextBuilder;
    }

    public async Task<AddToClientResponse> Handle(AddToClientCommand command, CancellationToken cancellationToken)
    {
        using (var context = _contextBuilder.CreateMyContext())
        {
            // getting some data from db (omitted)

            // generate xmlRequest (omitted)

            var result = await context.ProcExecuteAsync(command.Login, xmlRequest);
            return new AddToClientResponse(result);
        }
    }
}

Here is a ContextBuilder:

public class ContextBuilder : IContextBuilder
{
    private readonly IOptions<ConnectionStrings> _connectionStrings;

    public ContextBuilder(IOptions<ConnectionStrings> connectionStrings)
    {
        _connectionStrings = connectionStrings;
    }

    public MyContext CreateMyContext()
    {
        var result = new MyContext(_connectionStrings.Value.Entities);
        return result;
    }
}

And I register it like this:

public static class ApplicationContextBuilderExtension
{
    public static void AddApplicationContextBuilder(this IServiceCollection services)
    {
        services.AddSingleton<IContextBuilder, ContextBuilder>();
    }
}

Is it possible that the problem comes from that I use AddSingleton when I register ContextBuilder?

Can someone point me up to how and where investigate the problem?

Your handler is calling `CreateLMContext` but your builder has `CreateMyContext`. Is this just a typo/mistake in the example code? — Crowcoder, Feb 05 '20 at 10:59
Does this answer your question? [Query times out when executed from web, but super-fast when executed from SSMS](https://stackoverflow.com/questions/2248112/query-times-out-when-executed-from-web-but-super-fast-when-executed-from-ssms) — Ian Kemp, Feb 05 '20 at 11:17
Can you check (from the exception stack trace or EF Core logging) if the exception is triggered by the db connection opening, or by the db command execution? — Ivan Stoev, Feb 05 '20 at 11:36
@Ivan, looks like exception is triggered by the db command execution: "ClassName": "System.Data.SqlClient.SqlException", "Message": "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.", — Dmitry Stepanov, Feb 05 '20 at 12:40
@Ivan, is there any sense to add interceptor that would add "SET ARITHABORT ON" to the executing code as it's proposed [here](https://stackoverflow.com/questions/2248112/query-times-out-when-executed-from-web-but-super-fast-when-executed-from-ssms)? Or this is not a solution. — Dmitry Stepanov, Feb 05 '20 at 13:37
Well, IMHO before entering the (hard) interception path, try adding that manually to your `ExecuteSqlCommandAsync` command text and see if it has the desired positive effect. — Ivan Stoev, Feb 05 '20 at 13:53
Restarting SQL on the server helped. The question can be closed. — Dmitry Stepanov, Feb 06 '20 at 09:09

score 1 · Answer 1 · answered Feb 05 '20 at 10:51

Is it possible that the problem comes from that I use AddSingleton when I register ContextBuilder?

No, as the one method in ContextBuilder is not doing db operations and is no returning the same context all the time.

But when I execute the same procedure within SQL Server Management Studio it runs successfully

And how long does it take? To my knowledge SSMS does not impose and timeout limit, which would tell you why you do not get a timeout. EfCore is limited to 30 seconds UNLESS you CHANGE IT - which is trivial as per documentation, but it must be done.

The concept here being that SSMS is way more used for bulk operations than EfCore where a 30 second operation can be considered an error - and in those rare cases you want/need a longer timeout, reconfigure the dbcontext.

Right, EF Core has a 30 sec timeout. SSMS runs this proc just in 1 or 2 sec. So, the difference is too much. First time, this problem occured around 2 month ago. Before that the service was able to run this proc. — Dmitry Stepanov, Feb 05 '20 at 11:02
Well, if you have additional info you may consider next time to spend more time formulating your question. — TomTom, Feb 05 '20 at 11:21

score 0 · Answer 2 · answered Feb 05 '20 at 10:52

I have got the same issue. I went through the following link:

Stored procedure slow when called from web, fast from Management Studio

I have modified sp like this:

ALTER PROCEDURE [dbo].[sproc] @param1 int, AS SELECT * FROM Table WHERE ID = @param1

to

ALTER PROCEDURE [dbo].[sproc] @param1 int, AS DECLARE @param1a int SET @param1a = @param1 SELECT * FROM Table WHERE ID = @param1a

For me, it is working fine.

Executing SQL store procedure by EF Core 2.2 gets a timeout while executing it successfully in SQL Server Management

2 Answers2