Reportviewer - Timeout Error RRS feed

  • Question

  • I have a report where the report dataset query completes in SSMS within 4 seconds (returns about 600 rows).  However, I'm receiving a "Timeout Expired" error on page load of the report:

    Below is a snippet of my code-behind (showing page load only).  Judging by the error above, the timeout is occurring when trying to fill the "rds" dataset.  As I mentioned above, when I run the query used to populated the "rds" dataset in SSMS, it completes in 4 seconds, so I'm wondering if there's something else I should be looking at besides the query?  

    By the way, I have confirmed the report will display as expected in about a minute if I extend the timeout,  but my goal is for the report display almost immediately upon page load (which doesn't seem unrealistic when considering the run time of the query and the relatively low number of rows returned).  More than anything, I'm just looking for advice on what other factors could be contributing to the timeout error.  Thanks in advance for your help.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
            If Not Page.IsPostBack Then
                Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
                Dim sqlCon As New SqlConnection(strConnection)
                Dim sqlCom As SqlCommand = New SqlCommand("select...", sqlCon)
                sqlCom.CommandType = CommandType.Text
                sqlCom.Parameters.Add("@ID", SqlDbType.Int).Value = 1052
                Dim currentYear As String
                currentYear = sqlCom.ExecuteScalar().ToString()
                Session("ID") = 1052
                Session("UserID") = 3
                Session("SessionID") = 0
                Session("UnitTypeCount") = 1
                Session("CurrentYear") = currentYear
                Dim conn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
                Dim ds As New DataSet()
                Dim da As New SqlDataAdapter("select...", conn)
                da.SelectCommand.Parameters.Add("@ID", SqlDbType.Int).Value = Session("ID").ToString
                da.Fill(ds, "SelectYearParameter")
                DropDownList2.DataSource = ds.Tables(0)
                DropDownList2.DataValueField = "year"
                DropDownList2.DataTextField = "year_formatted"
                Dim defaultValue As String = Session("CurrentYear").ToString()
                DropDownList2.Items.FindByText(defaultValue).Selected = True
                '* Declare objects
                Dim rds As New DataSet()
                Dim rda As New SqlDataAdapter("select...", conn)
                rda.SelectCommand.Parameters.Add("@ID", SqlDbType.Int).Value = Session("ID").ToString
                rda.SelectCommand.Parameters.Add("@SelectYear", SqlDbType.Int).Value = DropDownList2.SelectedValue
                rda.SelectCommand.Parameters.Add("@TotalType", SqlDbType.VarChar).Value = DropDownList5.SelectedValue
                rda.SelectCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = Session("UserID").ToString
                rda.SelectCommand.Parameters.Add("@SessionID", SqlDbType.Int).Value = Session("SessionID").ToString
                rda.SelectCommand.Parameters.Add("@UnitTypeCount", SqlDbType.Int).Value = Session("UnitTypeCount").ToString
                'rda.SelectCommand.CommandTimeout = 300
                rda.Fill(rds, "DataTable1")
                Dim rds1 As New DataSet()
                Dim rda1 As New SqlDataAdapter("select...", conn)
                rda1.SelectCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = Session("UserID").ToString
                rda1.Fill(rds1, "STAFF")
                Dim myRDS As New ReportDataSource("ReportData_DataTable1", rds.Tables("DataTable1"))
                Dim myRDS1 As New ReportDataSource("ReportUser_STAFF", rds1.Tables("STAFF"))
                '* Show report automatically at form load
                Dim ddl2 As String
                Dim ddl5 As String
                If (String.IsNullOrEmpty(DropDownList2.SelectedValue)) Then ddl2 = "" Else ddl2 = DropDownList2.SelectedItem.Text
                If (String.IsNullOrEmpty(DropDownList5.SelectedValue)) Then ddl5 = "" Else ddl5 = DropDownList5.SelectedItem.Text
                Dim _selectYearParameter As New ReportParameter("SelectYear", ddl2)
                Dim _revExpParameter As New ReportParameter("RevExp", ddl5)
                ReportViewer1.LocalReport.SetParameters(New ReportParameter() {_selectYearParameter, _revExpParameter})
            End If
        End Sub

    Thursday, May 3, 2012 4:37 PM