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

• • Reply
  polachan

  Member

  416 Points

  1346 Posts

  Timeout expired. The timeout period elapsed prior to completion of the operation or the server i...

  Feb 04, 2021 10:31 PM|polachan|LINK

  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

• • Reply
  mgebhard

  All-Star

  53981 Points

  24215 Posts

  Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the serv...

  Feb 04, 2021 10:54 PM|mgebhard|LINK

  polachan

  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...

• • Reply
  polachan

  Member

  416 Points

  1346 Posts

  Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the serv...

  Feb 04, 2021 11:19 PM|polachan|LINK

  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 
  

• • Reply
  bruce (sqlwo...

  All-Star

  58624 Points

  15864 Posts

  Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the serv...

  Feb 04, 2021 11:44 PM|bruce (sqlwork.com)|LINK

  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.

  bruce (sqlwork.com)
• • Reply
  polachan

  Member

  416 Points

  1346 Posts

  Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the serv...

  Feb 05, 2021 07:13 AM|polachan|LINK

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

• • Reply
  Jerry Cai

  Participant

  1120 Points

  372 Posts

  Re: Timeout expired. The timeout period elapsed prior to completion of the operation or the serv...

  Feb 08, 2021 08:15 AM|Jerry Cai|LINK

  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

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >