ASP.NET - MSSQL Server Database Timeouts
One of the most disturbing aspect of most database applications is connection timeouts. Most of us might have faced this at one time or other. It would be helpful if we have complete control over the timeout and execution of our database queries/procedures.
There are 3 different places that you need to check if you face a timeout in your application. Let us say, you have an application that runs a lengthy stored procedure and you started getting a timeout error. You tried to optimize the procedure to the maximum possible extend. At this stage, it is desirable to have a way to extend the timeout so that the user will see the results even after waiting for a little longer.
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 timeout value to "0" you can instruct the server to allow long running queries. Or if you want to have a specific number of seconds specified, you can do so.
If you are working on SQL Server 2005 and / or using SQL Server Management Studio, you will see a different UI for the property page.
By changing the query timeout, you can make sure that the database server can tolerate a prolonged query execution as configured.
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.
So the question is "how do I control my query timeout then?" Well, thats where we need CommandTimeout property of the SQLCommand object.
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.
In an ASP.NET+SQLServer application there are 3 different timeout settings. Setting a timeout value in the database server will allow client applications to run longer queries within the time span that the server allows. Setting the timeout of a connection object (either thru connection string in web.config or by explicitly changing the property of the connection object) allows you to control the timeout period while attempting to establish a connection to a database server. Setting the timeout value of a command object allows you to control the query execution timeout. This is what is going to help you to execute long-running queries.
Note: The settings on the database server will take precedence over the settings by the client application. For example, if the timeout set on the database server is 60 seconds, and if you set a timeout of 2 minutes to your command object and if the command is taking more than 60 seconds, it will timeout at 60 seconds.
Hope this helps!
By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
There are 3 different places that you need to check if you face a timeout in your application. Let us say, you have an application that runs a lengthy stored procedure and you started getting a timeout error. You tried to optimize the procedure to the maximum possible extend. At this stage, it is desirable to have a way to extend the timeout so that the user will see the results even after waiting for a little longer.
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.
By changing the timeout value to "0" you can instruct the server to allow long running queries. Or if you want to have a specific number of seconds specified, you can do so.
If you are working on SQL Server 2005 and / or using SQL Server Management Studio, you will see a different UI for the property page.
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.
- 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.
Summary
In an ASP.NET+SQLServer application there are 3 different timeout settings. Setting a timeout value in the database server will allow client applications to run longer queries within the time span that the server allows. Setting the timeout of a connection object (either thru connection string in web.config or by explicitly changing the property of the connection object) allows you to control the timeout period while attempting to establish a connection to a database server. Setting the timeout value of a command object allows you to control the query execution timeout. This is what is going to help you to execute long-running queries.
Note: The settings on the database server will take precedence over the settings by the client application. For example, if the timeout set on the database server is 60 seconds, and if you set a timeout of 2 minutes to your command object and if the command is taking more than 60 seconds, it will timeout at 60 seconds.
Hope this helps!
By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
