I've just gone live with an web application using aspx pages, C# behind. All sitting on SQL Server. All data delivered with stored procedures. Almost all the procedures are dynamic SQL, with the sql statement being built in the procedure itself based on parameters passed in.
There are three virtually identical pages, the only difference being the parameters passed to the SQL and some labels. The page loads one grid and three drop downs on open. There are two other gridviews but they don't get loaded until more specific information is provided.
With the announcement of the site one of these pages is getting a lot of use. And a lot of time out SQL time out errors. Yet if I move to another of the three pages they are performing fast, no perceptible pause in loading data. And if I run the procedures they are all running in under a second at the same time the page is having time outs. So, I don't think these are actually time outs due to slow SQL Server performance.
I'm a db person, not a coder, who was talked into volunteering time to build this site for a couple clubs. I've been learning on the fly, with many of my answers coming from this site. But in this case I don't even know what I should be looking for. I've provided info below that seems relevant. I'm happy to update with the full .aspx and .cs if helpful.
The page load is simple:
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;
}
}
The site is https://trialpoints.com/ It is the first button "USBCHA Sheep" that is the struggling page, the one everyone is looking at. The second two buttons are almost identical, including using mostly the same data sets, but performing fine.
It feels to me like I've done something incorrect on either the aspx or the C# on the pages so they can't handle multiple requests (at all or at least well). There is only one place that I call the a procedure in the C#, the rest is setup as the aspx data sources. That piece of code is:
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("");
}
}
The error message after waiting on the slow page is:
[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
User contributions licensed under CC BY-SA 3.0