Database Timeout Settings
The first place that you need to check is your database server, to see if the server accepts long running queries/procedures. You can do this by checking the properties of the database. If you are using enterprise manager right click on the server name and select properties.
The first place that you need to check is your database server, to see if the server accepts long running queries/procedures. You can do this by checking the properties of the database. If you are using enterprise manager right click on the server name and select properties.
By changing the query timeout, you can make sure that the database server can tolerate a prolonged query execution as configured.
Connection Timeout
The next setting that needs to be checked is the connection timeout. Connection timeouts can be configured either specifying the "timeout" value in the connection string or by explicitly modifying property of SqlConnection object.
The following example shows how to specify a connection timeout in the connection string.
data source=myServer;initial catalog=pubs;UID=sa;PWD=sa;timeout=6000
The above connection string specifies a timeout value of 6000 seconds.
If you do not prefer to set the timeout in the connection string, you can set the value directly to the ConnectionTimeout property of SQLConnection object.
This has been a cause of major misunderstanding with most people. I had been, and many other people might have been considering this setting as the query timeout. Many times in the past I had been wondering why my queries get a timeout, eventhough I have a longer value set in the connection string, as well as in the database timeout. But it was a wrong perception. The timeout value that you set to a connection string has got nothing to do with the query execution. This value is used to specify how much time the object has to wait while attempting to establish a connection to the server.
To execute a query or procedure we need a two step process.
Connection Timeout
The next setting that needs to be checked is the connection timeout. Connection timeouts can be configured either specifying the "timeout" value in the connection string or by explicitly modifying property of SqlConnection object.
The following example shows how to specify a connection timeout in the connection string.
data source=myServer;initial catalog=pubs;UID=sa;PWD=sa;timeout=6000
The above connection string specifies a timeout value of 6000 seconds.
If you do not prefer to set the timeout in the connection string, you can set the value directly to the ConnectionTimeout property of SQLConnection object.
This has been a cause of major misunderstanding with most people. I had been, and many other people might have been considering this setting as the query timeout. Many times in the past I had been wondering why my queries get a timeout, eventhough I have a longer value set in the connection string, as well as in the database timeout. But it was a wrong perception. The timeout value that you set to a connection string has got nothing to do with the query execution. This value is used to specify how much time the object has to wait while attempting to establish a connection to the server.
To execute a query or procedure we need a two step process.
- Connect to the data source
- Execute the SQL command.
So the question is "how do I control my query timeout then?" Well, thats where we need CommandTimeout property of the SQLCommand object.
Command Timeout
Command execution (query, stored procedure etc) can be controlled by assigning the timeout value to CommandTimeout proeprty of SQLCommand object. The default timeout is 30 seconds. Some of you might have experienced that your queries are getting a timeout after 30 seconds.
SQLConnection allows you to specify a timeout value in the web.config, however SQLCommand does not allow that. You will have to explicitly specify the timeout value before the command is executed. However you can crate a custom configuration key and set the value in the configuration file, then before the command is executed read the value from the configuration file and assign to the timeout property. This way, you can globally controll the timeout settings without the need to scan the entire source code and change the timeout settings.
Command execution (query, stored procedure etc) can be controlled by assigning the timeout value to CommandTimeout proeprty of SQLCommand object. The default timeout is 30 seconds. Some of you might have experienced that your queries are getting a timeout after 30 seconds.
SQLConnection allows you to specify a timeout value in the web.config, however SQLCommand does not allow that. You will have to explicitly specify the timeout value before the command is executed. However you can crate a custom configuration key and set the value in the configuration file, then before the command is executed read the value from the configuration file and assign to the timeout property. This way, you can globally controll the timeout settings without the need to scan the entire source code and change the timeout settings.
No comments:
Post a Comment