EFcore TimesOut when executing long time taking queries

[kishoretvk]

Describe what is not working as expected. Ef core when executing stored procedure, that takes more than few millions of records.

times out abruptly. i use EF core as async. the result might be from search of 3 record from a million to few 100. If you are seeing an exception, include the full exceptions details (message and stack trace). execption : ef core timeout.

Exception message:
Stack trace:

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

1. stored procedure which joins tables with more than few millions of records.
2. select 5 records from the entire joined tables.
3. return the result set .
4. EF core is async

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

Console.WriteLine("Hello World!");

Further technical details

EF Core version: (found in project.csproj or packages.config) Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Operating system: IDE: (e.g. Visual Studio 2015) visual studio 2015 update 3

[Wayne-Mather]

I had a similiar issue but resolved it as follows:

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
{
      Database.SetCommandTimeout(9000);
}

See if that at least solves the timeout problem for yourself

[ajcvickers]

Looks like @Wayne-Mather has answered the question. @kishoretvk If you are still having issues, then please provide more complete details on how we can reproduce the issue.

[kishoretvk]

thanks for the input, it helped in maintaining the connection for more than standard 30 seconds , I'm dealing with data more than 90 million records, when ever i increase the timeout, ef core makes multiple calls to sp after 30 seconds, which leads to dead lock , until it reached timeout , eventually throwing timeout exception,

[ErikEJ]

Investigate missing indexes in your database

[kishoretvk]

no that does not help inside my sp, im using 6 tables which has 5 of them more than 500k records and one with more than 10 million records, i have to join all of them. when ever sp runs with more than 30 secods time , efcore keeps calling it again and again

[ajcvickers]

@kishoretvk Presumably this is still happening when a suitably large connection timeout (in the connection string) as well as a suitably large command timeout (as shown above)? Are you using SQL Azure?

[kishoretvk]

no im not using sql azure but rather sql server on a windows server. i gave command timeout as

above mentioned.

[kishoretvk]

the issue is not resolved, and i would like to open a new one

[Wayne-Mather]

@kishoretvk Are you able to provide some code about your joins? I join several levels deep and while I don't have the record count you do, the code provided got around my timeout issues.

I would also look at getting the SQL that EF has generated and putting that into SSMS and see the performance. I suspect the issue may not be EF but actually the underlying schema and indexes.

Also, have you looked at running the missing indexes management view to see if you are missing indexes that can help the execution plan?

[kishoretvk]

Guys i m asking question from EFCore not sql ! secondly if u want to reporuce, write one sp with delay of 2 mintes and call from ef core u will see, make it async !!

[smitpatel]

@kishoretvk - Are you able to run the sp correctly if you just use ADO connection instead of using EF Core? Please post stack trace.

[Wayne-Mather]

@kishoretvk we are not denying ef core throws an exception when the timeout occurs. what we are trying to understand is where the problem lies.

the issue will probably be in one or more of the areas below:

• bad database schema design
• bad entity design
• bad sql generation
• bad execution plan
• missing indexes
• non-optimized stored procedure
• using linked servers

with all this we currently think the problem is not in ef but in the sql server stack somewhere and trying to help you find the problem so a solution can be provided. regardless of what the cause of the problem truly is.

[kishoretvk]

thanks for all the support but its not with sql server, please find more details

here i also posted stack trace and , found one more guy who tried to similar issue where he found he was not able to setcommandttimeout https://github.com/aspnet/EntityFrameworkCore/issues/9596

[kishoretvk]

i'm not using linked servers, i do not have caching problem as it works with Full Ef other problems are also not an issue, because they were refined to give better performance for sometime. lastly please see the stack trace and logs form ef core which clearly show. commandtimeout itsef does not change and async method goes to next. by thrrowin exception after set timetout. every 30 seconds, async method would call sp multiple times.

if timeout is set to 300, sp would be called `0 times and after tat timeout exception would be comming.

[kishoretvk]

@smitpatel thanks for reopening the issue i've posted the stack trace here but even if i do try ado dotnet connection, command timeout would not change.

there is an other issue which was opened for the same . i also liked it below,

please keep one of the thread , may be we can use the other one as it also has stack trace

and close this https://github.com/aspnet/EntityFrameworkCore/issues/9596

[smitpatel]

@kishoretvk - Let's ignore changing command timeout for now. Can you verify, if you use just ADO.NET SqlConnection without using EF Core in any form, are you able to run the query successfully? Or does it timeout?

[kishoretvk]

will try that too and let u know, by the way method throwing exception is , below i narrowed to as to show more details on that

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlCommand.<>c.b__107_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.d__26.MoveNext()

[kishoretvk]

will try ado dotnet and also try to do this with out async and see, and post u results if i get same exception

[kishoretvk]

@smitpatel i tried doing them sync calls same rrr repeated. will update with other one too ! with sync we get this for delays of 1 minute too.

try adding this in stored proc WAITFOR DELAY '00:00:50';

and run the call from Ecore, if i add it, timeout exception else data comes normal

[kishoretvk]

@smitpatel yes , all options used,

same error comes no matter efcore , async or non async or ado dotnet quries. error: timed out before server responds.

[kishoretvk]

@smitpatel please let me know any other alternative, may be can i use loading all data in memory with out sp ?

here is the sp i used to reproduce the issue:

Create PROCEDURE [dbo].[DateTest](
   @Status_ID INT
) AS

SET NOCOUNT ON
BEGIN 

    WAITFOR DELAY '00:00:50'
        WAITFOR DELAY '00:00:50';;
 select   CURRENT_TIMESTAMP as a ;

C# code :

   public void getData(string con)
        {

            try
            {
                SqlConnection conn = new SqlConnection(con); ;

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int i = 0;

                conn.Open();

                    object[] transactionGridDataParams =
                    {
                        new SqlParameter("@UserRoleOrganizationId",
                            (object) i ?? i )
                    };
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                  conn.Open();
                    // _context.Database.OpenConnection();
                SqlDataReader rdr = null;
                    cmd.CommandTimeout = 300;
                cmd.CommandText = "dbo.DateTest";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Status_ID", SqlDbType.BigInt) {Value = 1});
                List<T> resultList;

                var reader = cmd.ExecuteReader();

                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);

            }

        }

note: i' this code wor4ks fine with out time delay but with time delay it give error. i've connect timeout in connection string and also tried command timeout , too

[ErikEJ]

@kishoretvk What is the error?

[kishoretvk]

@ErikEJ Efcore timesout if the sql query takes moer than 2 minutes, and it keeps repeating calls to sql query for every 30 seconds, if u try to increase timeout . I please try the above code , u will reproduce the error. for further details, i 've also liked two other issues with stack trace too in my above posts.

or u can have a look here below too https://github.com/aspnet/EntityFrameworkCore/issues/9596

[smitpatel]

@kishoretvk - Your code does not give me any error when run on my machine.

[kishoretvk]

smitpatel c please make the delay as WAITFOR DELAY '00:02:50' WAITFOR DELAY '00:02:50'

[kishoretvk]

Im using EFCore 1.1.2 , secondly visual studio 2015 update 3, aspnet core.mvc core 1.1.2 im using sql server 2012 ,

[smitpatel]

I used following code trying to reproduce the issue. Stored Procedure:

Create PROCEDURE [dbo].[DateTest](
   @Status_ID INT
) AS

SET NOCOUNT ON;
WAITFOR DELAY '00:00:50';
WAITFOR DELAY '00:00:50';
select CURRENT_TIMESTAMP
GO

Program (Sync version)

using System;
using System.Data;
using System.Data.SqlClient;

namespace WaitTime
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0");

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();

                    cmd.CommandTimeout = 300;
                    cmd.CommandText = "dbo.DateTest";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Status_ID", SqlDbType.BigInt) { Value = 1 });
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.GetValue(0));
                    }

                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);

            }

            Console.WriteLine("Hello World!");
        }
    }
}

Output from code

PM> dotnet run
8/28/2017 10:35:04 AM
Hello World!

Program (Async version)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace WaitTime
{
    class Program
    {
        static void Main(string[] args)
        {
            Test().Wait();

            Console.WriteLine("Hello World!");
        }

        public static async Task Test()
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0");

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    await conn.OpenAsync();

                    cmd.CommandTimeout = 300;
                    cmd.CommandText = "dbo.DateTest";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Status_ID", SqlDbType.BigInt) { Value = 1 });
                    var reader = await cmd.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                        Console.WriteLine(reader.GetValue(0));
                    }

                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);

            }
        }
    }
}

Output from code

PM> dotnet run
8/28/2017 10:50:48 AM
Hello World!

Environment info: .NET Framework 4.5.2 & .NET Core App 1.1 using System.Data.SqlClient package 4.3.1 which EF Core 1.1 used. .NET Framework 4.6.1 & .NET Core App 2.0 using System.Data.SqlClient package 4.4.0 which are latest released packages (also used by EF Core 2.0).

I tested code with different wait time in stored procedure (as mentioned in previous post '00:02:50'). Everytime if the CommandTimeout is set lower than the wait time in sproc it times out. And once the timeout is increased more than wait time it passes successfully.

[smitpatel]

For me, the CommandTimeout is working properly (and as expected in all cases). Since it is failing for you in all the scenarios, it is likely there is some configuration on your SqlServer instance which is terminating the query ignoring the value of CommandTimeout. Please refer to SQL Server documentation on configuring your SqlServer.

EF Core uses underlying ADO.NET drivers to execute queries. If you are executing a query through EF Core then it will be passed to SqlClient for execution. If something does not work on ADO.NET level then it will not work with EF Core. It is possible that your query is failing due to different version of SqlServer than mine or using TCP/IP over localdb. Given that it fails with ADO.NET connection for you, file an issue on https://github.com/dotnet/corefx That team owns SqlClient and they will be able to assist you more. If it works in SqlClient, it will work in EF Core too.

Further observations,

• For certain async methods SqlCommand.CommandTimeout property is ignored according to docs here
• EF Core makes repeated calls due to retrying execution strategy. Every 30 secs, you would get SqlException due to timeout. Since it could be intermittent error (especially in Sql Azure), EF Core retries the same command on receiveing the exception. Therefore you are seeing multiple invocations

Closing this issue as there is nothing actionable on EF Side here. Please follow up with https://github.com/dotnet/corefx team to investigate if there is issue in SqlClient in certain conditions.

[kishoretvk]

Thanks for the time and suggestion will follow up with them and see,

[kishoretvk]

once we move to ef core 2.0 query executes till 100 seconds and after that, it goes to a loop of retry, even if the sp execution is done it will not return back. however if sp take mroe than 200 seconds, then ur call is wasted. no result comes back.

[anber500]

In our case, EF times out when trying to apply an index.

I read this documentation:

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-5.0&viewFallbackFrom=netcore-2.0#System_Data_SqlClient_SqlCommand_CommandTimeout

It seems the "Database.SetCommandTimeout" is pretty useless.

Our DBContext is shared among multiple APIs and having to add "SetCommandTimeout" to every call we make to the DB to prevent timeouts is a massive task. Why do these gotchas always arrive too late in a project?

Why can't we just do?:

public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { Database.SetCommandTimeout(90); }

It seems so simple :(

[ErikEJ]

With the latest SqlClient, you can do it from the connection string: https://erikej.github.io/sqlclient/2020/10/26/sqlclient-commandtimeout-preview.html

[roji]

Another option regardless of SqlClient version, is to set the command timeout when configuring your DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(@"<connection_string>", o => o.CommandTimeout(90));
}

[ErikEJ]

@roji Would that also apply to Migrations - I assume that is "when trying to apply an index" means?

[roji]

I think it definitely should - if not that would probably be a bug...

[anber500]

We are using postgres and was trying to add a new index via a migration.

We were able to extend the "CommandTimeout" from the connection string but setting the "CommandTimeout" in the DBContext doesn't seem to do anything.