Hi people,

I am wondering how to do this. Let's say that you execute a long running
insert or whatever stored procedure from your application. It runs for a
while (minute or two) and it runs on the SQL Server. So your application
doesn't know it's status. Is it possible to get the status of the work and
how long it will take, so you can inform your user by displaying a
progressbar or something. The problem I have is to get an information about
the status of procedure from the SQL Server and not how to show a
progressBar.

Any ideas?

Zvonko Biskup

Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> I am wondering how to do this. Let's say that you execute a long
> running insert or whatever stored procedure from your application. It
> runs for a while (minute or two) and it runs on the SQL Server. So your
> application doesn't know it's status. Is it possible to get the status
> of the work and how long it will take, so you can inform your user by
> displaying a progressbar or something. The problem I have is to get an
> information about the status of procedure from the SQL Server and not
> how to show a progressBar.

In general terms it's not possible.

For a specific procedure it could be possible depending on what it does.
A simple case is a procedure that runs a cursor. In this case you could
send informative messages with RAISERROR WITH NOWAIT. (Not PRINT, because
PRINT gets buffered.) Then again, the end user is better served if you
skip the cursor and perform the processing directly.

A similar approach could be applied to procedures that performs several
updates, although chances are good that all the time is spent on a
specific statement.

And for a procedure which consists of a single statement it is about
impossible. Possibly, you could have a parallel thread which runs a
query with NOLOCK that determins how many rows have been updated/inserted.
But if what's takes time is to locate the rows, this is not a very
meaningful operation.

Add to this that a query may be delayed to blocking, in which case you
have no clue how long time it will take.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland, thanks for the prompt response.

So generally, I am considering that it is far quicker to let everything
regarding computing and large insert be done directly in the database. But I
can not inform my user what is going on.
Then I should do everything on a client computer. But then there is a lot of
bottlenecks during server-client communication and a very poor performance,
but my users get a beautifull status bar and counter and stuff.
What to do, and what you guys are doing? Just a discussion (as this is a
discussion board)

Thanks
Zvonko



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97FC7BE8A37D8Yazorman@127.0.0.1...
> In general terms it's not possible.
>
> For a specific procedure it could be possible depending on what it does.
> A simple case is a procedure that runs a cursor. In this case you could
> send informative messages with RAISERROR WITH NOWAIT. (Not PRINT, because
> PRINT gets buffered.) Then again, the end user is better served if you
> skip the cursor and perform the processing directly.
>
> A similar approach could be applied to procedures that performs several
> updates, although chances are good that all the time is spent on a
> specific statement.
>
> And for a procedure which consists of a single statement it is about
> impossible. Possibly, you could have a parallel thread which runs a
> query with NOLOCK that determins how many rows have been updated/inserted.
> But if what's takes time is to locate the rows, this is not a very
> meaningful operation.
>
> Add to this that a query may be delayed to blocking, in which case you
> have no clue how long time it will take.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> So generally, I am considering that it is far quicker to let everything
> regarding computing and large insert be done directly in the database.
> But I can not inform my user what is going on. Then I should do
> everything on a client computer. But then there is a lot of bottlenecks
> during server-client communication and a very poor performance, but my
> users get a beautifull status bar and counter and stuff. What to do, and
> what you guys are doing? Just a discussion (as this is a discussion
> board)

What we do? Write so fast code, that there is no need for progress bars! :-)

The root problem is that SQL Server does not give any progress information.
And that might be just as well. Recall that the optimizer builds the query
plan from statistics sampled about the data. From this it makes an estimate
of what is the best plan. Any progress information would be based on this
estimate. And if the estimate is wrong - which is not unusual - the progress
information would be too. I can't speak for anyone else, but personally
I find incorrect progress information to be worse than no progress
information at all.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

So, one more thing.

Let's say that a user clicks a button that executes a stored procedure
which computes some figures: how will my application know when the procedure
is done, so it can get a result on screen.
How to achieve that?

Zvonko Biskup



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97FC86C30C3CEYazorman@127.0.0.1...
> What we do? Write so fast code, that there is no need for progress bars!
> :-)
>
> The root problem is that SQL Server does not give any progress
> information.
> And that might be just as well. Recall that the optimizer builds the query
> plan from statistics sampled about the data. From this it makes an
> estimate
> of what is the best plan. Any progress information would be based on this
> estimate. And if the estimate is wrong - which is not unusual - the
> progress
> information would be too. I can't speak for anyone else, but personally
> I find incorrect progress information to be worse than no progress
> information at all.
..com/sql/prodinfo/previousversions/books.mspx

Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> Let's say that a user clicks a button that executes a stored procedure
> which computes some figures: how will my application know when the
> procedure is done, so it can get a result on screen.

All client API provides synchronous methods to issue a query or run a
stored procedure. With these methods you simply wait until the API call
returns.

Some API:s also provides asynchronous methods which permits the application
to other things in the mean while. You will have to check the documentation
for the client API to find out whether the query has completed.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

If you actually have things running so that you drop a command off at
the database and don't have to wait for it (but it will complete on its
own), then all you really need is a status table or queue table.

With this route, you could drop a "task" into a table with fields
indicating its status. Then your front end would check the status in
the table periodically. Using some of Erland's suggestions, you could
even incorporate progress information if any existed (e.g. 3/10 steps
complete).

I would tend to agree with others though in that wildly inaccurate
status information is worse than not knowing.

Take airline sites for an example. You key in your travel info, then
you see a spinning wheel for anywhere from 3 seconds to over a minute.
You have no idea how long it will take but from experience, you know
what is "reasonable". I think it'd be aggrevating if they would give
you a percentage that represented progress in terms of anything other
than time (what if that last 2% takes 90% of the total time?).

Michael

Zvonko wrote:
> So, one more thing.
>
> Let's say that a user clicks a button that executes a stored procedure
> which computes some figures: how will my application know when the procedure
> is done, so it can get a result on screen.
> How to achieve that?
>
> Zvonko Biskup
>
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns97FC86C30C3CEYazorman@127.0.0.1...
> > What we do? Write so fast code, that there is no need for progress bars!
> > :-)
> >
> > The root problem is that SQL Server does not give any progress
> > information.
> > And that might be just as well. Recall that the optimizer builds the query
> > plan from statistics sampled about the data. From this it makes an
> > estimate
> > of what is the best plan. Any progress information would be based on this
> > estimate. And if the estimate is wrong - which is not unusual - the
> > progress
> > information would be too. I can't speak for anyone else, but personally
> > I find incorrect progress information to be worse than no progress
> > information at all.
> .com/sql/prodinfo/previousversions/books.mspx