Topic:   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Feb 11, 2021 11:02 2 Replies 127 Views SAI

When I am calling a stored procedure from asp.net core the following error is coming.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the parameter value is being passed for the execute

'242'
{01/01/2020 00:00:00}
{31/12/2020 00:00:00}

 
'NA,HO,DO,SI,MA,FU,TI,DE,EX,CO'

 

But when I execute the same procedure from sqlserver , it is coming very fast. Any idea why it is being showed slow here in asp.net core ? Please any help would be very appreciated.

 SqlParameter parEmpID = new SqlParameter("@Employee", employeeIds);
                SqlParameter paryearFrom = new SqlParameter("@FromDate", fromDate);
                SqlParameter paryearTo= new SqlParameter("@ToDate", toDate);
                SqlParameter parType = new SqlParameter("@Attendancetype", attType);             
                data = _db.AttendanceOverViewModel.FromSql("dbo.goGetEmployeeAttendanceReport @Employee,@FromDate,@ToDate, @Attendancetype",
              parEmpID , paryearFrom , paryearTo, parType).ToList();
Prev Next
Topic Replies (2)
  1. 1
    idnkx user

    PARTH

    Looks like you have a query that is taking longer than it should. From your stack trace and your code you should be able to determine exactly what query that is.

    This type of timeout can have three causes:

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

    A deadlock can be difficult to fix, but it's easy to determine whether that is the case. Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

    The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics:

    exec sp_updatestats
    If that doesn't work you could also try

    dbcc freeproccache
    You should not do this when your server is under heavy load because it will temporarily incur a big performace hit as all stored procs and queries are recompiled when first executed. However, since you state the issue occurs sometimes, and the stack trace indicates your application is starting up, I think you're running a query that is only run on occasionally. You may be better off by forcing SQL Server not to reuse a previous query plan

    For the third issue, you can easily determine whether the query needs tuning by executing the query manually, for example using Sql Server Management Studio. If the query takes too long to complete, even after resetting the statistics you'll probably need to tune it.

    1
    idnkx user

    PADMAKEECHU

    building a dataset is slower then then the display in sql devops. with devops you need to wait to the query has completed (not just when the first screen is filled) to know how long it takes.
    you ca also check if the ado version is using the same query plan.

Leave a Reply
Guest User

Not sure what course is right for you?

Choose the right course for you.
Get the help of our experts and find a course that best suits your needs.


Let`s Connect