Command Timeout on Queries

14 Jul 2006 8:23 PM

MarkO

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.

15 Jul 2006 8:50 AM

Frans Bouma [C# MVP]

MarkO wrote:

Show quote

> 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#)
------------------------------------------------------------------------