c# - aspx网站一页的SQL超时
问题描述
我刚刚使用了一个使用 aspx 页面的 Web 应用程序,后面是 C#。都坐在 SQL Server 上。使用存储过程交付的所有数据。几乎所有的过程都是动态SQL,sql语句是根据传入的参数内置在过程本身中的。
有三个几乎相同的页面,唯一的区别是传递给 SQL 的参数和一些标签。该页面在打开时加载一个网格和三个下拉菜单。还有另外两个网格视图,但在提供更具体的信息之前不会加载它们。
随着网站的发布,其中一个页面得到了广泛的使用。并且有很多超时 SQL 超时错误。然而,如果我移动到三个页面中的另一个,它们的执行速度很快,加载数据时没有明显的停顿。如果我运行这些程序,它们都在一秒钟内运行,同时页面超时。所以,我不认为这些实际上是由于 SQL Server 性能缓慢而导致的超时。
我是一个 db 人,而不是一个编码员,他被说服自愿参加为几个俱乐部建立这个网站的时间。我一直在学习,我的许多答案都来自这个网站。但在这种情况下,我什至不知道我应该寻找什么。我在下面提供了似乎相关的信息。如果有帮助,我很乐意使用完整的 .aspx 和 .cs 进行更新。
页面加载很简单:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SelectedDogID.Text = "0";
SelectedTrialID.Text = "0";
SelectedHandlerID.Text = "0";
ddYear.DataBind();
ddTrial.DataBind();
ddTrial.Visible = false;
LabelTrial.Visible = false;
}
}
该网站是https://trialpoints.com/ 它是第一个按钮“USBCHA Sheep”,这是每个人都在关注的挣扎页面。后两个按钮几乎相同,包括使用几乎相同的数据集,但性能良好。
在我看来,我在页面上的 aspx 或 C# 上做了一些不正确的事情,因此它们无法处理多个请求(完全或至少很好)。在 C# 中我只有一个地方调用了 a 过程,其余的都设置为 aspx 数据源。那段代码是:
protected void ddTrial_SelectedIndexChanged(object sender, EventArgs e)
{
SelectedTrialID.Text = ddTrial.SelectedValue;
if (SelectedTrialID.Text != "0")
{
mvPoints.ActiveViewIndex = 3;
rbTrial.Checked = true;
GridViewIndTrial.DataBind();
//Now populate the fields at the top of the form
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand("SingleTrialReturnDetailsForPointsPage", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Organization", SqlDbType.NVarChar, 30));
cmd.Parameters.Add(new SqlParameter("@TrialID", SqlDbType.Int, 4));
cmd.Parameters["@Organization"].Value = Convert.ToString(tbOrganization.Text);
cmd.Parameters["@TrialID"].Value = Convert.ToInt32(ddTrial.SelectedValue);
conn.Open();
using (SqlDataReader read = cmd.ExecuteReader())
// Data is accessible through the DataReader object here.
{
while (read.Read())
{
lblTrialID.Text = (read["TrialID"].ToString());
lblTrialID.Text = (read["TrialID"].ToString());
lblTrialName.Text = (read["TrialName"].ToString());
lblTrialDate.Text = (read["TrialDate"].ToString());
lblTrialYear.Text = (read["TrialYear"].ToString());
lblDogsToPost.Text = (read["DogsToPost"].ToString());
lblDogsWithPoints.Text = (read["DogsWPoints"].ToString());
GridViewIndTrial.Caption = (read["TrialName"].ToString());
}
read.Close();
}
conn.Close();
conn.Dispose();
}
else
{
GridViewIndTrial.DataSource = null;
lblTrialID.Text = Convert.ToString("");
lblTrialName.Text = Convert.ToString("");
lblTrialDate.Text = Convert.ToString("");
lblTrialYear.Text = Convert.ToString("");
lblDogsToPost.Text = Convert.ToString("");
lblDogsWithPoints.Text = Convert.ToString("");
GridViewIndTrial.Caption = Convert.ToString("");
}
}
在慢页面等待后的错误信息是:
[Win32Exception (0x80004005): The wait operation timed out]
[SqlException (0x80131904): Execution 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, Action`1 wrapCloseInAction) +212
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +81
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +630
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4222
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
System.Data.SqlClient.SqlDataReader.get_MetaData() +89
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +437
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2617
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1636
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +64
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +243
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +37
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) +138
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +134
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +84
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1441
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +286
System.Web.UI.WebControls.ListControl.PerformSelect() +36
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +71
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +93
System.Web.UI.WebControls.BaseDataBoundControl.set_RequiresDataBinding(Boolean value) +104
System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewChanged(Object sender, EventArgs e) +15
System.Web.UI.DataSourceView.OnDataSourceViewChanged(EventArgs e) +99
System.Web.UI.WebControls.SqlDataSourceView.SelectParametersChangedEventHandler(Object o, EventArgs e) +36
System.Web.UI.WebControls.ParameterCollection.OnParametersChanged(EventArgs e) +20
System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control) +143
System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control) +102
System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +37
System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +257
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +579
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +286
System.Web.UI.WebControls.ListControl.PerformSelect() +36
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +71
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +93
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +23
System.Web.UI.Control.PreRenderRecursiveInternal() +166
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4720
解决方案
推荐阅读
- asp.net - 如何从 ASP.NET 中的路由路径中删除页面 URL?
- .net - Entity Framework Core 中的 db.Entry 与 db.update
- javascript - Angular Material Sidenav 只会显示导航列表中的第一项
- html - 禁用下载链接(使用 HTML5 下载属性)直到下载完成
- codeigniter - 我的匹配规则 codeigniter 不适用于我输入的字符串
- java - 如何在 Java 中为 Okttp 编写 Junit Mockito 测试
- dacpac - 使用 AAD MFA 身份验证使用 SqlPackage.exe (DACPAC) 发布的命令是什么?
- vue.js - 点击事件后函数触发,但不更新 VueJS 中的数据变量
- python-3.x - 在 Python 中解析字符串
- sql - 获取最新记录,字段可以为空