The timeout period has not expired, but still a timeout (see code)?
OK. I keep getting this error after about 3-4 minutes of changing:
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 right, I have set the timeout to 5000 seconds, although it must be something else. You will notice that this is a rather nested loop of procedure calls. I get each Company and then I get each course assigned to each Company, then for each course I get a report of all user activity. There are about 250 companies in a company: 2 to 70 courses per company and 8 to 1000 users per course report per company ... so we are dealing with a lot of data. This final call to the receipt report is a pretty massive stored procedure ...
I'm trying to convert the data to a new form that will make it faster and easier to work with later, but for now I have to parse what we have and publish it in a new way. It's all in one database, but I'm not really sure how I would go about doing it in just SQL. I mainly use the stored procedure used by our reporting tools to get the data to publish to a new table. But I need to run a 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>
a source to share
It looks like the timeout occurs when you run the sql stored procedure (your exception is of type SqlException). You will need to increase the execution timeout of your Sql stored procedure, but I don't think you can do that using the SqlHelper class.
You need to go with the SqlCommand class and set the timeout.
a source to share
There are five different timeouts you should be aware of when talking to the database from the asp.net page:
- Database connection timeout Install on your object
SQLConnection
, possibly via a connection string. - Database Command Timeout Set to your object
SQLCommand
. - ASP.Net Script Timeout Set to your page via
Server.ScriptTimeout
. - Other IIS Timeouts Superimposed on your IIS page. See this link: http://msdn.microsoft.com/en-us/library/ms525386.aspx?ppud=4
- Internet browser timeout The browser / client will only wait that long. You have no control over it, so there is no point in worrying about it, but you should still know that it exists.
Make sure you check the first 4 I've listed.
I usually also say something that 3+ minutes is a way to wait a long time for the page to load. You may have enough data to justify the request time, but if so, there is too much data for the user to really evaluate on one page. Think about it. But in this case, it sounds like you are creating a "report" that you do not need to run often. While I still doubt the merits of such reports (it's just too much data to go through manually, dump it into a fact table or something like that for additional data mining), I understand that companies often want them anyway.
Also, by looking at your code, I can see where you could write that this is all as one big request, not a bunch of small ones. This would be much more efficient for the database at the cost of some additional complexity in assembling your results, but the result will be much faster. But for something that doesn't work often, when you already have stored procedures created this way, you won't be able to justify re-creating things.
So, I'll give you a long page pass ... this time.
a source to share
This is an example of extremely poor code reuse. Instead of reusing a stored process that forces you to loop through all the input variables you want, write a new set based process that selects information based on joins. It will be much faster (assuming you indexed correctly, of course). When accessing databases, you don't want to do anything through a loop or cursor if a set-based alternative is possible.
a source to share