Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Track a stored procedure progress on front end.


Track a stored procedure progress on front end.

Author
Message
shweta-210049
shweta-210049
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9

hi all,

I have a VB dot net allplication which calls a stored procedure.This stored procedure is required to do a lot of computation ,inserts, updates etc for a large amount of data. This is taking a very long time. Is there a way i can intimate the front end as to the amount wrk completed. some sort of a progress bar.

Since the control shifts to the proc i am unable to update the progress bar as the execution proceeds.

Please help

thanks in advance

shweta


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
2 options come to mind.

1 - Have the proc update a progress table in the database, then query that table every few minutes to update the progress bar.

2 - Split the work into multiple stored procs. Have the program call each of the stored procs and in between calls you can update the progress meter.
shweta-210049
shweta-210049
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9

THe second option may be difficult to implement at this stage.

Did not catch the first one though , even if i manage to update a progress table, i will be able to query and send the result back to my front end only when the call to the procedure is over. which may not serve my purpouse.

is there a way with thresds or some thing this can be implemented??


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Create procedure megaWork @param as int --...
as
set nocount on
--reset progress bar

--step one

--update progress meter tab

--have the application open another thread and query that progress table

--keep working on the process of???

--reupdate the progress meter

--have the application recheck the progress table

set nocount off
go
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
keep in mind that if you do something like this you'll have to use a permanent table to update the progress meter table.

This might cause so big problems if you can have multiple instance of the same proc running simultaneously.
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593

What is the criteria of the progress? Does this procedure works with a cursor and the loops like While ....?

If yes, you can determine a number of loops to be completed and have a counter inside a loop that will update second row of a global temp table of 2 rows that is created inside the stored procedure with a name reflecting a user name for example or containing application-generated unique ID. That may resolve the multipel app instances issue. Then the same instance of the application connects to this temp table and gets the overall number of rows from the first row and a current counter from the second row. This will let it to update the status bar

Do not forget to drop this table at the end of the stored procedure

Yelena




Regards,
Yelena Varshal

ig_feldman
ig_feldman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 19
I think you can support Option 1 by making an asynchronous method call from your app tier.
This can be done using a local thread pool vs, crating a new thread. You can use delegates to handle this .



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search