How do I increase the timeout period for my stored procedure call (see error)?

Question

 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:

Line 91: 
Line 92:             DataSet getData;
Line 93:             getData = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "Course_NewReportGet_Get_Sav", objPara);
Line 94: 
Line 95:             foreach (DataRow dr in getData.Tables[0].Rows)


Source File: c:\Users\Ryan\bancroft archive\santiago\santiago code\trunk\admin\tools\Optimus.aspx.cs    Line: 93

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) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
   Mexico.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) +149
   Mexico.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) +93
   admin_tools_Optimus.GetUsers() in c:\Users\Ryan\bancroft archive\santiago\santiago code\trunk\admin\tools\Optimus.aspx.cs:93
   admin_tools_Optimus.GetCompanies() in c:\Users\Ryan\bancroft archive\santiago\santiago code\trunk\admin\tools\Optimus.aspx.cs:75
   admin_tools_Optimus.Proceed(Object sender, EventArgs e) in c:\Users\Ryan\bancroft archive\santiago\santiago code\trunk\admin\tools\Optimus.aspx.cs:43
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Answer 1

Before calling set the connection's ConnectionTimeout property.

conn.ConnectionTimeout = 90; //Seconds for timeout

Answer 2

You can raise the timeout period on the command by setting the SqlCommand.CommandTimeout property.

Answer 3

Perhaps this doesn't apply, but I think it should be mentioned none the less. The default CommandTimeOut is 30 seconds. This should be plenty of time for any actions that are happening in 99.9% of a web application. Unless you are absolutely sure that the action should take longer than that, you should spend your time trying to optimize the sql so that it returns in <30 seconds. Proper indexing is a good place to start for that.

Answer 4

+1 to Al because 30 seconds should be plenty. Making the timeout period longer is really only a bandage to the underlying problem.

In my experience the stored proc is too expensive 90% of the time. When I was reviewing these errors last, I had a general rule of thumb that no stored proc cost more than 1.00. The more expensive they are, the greater the risk we had of blocking and generating these exceptions.

Answer 5

ConnectionTimeout Property is Read Only.