Answered by:
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
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