Why am I getting query timeout despite setting a large timeout value in appsettings

Question

I have a data provider class. Below is simplified version of it.

namespace EmployeeManager
{
    public class EmployeeDataProvider : IEmployeeDataProvider
    {
        private DataObjects.EmployeeServiceContext databaseContext;

        IDbContextTransaction transaction;

        public EmployeeDataProvider(DataObjects.EmployeeServiceContext context)
        {
            this.databaseContext = context;
        }

        public void CreateEmployee(Id employeeId, string description)
        {
            transaction = databaseContext.Database.BeginTransaction();

            var employeeDto = databaseContext.Employee.Where(x => x.EmployeeId == employeeId).FirstOrDefault();
            if (employeeDto == null)
            {
                databaseContext.Employee.Add(new DataObjects.Employee() { EmployeeId = employeeId, Description = description });
            }
        }
        public void AddOrUpdateEmployeeData(EmployeeData data)
        {
            // Add or updates multiple Employee related tables

        }

        public void CommitChanges()
        {
            if (databaseContext.ChangeTracker.HasChanges())
                databaseContext.SaveChanges();
            transaction.Commit();
            transaction.Dispose();
        }
        public void RollbackChanges()
        {
            if (transaction != null)
            {
                transaction.Rollback();
                transaction.Dispose();
            }
        }
    }
}

The related service that consumes this provider could be called by multiple clients. Its setup as follows in the Startup.cs.

 public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            .....
        }

        public IConfigurationRoot Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            ......
            services.AddTransient(typeof(IEmployeeDataProvider), typeof(EmployeeDataProvider));


        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            app.UseStaticFiles();
            app.UseDeveloperExceptionPage();
            app.UseMvc();
        }
    }

Under different load conditions, I see lots of following in my log files. Note that none of stack traces are pointing to any method from my class.

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory|An exception occurred in the database while iterating the results of a query.
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.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

My connection string in appsettings.json is as follows.

"AppConnectionString": "Server=MyPC;User ID=myuser;Password=mypassword;Database=myDb;Connection Timeout=2000"

The error happens within a minute, so its not like query is waiting for 2000 seconds for query to timeout. Do I need to setup the timeout somewhere in code as well?

can you show your actual code I would personally set the time out in the Command Object that of which you are declaring inside of the method or function.. — MethodMan, Apr 27 '17 at 19:42
actual code is quite big, i will try to trim it out but in my scenario I didn't set any command timeout anywhere. Is it something that's setup via dbcontext.Database.SetCommandTimout()? Plus multiple request are hitting these methods, so same method could be called by well over 500 clients simultaneously — jim crown, Apr 27 '17 at 19:55
since you should be setting new Command and it's timeout this would correct your issue..but I would need to see your code better yet check this link out it would be a better option / solution http://stackoverflow.com/questions/12788972/set-database-timeout-in-entity-framework and you can set it to 60*2000 for example in the link example I have just shown — MethodMan, Apr 27 '17 at 19:56
Don't fight timeouts by increasing them. Make your code scalable instead. — Gert Arnold, Apr 27 '17 at 22:24
Connection timeout is not a command/query timeout. Figure out how to make your query faster (or even avoid executing it if possible) — Pawel, Apr 27 '17 at 23:15
@GertArnold I agree..but in the case of network issues increasing would help we had the same issue and had to increase timeout until our network team finally stepped up from a 10meg to a 100meg was like instant magic.. but I do agree with you about scalability — MethodMan, Apr 28 '17 at 14:18

Why am I getting query timeout despite setting a large timeout value in appsettings

0 Answers0