Command Timeout on Queries

[Guest]

I'm reading mixed opinions on the effect of a command timeout, and wanted to
asked for clarification. From what I understood (and from what my testing
seems to indicate), CommandTimeout affect non-queries executes only.
Therefore if ADO was calling an SP, and the Command object was set to 1
second timeout, and a Command.ExecuteReader was then called, and the SP
required 20 seconds to pull the data, there would be no timeout and the data
would be returned. However, if an SP required 20 seconds to perform (an
update or whatever), and a Command.ExecuteNonReader was called, then it would
timeout.

Again, I'm not 100% sure, especially after reading so many differing
opinions on this. Can someone clarify?

THe reason I ask... we are running into "Timeout" issues sometimes with
SqlDataReaders and some suggest bumping up the CommandTimeout. I'm thinking
its a different problem.

 

[Frans Bouma [C# MVP]]

I'm reading mixed opinions on the effect of a command timeout, and
wanted to asked for clarification. From what I understood (and from
what my testing seems to indicate), CommandTimeout affect non-queries
executes only. Therefore if ADO was calling an SP, and the Command
object was set to 1 second timeout, and a Command.ExecuteReader was
then called, and the SP required 20 seconds to pull the data, there
would be no timeout and the data would be returned. However, if an
SP required 20 seconds to perform (an update or whatever), and a
Command.ExecuteNonReader was called, then it would timeout.

Again, I'm not 100% sure, especially after reading so many differing
opinions on this. Can someone clarify?

THe reason I ask... we are running into "Timeout" issues sometimes
with SqlDataReaders and some suggest bumping up the CommandTimeout.
I'm thinking its a different problem.

The timeout is for the timeout of the command. So if you call a proc,
that IS a command (EXEC procname) and it will time out if your timeout
value is lower than the time it takes the proc to execute.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------