Time out period not elapsed, but still timing out (see code)?

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"&gt;

<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<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

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 2

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.