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

  • 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

    Thursday, February 4, 2021 10:31 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 8, 2021 8:15 AM

All replies

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

    Thursday, February 4, 2021 10:54 PM
  • 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 
    

    Thursday, February 4, 2021 11:19 PM
  • 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.

    Thursday, February 4, 2021 11:44 PM
  • User-1355965324 posted

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

    Friday, February 5, 2021 7:13 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 8, 2021 8:15 AM