Track a stored procedure progress on front end.

Question

Track a stored procedure progress on front end.

shweta-210049

Old Hand

Points: 360
More actions
May 26, 2005 at 8:33 am

#163838

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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 Old Hand Points: 360 More actions · Answer 2

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 SSC Guru Points: 294069 More actions · Answer 3

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 SSC Guru Points: 294069 More actions · Answer 4

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 SSC-Dedicated Points: 34286 More actions · Answer 5

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 Varsha

ig_feldman Valued Member Points: 55 More actions · Answer 6

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 .