Brian Chaplin

Subscribe to Brian Chaplin: eMailAlertsEmail Alerts
Get Brian Chaplin: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

Monitoring the Progress of a Long Running Stored Procedure

Monitoring the Progress of a Long Running Stored Procedure

Many reporting applications use stored procedures that sometimes take a while to run. Even with fast database servers, response time can be measured in minutes after your best efforts at tuning. Users who are used to seeing progress bars on their Internet downloads expect a similar visual clue from their database applications. How do you monitor and display the progress of a long-running stored procedure?

Solution
A straightforward solution to this problem is to have the stored procedure update a status table as it progresses through each step. Open up another connection to the database and display the results from the status table on a periodic basis until the stored procedure completes.

How to Implement
Although the concept is straightforward, each step has its nuances. I'll detail the 14 steps and add a fifteenth one for extra credit. These steps were developed with PowerBuilder 7.0 using PFC and Sybase Adaptive Server on Sun OS 5.6.

Tune Your Stored Procedure
Tune your stored procedure first and maybe you won't have to go beyond this step. It's not a good idea to make users wait for their reports, no matter how much eye candy you give them. In fact, this feature really came about as a side benefit to tuning my stored procedures. I tuned the procedures to run as fast as possible, hoping I wouldn't have any long-running ones left. When that happy situation didn't occur, I built a monitoring mechanism. I like to run my stored procedure from a telnet session with the Sybase statistics turned on.

  1. Set up your telnet viewer to capture the statistics. I'll use the Microsoft telnet viewer as an example (see Figure 1).

  2. Make the terminal buffer large so you can scroll back and see the statistics without opening the log (see Figure 2).

  3. Go into ISQL and turn on the statistics (see Figure 3).
    For Sybase use the command: <
    set statistics IO, time on
    To see the execution plans the database is using you can also enter:
    set showplan on
  4. Execute the procedure.
    exec your procedure 'parm1','parm2'
    go
    You should see a bunch of statistics fly by (see Figure 4).

  5. Close the log (see Figure 5).

  6. Open up the log file and view with a text editor (such as Wordpad).

    Review each step of the stored procedure. Look at the elapsed and CPU times and focus your efforts on the steps with large elapsed or CPU times. Review the showplan output to ensure that appropriate indexes are used, etc. If I find a step that has a high CPU or elapsed time, I review the I/O hits for each table in the join to see if there's a problem with the join. On some multitable joins I've found that the optimizer doesn't always make the best choice.

Create the Status Table
You'll need four columns in your table. I'll explain the pct_next column when I describe how to calculate time remaining. For now just create the table:

create table dbo.status
(host_id char(8) not null,
step_descr char(40) not null,
pct int not null,
pct_next int not null)
Update Your Stored Procedure to Write Out Its Status
Now that you have the numbers (see Figure 8), you can put them in your stored procedure.
  1. Get the connection ID.
    For Sybase, the function host_id() returns the process name for the connection. This is the key for the table. PowerBuilder will use this later to look up the status for its connection. select @host_id = host_id()
  2. Delete any previous status for this connection.
    delete from status where host_id = @host_id
  3. Insert the intial starting status.
    insert status values(@host_id,'Starting',1,10)
    The third parameter, the pct column, must be 1, not 0. Otherwise you'll get a divide by zero error when you use this to forecast time to complete.

    The second parameter, step_descr, should be something you want displayed initially like the word "Starting." The third value, pct, is the starting percentage you want to show the user. The fourth value, pct_next, should be the pct value of the first status update that follows this insert.

  1. Update the status at each significant step.
    For example, a couple of steps could look like this:
    Preparatory SQL (0 to 10%)
    update status
    set step_descr = 'Getting Customer Balances',
    pct = 10,
    pct_next = 25
    where host_id = @host_id

    SQL to get customer balances

    update status
    set step_descr = 'Getting Customer Addresses',
    pct = 25,
    pct_next = 50
    where host_id = @host_id

    SQL to get customer addresses

    Your final update should look something like this:

    update status
    set step_descr = 'Retrieving',
    pct = 99,
    pct_next = 100
    where host_id = @host_id

    5. Clean up your mess.
    delete from status where host_id = @host_id
Enable a Second Database Connection
Set up asynchronous database processing, which most databases support. Refer to your PowerBuilder documentation on how to set it up. Basically, you should:
  1. Set the dbparm database value.
    DBParm = "Async = 1"
  2. Enable the retrieverow event.
    Put a comment in the retreiverow of the DataWindow control that invokes your stored procedure.
  1. Define a second transaction object and connect to the database with it.
    u_tr gtran /* 2nd connection for progress checking */
    You could also use the transaction object if you're not using PFC:
    transaction gtran /* 2nd connection for progress checking */
  2. Create it in the application or application manager's pfc_open event.
    // Create a second connection for progress monitoring
    gtran = Create u_tr
    For non-PFC just code:
    gtran = Create transaction
  3. In the same event, set up the database parms.
    These should be the same as the primary connection. I just used the PFC of_CopyTo function, but you can just repeat your primary connection's database initialization.
    SQLCA.of_CopyTo(gtran)
  4. Connect to the database in the same event.
    li_ret = gtran.of_connect()
Get the Connection ID
Now you have to make PowerBuilder find the connection ID so it can access the status information.
  1. Write the RPC.
    I wrote a small stored procedure to return the results from the Sybase host_id() function and invoked an RPC for the u_tr transaction object. In Sybase this is basically a one-line stored procedure:
    create PROCEDURE get_hostid
    @host_id char(8) out
    as
    SELECT @host_id = host_id()
    Return @@error
  2. Define the RPC.
    Declare this procedure in the local external function of your transaction object, u_tr in this case:
    function long get_hostid(ref string host_id) RPCFUNC ALIAS FOR "dbo.get_hostid"
  3. Define a global variable for the connection ID.
    u_tr gtran /* 2nd connection for progress checking */
  1. Get the connection ID.
    Return the connection ID to the open event of your application or the pfc_open event of the application manager if you're using PFC.
    // get host_id for key to status table for progress checking
    gshost_id = ' '
    gtran.get_hostid(gshost_id)
    Your open or pfc_open event code should now look like this:
    // Create a second connection for progress monitoring
    gtran = Create u_tr
    SQLCA.of_CopyTo(gtran)
    li_ret = gtran.of_connect()

    // get host_id for key to status table for progress checkingbr> gshost_id = ' '
    gtran.get_hostid(gshost_id)

Create the User Event
Create the window user event that will show the status messages and progress bar. The timer object will call this. In this example it's called ue_showtimer.

Set Up the Timer
Before executing the DataWindow control's retrieve function to obtain your report, code the following in that event or function. I want to update the display every second so I set the second parameter of the of_Register() function to 1.

PFC
//create the timer
itmg_timer = CREATE n_tmg
//enable single notify service
itmg_timer.of_SetSingle(TRUE)
IF li_return = -1 THEN
MessageBox("Timer", "Error with of_Set-
Single")
END IF
//register this object and ue_showtime event with the timing service
IF IsValid(itmg_timer.inv_single) THEN
itmg_timer.inv_Single.of_Register (this, "ue_showtimer", 1)
end if
If you're not using PFC, use the timer object and manually register your event with the timer.

Clean Out the Status Table
Just in case the status table row wasn't deleted by the prior run, delete the row before retrieving the report. Put this in the same event. delete from status where host_id = :gshost_id using gtran; Disable the Application While the Stored Procedure Is Running
You'll want to disable much of your application while the stored procedure is running. In the retrievestart event of your DataWindow control, code something like this:

// tell rest of app that I'm retrieving
gb_retrieve = TRUE
// disable report viewing buttons and menu items
m_report.m_view.m_sort.enabled = FALSE
m_report.m_view.m_first.enabled = FALSE
m_report.m_view.m_priorpage.enabled = FALSE
m_report.m_view.m_nextpage.enabled = FALSE
m_report.m_view.m_lastpage.enabled = FALSE
m_report.m_view.m_filter.enabled = FALSE
Also, you'll want to set the DataWindow control pointer to an hourglass so the user knows the stored procedure is still running.
this.Object.DataWindow.Pointer = 'HourGlass!'
Calculate and Display Remaining Time
Forecasting when a stored procedure will finish is an educated guess at best. Aim for the stored procedure to write out a status about six to 10 times during its execution. At each step I can see how much time has elapsed and calculate the remaining time based on the elapsed time. The more status updates, the more chances I have to adjust the completion time as the procedure runs. It also makes it more interesting for your patient user.

Additionally, it's nice to be able to count down the seconds until you receive the next status or the procedure completes. To do this, though, the status query needs to return not only the percent completed but also the percent that will be completed on the next status query. That's why there's a pct_next column; so you can estimate how long you need to wait until the next status update. In the meantime, count down the elapsed seconds from when it was last calculated. If the next status is late in arriving, then the countdown will stop and begin gradually rising, as it anticipates a longer elapsed time calculation before the arrival of the next status update.

This code will display a progress bar and a text message in the bottom of the MDI frame. Make sure that your status description (step_descr column) contains a meaningful description the user will recognize.

Declare the following instance variables for the window containing the report DataWindow control.

time itm_spstart, itm_prevstart
integer ii_FirstPct, ii_prevpct,ii_threshold,ii_remaining /* for timer calculation */
boolean ib_spstarted
Copy the code in Listing 1 into the ue_showtimer event of the window containing the report DataWindow control.

You should see something similar to Figure 9 at the bottom of the MDI frame.

Figure 9
Figure 9: MDI status bar and progress text

Reenable the Application When the Stored Procedure Finishes
Don't forget to turn on what you turned off when you started the stored procedure. In the retrieveend event of your DataWindow control, code something like this:

// tell rest of app that I'm done retrieving
gb_retrieve = FALSE
// enable report viewing buttons and menu items
m_report.m_view.m_sort.enabled = TRUE
m_report.m_view.m_first.enabled = TRUE
m_report.m_view.m_priorpage.enabled = TRUE
m_report.m_view.m_nextpage.enabled = TRUE
m_report.m_view.m_lastpage.enabled = TRUE
Don't forget to reset the DataWindow pointer.
this.Object.DataWindow.Pointer = 'Arrow!'
Stop the Timer When the Stored Procedure Finishes
Put the following in the retrieveend event of your DataWindow control.
PFC itmg_timer.inv_single.of_UnRegister()
Destroy the Status Bar Object When the Stored Procedure Finishes
Put the following in the retrieveend event of your DataWindow control.

PFC

w_rpt_mdi_frame.inv_statusbar.of_SetBar
(FALSE)

Extra Credit: Add a Cancel Button
With a long running report it's always a good idea to let the user cancel it. This is especially the case after they realize, from your time-remaining message, that their quick and dirty report may be dirty but definitely not quick.

Set up a cancel button and have it trigger a cancel event in the window. Mine is called ue_cancelretrieve. In it I've coded the following:

itmg_timer.inv_single.of_UnRegister()
w_rpt_mdi_frame.inv_statusbar.of_SetBar
(FALSE)
dw_report.dbcancel()
ib_cancelled = TRUE
You'll need to react appropriately to a cancel of the retrieval. In my case I simply closed the sheet. My retrieve event looks like this:
li_ret = dw_report.of_retrieve()
if ib_cancelled then
close(this)
return
end if
Conclusion
This article presents the steps needed to implement a progress monitoring mechanism for long-running stored procedures. For traditional client/server applications this will save users from wasting time staring at the interminable hourglass cursor. For Internet applications this will help retain visitors to your site. These techniques can be easily adapted to PowerBuilder Internet applications to provide a status bar similar to the file downloads or page-loading progress status bar that users are used to seeing in their browsers.

More Stories By Brian Chaplin

Brian Chaplin is a project manager and computer consultant
specializing in PowerBuilder and Web development.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.