Execution Timeout Expired - Randomly for simple update command

asp.net entity-framework-6 sql timeout

Question

I am randomly getting execution timeout expired error using the Entity Framework (EF6). At the time of executing the below update command it gives randomly execution timeout error.

UPDATE [dbo].[EmployeeTable] SET [Name]=@0,[JoiningDate]=@1 WHERE ([EmpId]=@2)

The above update command is simple and it takes 2-5 seconds to update the EmployeeTable. But sometime the same update query takes 40-50 seconds and leads the error as

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. the statement has been terminated

.

For that I updated my code inside constructor of MyApplicationContext class can be changed to include the following property

this.Database.CommandTimeout = 180;

The above command should resolve my timeout issue. But I can’t find out the root cause of that issue.

For my understanding this type of timeout issue can have three causes;

  1. There's a deadlock somewhere
  2. The database's statistics and/or query plan cache are incorrect
  3. The query is too complex and needs to be tuned

Can you please tell me what the main root cause of that error is?

1
2
6/28/2017 11:26:43 AM

Popular Answer

This query:

UPDATE [dbo].[EmployeeTable]
    SET [Name] = @0,
        [JoiningDate] = @1
    WHERE ([EmpId]=@2);

Should not normally take 2 seconds. It is (presumably) updating a single row and that is not a 2-second operation, even on a pretty large table. Do you have an index on EmployeeTable(EmpId)? If not, that would explain the 2 seconds as well as the potential for deadlock.

If you do have an index, this perhaps something else is going on. One place to look is for any triggers on the table.

0
6/28/2017 11:30:09 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow