c# - aspx页面超时过期错误

Question

我有一个 aspx 页面，其中包含 .aspx.vb 格式的代码。此代码使用用 SQL 编写的存储过程。此代码在测试服务器中运行没有任何问题，但在生产服务器中我们收到超时错误:

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

我尝试在连接字符串中添加连接超时，例如:

    <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" />

在查询字符串中添加连接超时无效。奇怪的是它在测试环境中没有给出错误，但在生产环境中却给出了这个错误。我尝试在 Microsoft SQL Management Studio 中运行存储过程，查询在 2 - 3 秒内运行，并且无需花费很长时间即可快速提供输出。我对比了测试环境和生产环境的aspx代码、代码隐藏和存储过程，没有区别。

任何可能导致此问题的建议或想法？

执行存储过程:

<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
        ">

此 SqlDataSource1 用作:

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

Answer 1

这与连接超时无关 - 它与 Command Timeout 有关.多次调用同一个请求会导致 SQL-Server 从缓存中加载它 - 这可能是 2-3 秒的快速结果的原因。

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);
    }
}