tags:

views:

94

answers:

1

I have seen this show up several places in code, never with an explanation, just a cryptic comment above it (Declaration and execution included for an idea of context. It's just a standard procedure of running a SqlCommand):

//SqlCommand cmd = new SqlCommand();
//cmd.ExecuteReader();
//Read off the results

//Cancel the command. This improves query time.
cmd.Cancel ();

Basically, after finishing a query, it goes back and cancels it, claiming some performance boost. I suppose you might get some memory back when it goes and frees up the XmlReader, but usually it's about to go out of scope anyways.

I've never bothered with it before, but it's finally showed up in some code I'm reviewing. Does canceling a SqlCommand after running it in the code actually somehow speed it up, or is this just some weird programmer superstition?

+8  A: 

According to MSDN, this is correct.

The Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query. When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method.

Weird!

Will A
Huh. Didn't think to look in the SqlDataReader doc. Good catch!
fire.eagle
Looking @ the underlying code, it's clear that it does a fair amount of work on Close, however, I wouldn't imagine for one moment that this is all that significant at the end of the day.
Will A
Learned something ;-) Although I agree you'd have to be seriously bottlenecked on DB IO to notice the change.
gjvdkamp
@Lucas Heneks, thanks for posting the quote. I think it's important to note that cmd.Cancel() tells SQL Server to not send back the output params, return values or RecordsAffected. Usually you would set nocount on to disable sending back RecordsAffected and if there are output params or return values you probably want to retrieve them so I'm wondering about how applicable the technique is in most situations.
Tuzo