SqlCommand object, what length of time for CommandTimeout?

Question

How do I decide what length of time to use as a timeout when using an SqlCommand object?

On parts of the code I'm working on (written by somebody else) I have:

cmd.CommandTimeout = 60;

Which I think is quite short. However, I have seen some people in forums talking about setting it to 30000, which seems too long.

How do I know what is best for my application?

Canotas

Answer 1

It seems that people are confused as to whether this is seconds or milliseconds. The documentation states that the timeout is in seconds. A 1-minute timeout seems reasonable for most queries. An 8+ hour timeout doesn't seem reasonable.

Do you have queries you're expecting to take longer than a minute? If so, raise it to a value you expect to be higher than anything you'd see if everything is working properly, but not so high as to take forever to alert you to a problem. (For example, you might go from 1 minute to 5 minutes.)

Crozier

Answer 2

The default value is 30 seconds (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx).

If your database is on the same machine you usually don't have to change it. Just try it out: if you experience connection timeouts increase the value.

Flogging

Answer 3

You should calculate how much time will take the longest possible query and probably multiply that value by 1.5 to handle server overload, network traffic, etc ...

Stillas

Answer 4

This is in SECONDS and it depends entirely on how long the actual SQL command is likely to take.

For example example, most of our commands run sproc's that should take no longer than 30 seconds to complete, however there are a couple that run for much longer, which means they have their own high valued timeouts.

You'll need to profile how long on average your routine takes then adjust the timeout accordingly, and remember to leave room for variables like latency etc.

Canescent

Answer 5

SqlCommand.CommandTimeout is used to set waiting time before terminating the attempt to execute a command, which means that the time you wanna give your SqlCommand to execute particular sql query or stored procedure and wait for it to complete. If command doesn't completed in specified time than it will be terminated and exception will be thrown..

So, setting the value of CommandTimeout totally depends on your application and requirements, if you know the sql statement or stored procedure you are executing will take little than set it to value greater than 60, otherwise 60 seconds seems to be enough to wait for completion of any statement.

Gurango

Are there any consequences in increasing this value to 5 mins. I am developing a WCF application which serves WPF clients. – Mcnutt