Time out period not elapsed, but still timing out

Question

OK I keep getting this error after about 3-4 minutes of churning:

 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.

Source Error:

Line 93: 
Line 94:             DataSet getData;
Line 95:             getData = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "Course_NewReportGet_Get_Sav", objPara);
Line 96: 
Line 97:             foreach (DataRow dr in getData.Tables[0].Rows)

Here is the code, I think I am not doing something properly, I set the timeout to 5000 seconds though so it must be something else. You'll notice it's quite the nested loop of procedure calls. I am getting every Company, then getting every Course assigned to each Company, then for each course I am getting a report of all the users activity. There is about 250 companies, anywhere from 2-70 courses per comapany, and from 8 to 1000 users per course report, per company... so we're dealing with a lot of data here. That final call to the get report is a pretty massive stored procedure also...

I am trying to transform the data into a new form that will make it faster and easier to work with later but for now I have to parse through what we have and post it in the new way. It is all in the same database but I am not exactly sure how I would do this all in just SQL. Basically am using a stored procedure that is used by our reporting tools to get the data to post to the new table. But I need to run the procedure for each course for each company and then post the data for each user returned in the report from each course from each company... It's huge...

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using Mexico.Data;

public partial class admin_tools_Optimus : System.Web.UI.Page
{
    protected int step = 0;
    protected string[] companies = new string[260];
    protected string[] coursestrings = new string[260];
    protected int total = 0;
    protected int[] totalcourses = new int[260];

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Proceed(object sender, EventArgs e)
    {
        DataSet getCompanies = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "Companies_All_Get");

        int counter = 0;

        foreach (DataRow dr in getCompanies.Tables[0].Rows)
        {
            lstData.Items.Add(dr["companyid"].ToString() + ": " + dr["companyname"].ToString());
            companies[counter] = dr["companyid"].ToString();
            counter++;
        }
        lblCurrentData.Text = counter.ToString() + " companies ready, click next to get all company courses.";
        total = counter;

        GetCompanies();
    }


    protected void GetCompanies()
    {
        string[,] courses = new string[260, 200];

        for (int i = 0; i < total-1; i++)
        {
            DataSet getBundles = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "CompanyCourses_ByCompanyID_Get_Sav", new SqlParameter("@companyid", companies[i]));

            int counter = 0;

            foreach (DataRow dr in getBundles.Tables[0].Rows)
            {
                courses[i, counter] = dr["bundleid"].ToString();
                counter++;
            }

            string allID = "";

            allID += courses[i, 0];

            for (int ii = 0; ii < counter; ii++)
            {
                allID += "," + courses[i, ii];
            }
            Response.Write(allID + " <br/>");
            coursestrings[i] = allID;
            totalcourses[i] = counter;
        }
        GetUsers();
    }

    protected void GetUsers()
    {
        for (int i = 0; i < total - 1; i++)
        {
            SqlParameter[] objPara = new SqlParameter[10];
            objPara[0] = new SqlParameter("@CompanyID", companies[i]);
            objPara[1] = new SqlParameter("@CourseID", coursestrings[i]);
            objPara[2] = new SqlParameter("@DateRangeType", 1);
            //objPara[3] = new SqlParameter("@StartDate", startDate);
            //objPara[4] = new SqlParameter("@EndDate", System.DateTime.Now.ToString("MM/dd/yyyy"));
            objPara[5] = new SqlParameter("@UserName", "");
            objPara[6] = new SqlParameter("@StartIndex", 1);
            objPara[7] = new SqlParameter("@MaximumRows", 100000);



            DataSet getData;
            getData = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "Course_NewReportGet_Get_Sav", objPara);

            foreach (DataRow dr in getData.Tables[0].Rows)
            {
                Response.Write("user: " + dr["userid"].ToString() + " / course: " + dr["bundleid"].ToString() + " - progress: " + dr["viewed"].ToString() + " - scored: " + dr["scored"].ToString() + "<br/><br/>");
            }
        }
    }
}

PAGE CODE:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Optimus.aspx.cs" Inherits="admin_tools_Optimus" Debug="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCurrentData" runat="server" Text="Click next to get all Companies"/><br />
        <asp:Button ID="btnNext" runat="server" Text="Next" OnClick="Proceed" />
        <br/>
        <asp:ListBox ID="lstData" runat="server" height="300" Width="300" />
    </div>
    </form>
</body>
</html>

Answer 1

There are five different timeouts you have to know about when talking to a database from an asp.net page:

1. The database connection timeout Set on your SQLConnection object, possibly via the connection string.
2. The database command timeout Set on your SQLCommand object.
3. The ASP.Net Script Timeout Set on your page via Server.ScriptTimeout.
4. Other IIS timeouts Imposed on your page by IIS. See this link: http://msdn.microsoft.com/en-us/library/ms525386.aspx?ppud=4
5. The internet browser timeout The browser/client will only wait so long. You don't control this so there's no point worrying about it, but you should still know it exists.

Make sure you check the first 4 I listed.

Normally I'd also say something to the effect that 3+ minutes is way to long to wait for a page to load. You might have enough data to justify the query time, but if so that's too much data for a user to really evaluate in one page. Consider breaking it up. But in this case it sounds like you're building a 'report' that needs to be run infrequently. While I still question the merits of such reports (it's just too much data to go through by hand — dump it into a fact table or somewhere similar for additional data mining instead), I understand that businesses often want them anyway.

Also, looking at your code I can see where you might be able to write that all as one big query instead of a bunch of little ones. That would be a lot more efficient for the database at the expense of some additionally complexity in assembling your results, but the result will run much faster. But for something that doesn't run often, when you already have the stored procedures built to do it this way, you may not be able to justify re-writing things.

So I'll give you a pass on the long-running page... this time.

Answer 2

It looks like the timeout is happening when you're running the sql stored procedure (Your exception is of type SqlException). You would need to increase the Timeout of your Sql Stored Procedure execution, but I don't think you can do that with the SqlHelper class.

You'll need to go with the SqlCommand class, and set the timeout there.

Answer 3

This is an example of an extremely bad re-use of code. Instead of reusing the stored proc which makes you loop through all input variables you want, write a new set-based proc that selects the information based on joins. It will be much faster (assuming you have properly indexed of course). In accessing databases, you do not want to do something through a loop or a cursor if a set-based alternative is possible.