Timeout expired error in aspx page

Question

I have a page in aspx with code behind in .aspx.vb format. This code is using stored procedure written in SQL. This code runs without any problem in test server, but in Production server we are getting Timeout error:

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.

[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) +212
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1099
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +112
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6319508
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6320577
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +19
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +221
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +573
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +161
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2805078
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +27
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +261
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +46
   System.Web.UI.Control.PreRenderRecursiveInternal() +108
   System.Web.UI.Control.PreRenderRecursiveInternal() +224
   System.Web.UI.Control.PreRenderRecursiveInternal() +224
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3394

I tried adding Connection Timeout in Connection String like:

    <add name="ConnectionString1" connectionString="Data Source=ExampleServer;Initial Catalog=Exampledb;Persist Security Info=True; Connect Timeout = 300; User ID=test;Password=test" 
providerName="System.Data.SqlClient" />

Adding Connect Timeout in querystring didn't work. The strange thing is it doesn't give error in test environment but in production it gives this error. I tried running Stored Procedure in Microsoft SQL Management Studio, the query runs in 2 - 3 seconds and gives output fast without taking long time. I compared the aspx code, code behind and stored procedure for both test environment and production environment, there is no difference.

Any suggestion or idea what might be causing this?

Executing Stored Procedure:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" SelectCommand="EXEC   [dbo].[Example_StoredProcedure]
        @year1 = @year1,
        @term1Term = @term1,
        @year2 = @year2,
        @term2 = @term2,
        @year3 = @year3,
        @term3 = @term3,
        @year4 = @year4,
         @term4 = @term4
        ">

This SqlDataSource1 is being used as:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False " 
                DataSourceID="SqlDataSource1" EnableModelValidation="True" AllowSorting="True"
               >

Answer 1

This is not about the Connection-Timeout - it's about the Command Timeout. Calling the same Request multiple times causes the SQL-Server to load it from the cache - this could be the reason for the fast result of 2-3 seconds.

string expr = "sp_YourProcedure";
DataSet ds = new DataSet();
using (SqlConnection Conn = new SqlConnection(YourConnString))
{
    using (SqlCommand sCommand = new SqlCommand(expr, Conn))
    {
        sCommand.CommandType = CommandType.StoredProcedure;
        sCommand.CommandTimeout = 600; // set CommandTimeout here
        SqlDataAdapter sdAdapter = new SqlDataAdapter(sCommand);
        sdAdapter.Fill(ds);
    }
}