Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Track a stored procedure progress on front end. Expand / Collapse
Author
Message
Posted Thursday, May 26, 2005 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 2, 2010 4:06 AM
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

 

Post #185400
Posted Thursday, May 26, 2005 8:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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.
Post #185406
Posted Thursday, May 26, 2005 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 2, 2010 4:06 AM
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??

Post #185414
Posted Thursday, May 26, 2005 8:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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
Post #185415
Posted Thursday, May 26, 2005 8:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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.
Post #185417
Posted Thursday, May 26, 2005 3:06 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:44 PM
Points: 3,475, Visits: 583

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

Post #185543
Posted Friday, April 13, 2012 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 13, 2012 1:57 PM
Points: 1, Visits: 16
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 .



Post #1283410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse