SQL timeout on one page of aspx site

2

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
c#
asp.net
sql-server
asked on Stack Overflow Apr 16, 2019 by Maria Amodei

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0