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

在查询字符串中添加连接超时不起作用。奇怪的是，它在测试环境中没有给出错误，但在生产环境中它会给出此错误。我尝试在 SQL 管理工作室Microsoft运行存储过程，查询在 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

这不是关于连接超时 - 而是关于命令超时。多次调用同一请求会导致 SQL 服务器从缓存中加载它 - 这可能是 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);
    }
}