CODEWITHSUNDEEP
sql-serverado.nettransactions
Eugene
Eugene

Reputation: 1535

What is the best approach of breaking long running stored procedures?

What is the best approach of breaking long running stored procedures (up to 20 minutes)? Inside the Stored procedure is wrapped in a transaction. If I close connection will this transaction be rolled back? Another approach is to start a transaction in C# before I start the stored procedure and when I want to cancel the stored procedure I just need to rollback the C# transaction.

Upvotes: 1

Views: 377

Answers (3)

MatthewMartin
MatthewMartin

Reputation: 33143

You can set a time out in three place-- the connection, the command, and if you are ultimately programming a web page-- in the page time out.

The relevant time out in this case is the command time out.

Update: Cancelling by a user's event: To cancel your command by a user event, call Cancel() on the command. I haven't written code to test this, but I suspect that once you call ExecuteReader() it will block, so you'd need an async call-- BeginExecuteNonQuery(), which really is a pain to set up-- it requires extra things on the query string and I think it requires SQL2005+

UPDATE: Re: Transactions C# (or ADO.NET) transaction code adds about two lines of code and guarantees invocations of stored procedures (which may have more than one statement in them, in not today, maybe a year from now) succeed or fail as a unit. They normally are not a source of poor performance and can be a source of poor performance when not used-- e.g. a long series of inserts runs faster in a transaction.

If you do not call CommitTrans() the transaction will rollback, you do not have to explicitly call Rollback()

Upvotes: 1

hemant
hemant

Reputation: 2445

Set Timeout at the time of beginning the transaction.

Upvotes: 0

Andomar
Andomar

Reputation: 238106

If you close the connection, SQL Server will rollback the transaction if it notices the disconnect before the transaction commits. There'll be a (very) small time window where the transaction might complete just when you disconnect.

A custom transaction adds complexity and has few benefits for a single stored procedure call. So I'd go for the disconnect.

Upvotes: 2

Related Questions