Perf problem with a stored proc - closing a connection too slow.

Question

I have previously asked a question about a stored proc that was executing too slowly on a sql server box, however, if I ran the sproc in Query Analyzer, it would return under one second. The client is a .NET 1.1 winforms app.

I was able to VNC into the user's box and, of course, they did not have SQL tools installed, so I cranked up Excel, went into VBA and wrote a quick function to call the sproc with exact same params.

It turns out that the sproc does return subsecond and I can loop through all the rows in no time at all. However, closing the connection is what takes a really long time, ranging from 5 seconds to 30.

Why would closing a connection take that long?

Answer 1

The symptoms you describe are almost always due to an 'incorrect' cached query plan. While this is an large topic (see parameter sniffing here on SO), you can often (but not always) alleviate the problem by rebuilding a database's indexes andensuring that all statistics are up to date.

Answer 2

If you're using a SqlDataReader, one thing you can try is once you have all the data you need, call Cancel on the SqlCommand before calling Close on the SqkDataReader. This will prevent the out parameters and return values from being filled in which might be the cause of the slowness to close the connection. Do it in a try catch block because it can throw a cancelled by user exception.

Answer 3

Connection pooling?

That, or I'd check for any service packs or KB articles for the client library.