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

[Marc Gravell]

In addition to the other replies, you could (as already suggested) batch the
deletes; i.e. you could try deleting perhaps 5000 at a time, looking at the
affected count each time until it gets to zero (SET ROWCOUNT and @@ROWCOUNT
can be of use here). This gives you a chance to report accurate progress
between batches, avoids a *massively* long-running operation, etc. The
downside is that you lose some isolation; I would not advocate deleting 200k
records in a transaction, especially if it takes ages to do it and will
involve 40 hops to the server. But that means that other users could see the
data in transit. This may or may not be an issue.

The other (rhetorical) questions are more SQL-centric: what is the query
that is doing this? is it optimised? For instance, are you lacking a key
index. Why is it so expensive to delete? Could you perhaps flag the rows
*for* deletion (but not delete them yet), and worry about that in an
agent-job; would a "delete me" (bit) column suffice? How about a table with
just the PKs; or just the relevent FKs? are you running in a transaction?
and if so should you be?

Marc