Give feedback to application during stored procedure execution

[Venkat]

Hi,

Is there any way to give feedback to the application while a stored
procedure execution is in progress? I am using sql server and my application
is built using c#. The stored procedure tries to delete around 200K records
and there is no way to give the progress of the deletion to the user. Some
times i get time out error as it takes much time. Is there any way to give
feedback as well to keep alive the stored procedure execution without
getting time out error?

Thanks in advance.

Venkat

 

[Bruce Wood]

You have more than one issue going on here.
First, you cannot report "Progress" to the UI unless you have some sort of
incremental event to do so. In your case, it sounds like the delete is an all
or nothing situation where you just have to wait until it is done. Possibly,
you could split it up into four parts and have a progress event for 25%, 50%,
75% etc. complete.

There _are_ several tricky ways to build adaptive progress indicators
for tasks like this. Here are a couple of ideas. Both of them rely on
you doing the database call on a background thread.

1. Before you start the database operation, have your UI thread kick
off a timer that will send you an event every so often. Display a
simple moving-bar progress indicator that doesn't commit to any firm
time-to-finish. This keeps something moving on the screen so that the
user gets the warm, fuzzy feeling that the app hasn't frozen up.

2. You could store away the total time taken for this operation and how
many times it was done (for example, in a configuration file or the
Registry). You can then calculate the average time that it takes, and
use a timer to throw up a "fake" progress indicator that shows %
complete based on past performance. You may even be able to work a
parameter into the equation: how many records are involved, which will
give you a more accurate answer. It doesn't have to be 100% accurate:
users are pleased if the operation completes when they thought it was
only 90% done, and usually patient when the progress indicator reaches
100% but the UI hasn't quite come back yet....