473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Give feedback to application during stored procedure execution

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
Dec 8 '06 #1
1 2487
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
Dec 8 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Warren Wright | last post by:
Hi group, I have a select statement that if run against a 1 million record database directly in query analyzer takes less than 1 second. However, if I execute the select statement in a stored...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
4
by: kanones | last post by:
I have some data that is been retrieved from a call to sql server stored procedure that I want to store for a period of time in a web farm architecture. I want to minimize the calls to sql server...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
9
by: David Helgason | last post by:
I'm calling one stored procedure with a prepared statement on the server with 6 arrays of around 1200 elements each as parameters. The parameters are around 220K in total. This is taking a...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
1
by: Venkat | last post by:
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...
4
by: TheRealPawn | last post by:
I'm trying to get the execution plan for a single stored procedure from Profiler. Now, I've isolated the procedure but I get all execution plans. Any ideas on how to connect the SPIDs so that I...
2
by: GUILLAUME LECOMTE | last post by:
Hi, I am a bit new to db2, and I am unable to find a proper command syntax where I can print values during execution in a stored procedure Pls help. regards, guillaume
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.