Link to home
Start Free TrialLog in
Avatar of yadavdep
yadavdepFlag for India

asked on

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

Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

what are the long running processes in the execution plan of the stored procedure
Avatar of yadavdep



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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial