SqlCommand.CommandTimeout Property

I am not sure of your query but if you set the timeout property on the sql command object to 0, the query will not timeout.   (However, this is normally a really bad thing to do.)  One thing to look for is WHY the query is running so long.  I would suggest looking at the execution plan and making sure you have the necessary indecies set for how the query is obtaining data.  If it is just processing huge amounts of data, then it is processing huge amounts of data and it can take a while.  After you get a basline about how long the query would nurmally run, I would set the timeout value to the necessary value and not continue to use 0.

$0

HARDWARE : SQL Server R2 - Running several intense apps - No possibility to load balance at this time.

ISSUE: ASP.NET 4.0 application is making a SQL Request that times out after about 30 seconds.

The KB Articles I've read only reveal a potential problem with my servers, memory, tuning of the SQL (stored proc's), etc and offers a temporary fix to add some code to the stored proc's that makes the request to SQL.  This I would suspect allows for additional SQL resource time. 

The ultimate fix, according to the articles I've read, is to find and destroy the factors causing the SP's to run longer than 30 seconds, however, I am assured by our engineering team that the network and hardware running the app and sql server are more than adequate for the job thus the problem lies in how I wrote the SQL.

From the SQL Server 2008 R2 Management Console I can replicate the selection process used by the tableadapters and can return over 2,355,638 rows of data in 4 minutes and 40 seconds. I do not know if that is a good rate of return for my query, however, using the parameters from the user request the system returns 132,294 rows of data in 1:02 seconds again is this a good speed?  I expect this to be somewhat of a static process and the tables accessed will only grow in size.

I know what needs to be done in the long term but right now I need to open the connection to run AS LONG AS IT NEEDS TO!!!!

Your suggestions are welcomed.


**** ERROR CODE RETURNED ****


Server Error in '/' Application.


Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +59
System.Data.SqlClient.SqlDataReader.get_MetaData() +118
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6387873
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6389442
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +538
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +256
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +21
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +325
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +504
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +324
ACADataAccess.ACADatabaseTableAdapters.State_zipcode_planTableAdapter.GetData(String StateCode, String ZipCode, String PlanCode, String InsuredAge, String Tobacco, String Gender) +1093

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner) +0
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +640
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +38
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +1234
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1992
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +28
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +274
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +46
System.Web.UI.Control.PreRenderRecursiveInternal() +113
System.Web.UI.Control.PreRenderRecursiveInternal() +222
System.Web.UI.Control.PreRenderRecursiveInternal() +222
System.Web.UI.Control.PreRenderRecursiveInternal() +222
System.Web.UI.Control.PreRenderRecursiveInternal() +222
System.Web.UI.Control.PreRenderRecursiveInternal() +222
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4201



Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

When using SPROC it seems that the CommandTimeout property effects the command, the callback is called after the default timeout (30 sec) and time out exception is thrown when calling the SqlCommand.EndExecuteReader. $0anyway, when using plain SQL text with the command the callback isn't called.$0

Asynchronous method do not ignore CommandTimeout under certain conditions. Read this thread for details: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/1aafaefe-037b-4a0f-80fd-a678d4e1daef.

Making the CommandTimeout property an int datatype means that the API is less discoverable as it is not clear what measurement is being used. A user needs to refer to documentation to figure out if the value is milliseconds or seconds.