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?
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
Your web app will select result from the intermediate table with zero waiting time.