I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.
Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background.
Also, I do not want the web server processes to be busy with the running stored procedure.
are you passing any input parameters, if not then why not schedule the job within your DB. If you are passing parameters then brief your requirements plz.
The application includes some calculations of items. The user has a button to re-calculate an item. When he clicks the button, the page will call a stored procedure that will insert a record in a table with re-calculation requests. Then the same stored proceedrue
will call another stored procedure to process re-calculating the items in the table one by one. Something like the following
ALTER Procedure [re_calculate_item] (@item_id int, @requested_by varchar(50) = null)
as
insert into t_item_recalculate (item_id, requested_by, requested_on, status)
values (@item_id, @requested_by, getdate(), 1)
exec re_calculate_pending
re_calcualte_pending is the stored procedure that does a huge amount of processing that consumes time. But it does not return any value, it only updates few tables with re-calculated values.
i) Threading.
ii) Open that page from where processing start in another window.
iii) I am quite hopefull about this method. schedule the job,according to the requirment,data and trafiic u can schedule the time frequency.
See,User open that page,input some data and submit.Your store procedure start working.
Here don't call ur SP rather store the input from individual user in seperate table along with suitable id.
Structure of new table (id,parameter1,parameter1 from user.......... and status (bit).
And in ur SP,make some changes.Like The Sp will pick all record whose status=1
Cursor or while loop
Do its work as usual
and finally update the new table status=0.
Again pick next id and so on.
On fix time job will start automatically.
and user can continue doing its job.
It has ceratin benefit also,application will become fast.and so on.
Only thing is I am not sure about is Frequency Of that Page .
How frequently that page is use ?
amroose
Member
55 Points
15 Posts
Call SQL Stored Procedure Asyncronously
Aug 09, 2010 12:23 PM|LINK
Hello,
I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.
Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.
Any help, please.
Best regards,
Imran
sansan
All-Star
53942 Points
8147 Posts
Re: Call SQL Stored Procedure Asyncronously
Aug 09, 2010 07:51 PM|LINK
you can initiate a callback to do that.
check this link
http://www.simple-talk.com/dotnet/asp.net/asynchronous-client-script-callbacks/
amroose
Member
55 Points
15 Posts
Re: Call SQL Stored Procedure Asyncronously
Aug 14, 2010 05:06 AM|LINK
Thanks Sansan. But this is not what I need. Read the scenario carefully
tanatrajan
Participant
1784 Points
370 Posts
Re: Call SQL Stored Procedure Asyncronously
Aug 14, 2010 05:28 AM|LINK
are you passing any input parameters, if not then why not schedule the job within your DB. If you are passing parameters then brief your requirements plz.
amroose
Member
55 Points
15 Posts
Re: Call SQL Stored Procedure Asyncronously
Aug 22, 2010 05:50 AM|LINK
Hi tanatrajan,
The application includes some calculations of items. The user has a button to re-calculate an item. When he clicks the button, the page will call a stored procedure that will insert a record in a table with re-calculation requests. Then the same stored proceedrue will call another stored procedure to process re-calculating the items in the table one by one. Something like the following
re_calcualte_pending is the stored procedure that does a huge amount of processing that consumes time. But it does not return any value, it only updates few tables with re-calculated values.
amroose
Member
55 Points
15 Posts
Re: Call SQL Stored Procedure Asyncronously
Sep 06, 2010 03:03 AM|LINK
Any help, please.
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Call SQL Stored Procedure Asyncronously
Sep 06, 2010 06:24 AM|LINK
i) Threading.
ii) Open that page from where processing start in another window.
iii) I am quite hopefull about this method.
schedule the job,according to the requirment,data and trafiic u can schedule the time frequency.
See,User open that page,input some data and submit.Your store procedure start working.
Here don't call ur SP rather store the input from individual user in seperate table along with suitable id.
Structure of new table (id,parameter1,parameter1 from user.......... and status (bit).
And in ur SP,make some changes.Like The Sp will pick all record whose status=1
Cursor or while loop
Do its work as usual
and finally update the new table status=0.
Again pick next id and so on.
On fix time job will start automatically.
and user can continue doing its job.
It has ceratin benefit also,application will become fast.and so on.
Only thing is I am not sure about is Frequency Of that Page .
How frequently that page is use ?
any problem ask.
Kumar Harsh