Long running Stored Procedure in SQL Server 2012
We have a stored procedure that takes around 40 to 60 seconds sometimes to finish.
As it depends on customer data.
Now the issue is when users start this SP from a web form, sometimes the users get timeout error from SQL Server or Asp.net Ajax as well.
Now we don't want to increase the time-out limit.
Is there something like some background process or task which we can run on the SQL server and show the users processing and timely check whether that task is finished or not?
Is there anything like this in SQL Server?
David Johnson, CD
what are the long running processes in the execution plan of the stored procedure
ASKER
David,
This SP has more than 100 lines to execute, so there is no one specific thing that is taking time.
It keeps changes user by user
This SP has more than 100 lines to execute, so there is no one specific thing that is taking time.
It keeps changes user by user
You can schedule a SQL Agent Job to execute SP and store result in an intermediate table.
Your web app will select result from the intermediate table with zero waiting time.
Your web app will select result from the intermediate table with zero waiting time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.