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

Question

User-1355965324 posted

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();

Thanks

Pol

Answer 1

User475983607 posted

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 ?

SQL server does not care if a batch is submitted from SSMS or a web application.   The typical issue is returning thousands of records or a poorly designed DB schema.  The community cannot reproduce your environment or the timeout.  Plus, you did not share any relevant code or details other than the error.  I recommend basic troubleshooting.  Use SQL profiler to capture what's a happening.   Take a look at the execution plan.  Use the Visual Studio debugger to look for other errors in your code.  Basic stuff...

Answer 2

User-1355965324 posted

If there is any sql  index, schema  problem  why it is coming fast when I  execute the stored procedure from sqlserver. The Result is getting fast if I  run from sqlserver. I tried also in ado.net  code in mvc , still it is very slow. but when I run just a 3 days report it will come . If I  give for 3 month or for  two month it will be slow even using ado.net. but it will run fast from sqlserver

 DataSet ds = ReportBLL.GetEmployeeAttendanceReport("242", fromDate, toDate, "NA,HO,DO,SI,MA,FU,TI,DE,EX,CO", connectionSettings);

This is the way I am passing the parameter in to the stored procedure

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();

// Inside Stored Procedure  receiving the parameter as given below
ALTER PROCEDURE [dbo].[goGetEmployeeAttendanceReport] (
		    @Employee varchar(MAX) 
		   ,@FromDate smalldatetime
		   ,@ToDate smalldatetime 
		   ,@Attendancetype varchar(MAX) 
           )
AS 

Answer 3

User-474980206 posted

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.

Answer 4

User-1355965324 posted

Please can you advise me how to check the ado version with query plan

Answer 5

User1312693872 posted

Hi,polachan

By default,connections through Sql Server Management Studio have SET ARITHABORT ON. But ADO.NET connections do not. It will use the

pre-compiled cached query plan that the SSMS was using, so it will be time out.

You can set it OFF When using stored procedure, or using these code as admin on database, and then you can ignore the ARITHABORT settings.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Best Regards,

Jerry Cai