[Solved] Performance Slow with Hierarchy

[David Kucharski]

I have a RadGrid in my form that has a MasterViewTable and 1 GridTableView. In my MasterViewTable I will have a total of 1643 records. Out of those parent records, only 160 have child records. Some have 1, Some have a few. There are pages where all the parent records on that page do not have any Children. You will see below my RadGrid declaration all of the functions I am using. The performance is very slow. About 15 to 20 seconds between pagination or sorting. Is there something I am doing wrong or is there something I need to do differently? I have looked at all of the "tuning articles" and believe I am doing everything correctly. I cannot release this grid with that type of delay. One more thing, I am only showing 50 parent rows on every page. Thank you in advance. I call the autoRadGrid.DataBind() function when the user clicks on a drop-down on the screen.

<telerik:RadGrid ID="autoRadGrid" runat="server" Width="1500" Height="700" AllowPaging="true" PagerStyle-Mode="NextPrevAndNumeric"
  AutoGenerateColumns="false" ShowStatusBar="true" AllowSorting="true" AllowFilteringByColumn="true"
  Skin="WebBlue">
    <MasterTableView PageSize="50" AllowMultiColumnSorting="true" DataKeyNames="DetailID, Day" HierarchyLoadMode="ServerOnDemand" HierarchyDefaultExpanded="true" >
        <DetailTables>
            <telerik:GridTableView Name="SubCodes" Width="100%">
                <ParentTableRelation>
                    <telerik:GridRelationFields DetailKeyField="DetailID" MasterKeyField="DetailID" />
                    <telerik:GridRelationFields DetailKeyField="Day" MasterKeyField="Day" />
                </ParentTableRelation>
                <Columns>
                    <telerik:GridBoundColumn DataField="Sub_Code" HeaderText="Sub Code" UniqueName="Sub_Code" FilterControlWidth="60" HeaderStyle-Width="80"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="HouseHold_Count" HeaderText="Household" UniqueName="HouseHold_Count" FilterControlWidth="60" HeaderStyle-Width="80"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="CD1" UniqueName="CD1" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="CD2" UniqueName="CD2" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="CD3" UniqueName="CD3" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Demographic1" UniqueName="Demographic1" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Demographic2" UniqueName="Demographic2" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Demographic3" UniqueName="Demographic3" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Demographic4" UniqueName="Demographic4" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Demographic5" UniqueName="Demographic5" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn UniqueName="Selected" HeaderText="Selected" SortExpression="Selected" FilterControlWidth="60" HeaderStyle-Width="60">
                        <ItemTemplate>
                            <asp:CheckBox ID="selected" Checked='<%# Bind("Selected") %>' AutoPostBack="false" runat="server" />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridTemplateColumn UniqueName="Forced" HeaderText="Forced" SortExpression="Forced" FilterControlWidth="60" HeaderStyle-Width="60">
                        <ItemTemplate>
                            <asp:CheckBox ID="forced" Checked='<%# Bind("Forced") %>' AutoPostBack="false" runat="server" />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridTemplateColumn UniqueName="Circ" HeaderText="Circ" SortExpression="Circ" FilterControlWidth="60" HeaderStyle-Width="70">
                        <ItemTemplate>
                            <asp:TextBox ID="circ" Text='<%# Bind("Circ") %>' AutoPostBack="false" runat="server" />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridBoundColumn DataField="Coverage" HeaderText="Coverage" UniqueName="Coverage" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
                </Columns>
            </telerik:GridTableView>
        </DetailTables>
        <Columns>
            <telerik:GridBoundColumn DataField="Geography" HeaderText="Geography" UniqueName="Geography" FilterControlWidth="60" HeaderStyle-Width="80"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CirculationTypeDescription" HeaderText="Circ Type" UniqueName="CirculationTypeDescription" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="HouseHold_Count" HeaderText="Household" UniqueName="HouseHold_Count" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="TotalCoverage" HeaderText="Total Coverage" UniqueName="TotalCoverage" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="LocationMarket" HeaderText="LocationMarket" UniqueName="LocationMarket" FilterControlWidth="60" HeaderStyle-Width="95"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="LocationNumber" HeaderText="Location Number" UniqueName="LocationNumber" FilterControlWidth="60" HeaderStyle-Width="75"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="LocationName" HeaderText="Location Name" UniqueName="LocationName" FilterControlWidth="60" HeaderStyle-Width="110"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Distance" HeaderText="Distance" UniqueName="Distance" FilterControlWidth="60" HeaderStyle-Width="75"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Sales" HeaderText="Sales" UniqueName="Sales" FilterControlWidth="60" HeaderStyle-Width="50"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="TotalSales" HeaderText="Total Sales" UniqueName="TotalSales" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="BOS" HeaderText="BOS" UniqueName="BOS" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CumeBOS" HeaderText="Cume BOS" UniqueName="CumeBOS" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="SPH" HeaderText="SPH" UniqueName="SPH" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="SPHIndex" HeaderText="SPH Index" UniqueName="SPHIndex" FilterControlWidth="60" HeaderStyle-Width="80"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CD1" UniqueName="CD1" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CD2" UniqueName="CD2" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="CD3" UniqueName="CD3" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Demographic1" UniqueName="Demographic1" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Demographic2" UniqueName="Demographic2" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Demographic3" UniqueName="Demographic3" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Demographic4" UniqueName="Demographic4" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Demographic5" UniqueName="Demographic5" FilterControlWidth="60" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Day" HeaderText="Day" UniqueName="Day" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridTemplateColumn UniqueName="Selected" HeaderText="Selected" SortExpression="Selected" FilterControlWidth="60" HeaderStyle-Width="60">
                <ItemTemplate>
                    <asp:CheckBox ID="selected" Checked='<%# Bind("Selected") %>' AutoPostBack="false" runat="server" />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn UniqueName="Forced" HeaderText="Forced" SortExpression="Forced" FilterControlWidth="60" HeaderStyle-Width="60">
                <ItemTemplate>
                    <asp:CheckBox ID="forced" Checked='<%# Bind("Forced") %>' AutoPostBack="false" runat="server" />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn UniqueName="Circ" HeaderText="Circ" SortExpression="Circ" FilterControlWidth="60" HeaderStyle-Width="70">
                <ItemTemplate>
                    <asp:TextBox ID="circ" Text='<%# Bind("Circ") %>' AutoPostBack="false" runat="server" />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridBoundColumn DataField="Coverage" HeaderText="Coverage" UniqueName="Coverage" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Zones" HeaderText="Zones" UniqueName="Zones" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Version" HeaderText="Version" UniqueName="Version" FilterControlWidth="60" HeaderStyle-Width="90"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="SubVersionCategory" HeaderText="Sub-Version Category" UniqueName="SubVersionCategory" FilterControlWidth="90" HeaderStyle-Width="70"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="DeliveryCode" HeaderText="Delivery Code" UniqueName="DeliveryCode" FilterControlWidth="90" HeaderStyle-Width="70"></telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>

    <ClientSettings AllowColumnsReorder="true" ColumnsReorderMethod="Swap" >
        <ClientEvents OnGridCreated="GetGridObject" />
        <Scrolling AllowScroll="true" FrozenColumnsCount="2" UseStaticHeaders="true" SaveScrollPosition="true" />
        <Selecting AllowRowSelect="true" />
        <Resizing AllowColumnResize="true" />
    </ClientSettings>
</telerik:RadGrid>

NeedDataSource

Private Sub autoRadGrid_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles autoRadGrid.NeedDataSource
    If mintCircSetID > 0 Then
        Dim intIndex As Integer = 0
        Dim strSortedColumn As String = ""
        Dim strSortClause As String = ""
        mstrSQL = ""
        mstrSQL = "EXEC WB_sp_RetrieveGeographySelection_ForTelerik '" & mstrSessionID & "'," & mintEventID
        If mblnFullRunUtilizedInd Or cbSingleZip.Checked Then
            mstrSQL = mstrSQL & ",0"
        Else
            mstrSQL = mstrSQL & ",1"
        End If
        If cbSundayFR.Checked Or cbSundayHD.Checked Or cbSundaySC.Checked Or cbSundayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbMondayFR.Checked Or cbMondayHD.Checked Or cbMondaySC.Checked Or cbMondayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbTuesdayFR.Checked Or cbTuesdayHD.Checked Or cbTuesdaySC.Checked Or cbTuesdayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbWednesdayFR.Checked Or cbWednesdayHD.Checked Or cbWednesdaySC.Checked Or cbWednesdayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbThursdayFR.Checked Or cbThursdayHD.Checked Or cbThursdaySC.Checked Or cbThursdayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbFridayFR.Checked Or cbFridayHD.Checked Or cbFridaySC.Checked Or cbFridayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        If cbSaturdayFR.Checked Or cbSaturdayHD.Checked Or cbSaturdaySC.Checked Or cbSaturdayMX.Checked Then
            mstrSQL = mstrSQL & ",1"
        Else
            mstrSQL = mstrSQL & ",0"
        End If
        mstrSQL = mstrSQL & "," & mintParentMap
        If cbSundayFR.Checked Or cbMondayFR.Checked Or cbTuesdayFR.Checked Or cbWednesdayFR.Checked Or cbThursdayFR.Checked Or cbFridayFR.Checked Or cbSaturdayFR.Checked Then
            mstrSQL = mstrSQL & ",1,1,1"
        Else
            If cbSundayHD.Checked Or cbMondayHD.Checked Or cbTuesdayHD.Checked Or cbWednesdayHD.Checked Or cbThursdayHD.Checked Or cbFridayHD.Checked Or cbSaturdayHD.Checked Then
                mstrSQL = mstrSQL & ",1"
            Else
                mstrSQL = mstrSQL & ",0"
            End If
            If cbSundayMX.Checked Or cbMondayMX.Checked Or cbTuesdayMX.Checked Or cbWednesdayMX.Checked Or cbThursdayMX.Checked Or cbFridayMX.Checked Or cbSaturdayMX.Checked Then
                mstrSQL = mstrSQL & ",1"
            Else
                mstrSQL = mstrSQL & ",0"
            End If
            If cbSundaySC.Checked Or cbMondaySC.Checked Or cbTuesdaySC.Checked Or cbWednesdaySC.Checked Or cbThursdaySC.Checked Or cbFridaySC.Checked Or cbSaturdaySC.Checked Then
                mstrSQL = mstrSQL & ",1"
            Else
                mstrSQL = mstrSQL & ",0"
            End If
        End If
        intIndex = UBound(Split(hidSortInformation.Value, ","))
        Do While intIndex >= 0
            strSortedColumn = Trim(Split(hidSortInformation.Value, ",")(intIndex))
            strSortClause = strSortClause & strSortedColumn & ","
            intIndex -= 1
        Loop
        strSortClause = Left(strSortClause, Len(strSortClause) - 1)
        mstrSQL = mstrSQL & ",'" & strSortClause & "',"
        If cbSortingByParent.Checked Then
            mstrSQL = mstrSQL & "1"
        Else
            mstrSQL = mstrSQL & "0"
        End If
        mstrSQL = mstrSQL & ",''"
        If Not e.IsFromDetailTable Then
            autoRadGrid.DataSource = GetDataTable(mstrSQL & ",0,0,''")
        End If
    End If
End Sub

ItemDataBound

Private Sub autoRadGrid_ItemDataBound(sender As Object, e As Telerik.Web.UI.GridItemEventArgs) Handles autoRadGrid.ItemDataBound
    If (TypeOf (e.Item) Is GridDataItem) And e.Item.OwnerTableView.Name <> "SubCodes" Then
        Dim dataBoundItem As GridDataItem = e.Item
        Dim dataRow As DataRowView = dataBoundItem.DataItem
        Dim index As Integer = dataBoundItem.ItemIndex
        dataBoundItem("Geography").Text = dataBoundItem("Geography").Text.Trim
        dataBoundItem("Zones").Text = dataBoundItem("Zones").Text.Trim
        If dataBoundItem("TotalCoverage").Text > 0 And UCase(Request("TargetGeo")) <> "TRUE" Then
            dataBoundItem("TotalCoverage").Attributes.Add("onClick", "Javascript:fnOpenGeoInfoView('" & dataBoundItem("Geography").Text.ToString.Trim & "'," & mintEventID & "," & ddlCircSet.SelectedItem.Value & ")")
            dataBoundItem("TotalCoverage").CssClass = "GridLink"
        End If
        Dim strSubCode As String = ""
        Dim mode As Integer = 0
        Dim day As Integer = 0
        Dim circType As Integer = 0
        If dataBoundItem("Zones").Text.Trim = "" Or dataBoundItem("Zones").Text.Trim = " " Then
            mode = 0
        ElseIf InStr(dataBoundItem("Zones").Text.Trim, "NPP") > 0 Or InStr(dataBoundItem("Zones").Text.Trim, "NOPP") > 0 Then
            mode = 1
        Else
            mode = 2
        End If
        If dataBoundItem("Day").Text.Trim.ToUpper = "SUNDAY" Then
            day = 1
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "MONDAY" Then
            day = 2
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "TUESDAY" Then
            day = 3
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "WEDNESDAY" Then
            day = 4
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "THURSDAY" Then
            day = 5
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "FRIDAY" Then
            day = 6
        ElseIf dataBoundItem("Day").Text.Trim.ToUpper = "SATURDAY" Then
            day = 7
        End If
        If dataBoundItem("CirculationTypeDescription").Text.Trim.ToUpper = "HOME DELIVERED" Then
            circType = 1
        ElseIf dataBoundItem("CirculationTypeDescription").Text.Trim.ToUpper = "SINGLE COPY" Then
            circType = 3
        ElseIf dataBoundItem("CirculationTypeDescription").Text.Trim.ToUpper = "MIXED" Then
            circType = 2
        End If
        dataBoundItem("Selected").Attributes.Add("onClick", "javascript:return fnSelectDayForTelerik(" & index & "," & mode & "," & mintEventID & "," & circType & "," & day & "," & ddlCircSet.SelectedItem.Value & ",'" & mstrSessionID & "'," & Request("PARID") & ",'" & Trim(UCase(Request("TargetGeo"))) & "',0);")
        dataBoundItem("Forced").Attributes.Add("onClick", "javascript:return fnSelectDayForTelerik(" & index & "," & mode & "," & mintEventID & "," & circType & "," & day & "," & ddlCircSet.SelectedItem.Value & ",'" & mstrSessionID & "'," & Request("PARID") & ",'" & Trim(UCase(Request("TargetGeo"))) & "',1);")
        Dim textBox1 As TextBox
        textBox1 = dataBoundItem("Circ").FindControl("Circ")
        textBox1.Attributes.Add("onChange", "javascript:ValidateCirculationForTelerik(" & index & "," & mintEventID & "," & circType & "," & day & "," & ddlCircSet.SelectedItem.Value & ",'" & mstrSessionID & "'," & Request("PARID") & ",''," & textBox1.Text & ",this);")
        Dim strZones As String = "No"
        If mblnFullRunUtilizedInd Or cbSingleZip.Checked Then
            strZones = "No"
        Else
            If dataBoundItem("Zones").Text.Trim = "" Or dataBoundItem("Zones").Text.Trim = " " Then
                strZones = "No"
            ElseIf InStr(dataBoundItem("Zones").Text.Trim, "NPP") > 0 Or InStr(dataBoundItem("Zones").Text.Trim, "NOPP") > 0 Then
                strZones = "No"
            Else
                strZones = "Yes"
            End If
        End If
        If UcHeader.objSession.ApplicationId <> CInt(ConfigurationManager.AppSettings("MediaviewerAppID")) Then
            If miVersionsCategoryID > 0 Then
                dataBoundItem("Version").Attributes.Add("onClick", "javascript:fnVersionOverrideForTelerik(" & index & ",'" & strZones & "'," & mintEventID & "," & circType & "," & day & ");")
                dataBoundItem("Version").CssClass = "GridLink"
            End If
        End If
        If UcHeader.objSession.ApplicationId <> CInt(ConfigurationManager.AppSettings("MediaviewerAppID")) Then
            If miDeliveryCodeCount > 0 And ddlDeliveryCodeCategory.SelectedIndex > 0 Then
                dataBoundItem("DeliveryCode").Attributes.Add("onClick", "javascript:fnDeliveryCodeOverrideForTelerik(" & index & ",'" & strZones & "'," & mintEventID & "," & circType & "," & day & "," & ddlDeliveryCodeCategory.SelectedValue & ");")
                dataBoundItem("DeliveryCode").CssClass = "GridLink"
            Else
                dataBoundItem("DeliveryCode").Text = ""
            End If
        End If
    End If
End Sub

ItemCreated

Private Sub autoRadGrid_ItemCreated(sender As Object, e As Telerik.Web.UI.GridItemEventArgs) Handles autoRadGrid.ItemCreated
    If (TypeOf (e.Item) Is GridNoRecordsItem) Then
        e.Item.OwnerTableView.Visible = False
    End If
End Sub

PreRenderer

Private Sub autoRadGrid_PreRender(sender As Object, e As System.EventArgs) Handles autoRadGrid.PreRender
    HideExpandColumnRecursive(autoRadGrid.MasterTableView)
End Sub

DetailTableDataBind

Private Sub autoRadGrid_DetailTableDataBind(sender As Object, e As Telerik.Web.UI.GridDetailTableDataBindEventArgs) Handles autoRadGrid.DetailTableDataBind

     Dim dataItem As GridDataItem = CType(e.DetailTableView.ParentItem, GridDataItem)

     Select Case e.DetailTableView.Name

         Case "SubCodes"

             Dim detailID As Integer = dataItem.GetDataKeyValue("DetailID").ToString()  

             Dim Day As String = dataItem.GetDataKeyValue("Day").ToString()

             If mintCircSetID > 0 Then 

                 e.DetailTableView.DataSource = GetDataTable(mstrSQL & ",1," & detailID & ",'" & Day & "'")

             End If

     End Select

 End


HideExpandColumnRecursive

Public Sub HideExpandColumnRecursive(ByVal tableView As GridTableView)
    Dim nestedViewItems As GridItem() = tableView.GetItems(GridItemType.NestedView)
    For Each nestedViewItem As GridNestedViewItem In nestedViewItems
        For Each nestedView As GridTableView In nestedViewItem.NestedTableViews
            If nestedView.Items.Count = 0 Then
                Dim cell As TableCell = nestedView.ParentItem("ExpandColumn")
                cell.Controls(0).Visible = False
                nestedViewItem.Visible = False
            End If
            If nestedView.HasDetailTables Then
                HideExpandColumnRecursive(nestedView)
            End If
        Next
    Next
End Sub

GetDataTable

Public Function GetDataTable(ByVal query As String) As DataTable
    Dim ConnString As String = ConfigurationManager.ConnectionStrings("iAnalyzeConnectionString").ConnectionString
    Dim conn As SqlConnection = New SqlConnection(ConnString)
    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    adapter.SelectCommand = New SqlCommand(query, conn)
    Dim table1 As New DataTable
    conn.Open()
    Try
        adapter.Fill(table1)
    Finally
        conn.Close()
    End Try
    Return table1
End Function

[Vasil]

Hi,

You need to implement Custom Paging to improve significantly the performance. Currently in your approach, all records are send from your database to your grid, on each binding, which is probably what is causing the biggest delay and consumes more resources than when sending only 50 items.
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/custompaging/defaultcs.aspx
http://www.telerik.com/help/aspnet-ajax/grid-custom-paging.html

Regards,
Vasil
the Telerik team

[David]

Do you have an example of the custom paging that involves an actual call to a database to retrieve information instead of a randomly generated object based on fixed arrays? I am having a had time wrapping my head around the concept.

In my NeedDataSource object, I changed the following code from

If Not e.IsFromDetailTable And daySelected Then
    RadGrid1.DataSource = GetDataTable(mstrSQL & ",0,0,''")
End If

to

If Not e.IsFromDetailTable And daySelected Then
    Dim MyBusinessObjectCollection1 As New MyBusinessObjectCollection(mstrSQL & ",0,0,''")
    Dim startRowIndex As Integer = IIf((ShouldApplySortFilterOrGroup()), 0, RadGrid1.CurrentPageIndex * RadGrid1.PageSize)
    Dim maximumRows As Integer = IIf((ShouldApplySortFilterOrGroup()), MyBusinessObjectCollection1.SelectCount(), RadGrid1.PageSize)
    RadGrid1.AllowCustomPaging = Not ShouldApplySortFilterOrGroup()
    RadGrid1.DataSource = MyBusinessObjectCollection1.[Select](startRowIndex, maximumRows)
End If

So when I create my instance of MyBusinessObjectCollection, I will need to fill in a DataReader with my query. At this point I get my MaxItems

Public Sub New(ByVal sql As String)
    Try
        mobjSqlDataReader = mobjDatabase.fnRetrieveData("DATAREADER", sql)
        Do While mobjSqlDataReader.Read
            _maxItems += 1
        Loop
        mobjSqlDataReader.Close()
    Catch ex As SqlException
        Throw New Exception(ex.Message & ",SQL:" & sql & ",frmGeographySelection.subLoadGroupTypes")
    End Try
End Sub

I also have the mobjSqlDataReader filled with the information from the database. At this point it looks like I can call the [Select] function with the startRowIndex and maximumRows parameters to build my MyBusinessObject. I just don't know what to do with my sqldatareader inside the while loop.

Public Function [Select](ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As List(Of MyBusinessObject)
        Dm list As New List(Of MyBusinessObject)()
    Dim i As Integer = startRowIndex
    While i < Math.Min(_maxItems, startRowIndex + maximumRows)
        Dim details As New MyBusinessObject()
        details.day = mobjSqlDataReader("Day") 'but where is this being read from and do I have to put this in a while loop also?
        list.Add(details)
        i += 1
    End While
    Return list
End Function

[Vasil]

Hello David,

We don't have such example, but if you want to use your SQL DataSource, you can just modify your query a bit further, to skip the first (CurrentPage-1)*PageSize elements and to get only PageSize elements. This is a bit more general problem, not directly related to our controls, but see this forum for some possible solutions:
http://stackoverflow.com/questions/187998/row-offset-in-sql-server

All the best,
Vasil
the Telerik team

[David Kucharski]

Vasil,

I don't know if what you offered in the last post would help me. If I understand the logic of custom paging, using the NeedDataSource, you retrieve your startingRowIndex based on the grid, get the Maximum Size. You will then select, out of your MyBusinessObjectCollection, the records that should be shown next.

So within the MyBusinessObjectCollection, in the New construct, I would query the database for the full amount of records that could be shown in the radgrid, without sorting/filtering applied.

upon calling the select method of MyBusinessObjectCollection, with startrowindex and maximumrows, I need to now apply the filtering/sorting upon the collection of records retrieved from the database. In the example about Custom Paging, I see the 2 classes used for sorting and filterering. When are these invoked to apply to the full collection from the database to get my collection to return to the radgrid in the right order?

[David Kucharski]

Vasil,

I think I am close but still have issues. I got my MyBusinessObjectCollection to tap into my datasource, and set up a datatable. that way I can see the row count. In my Select, I set up a new datatable and add the correct number of records back into the new datatable. In my case it is 50 records out of 1643.

The grid displays with the first 50 and pagenumber is correct.

Here is the problem, when I just go to the next page, the grid stays on the 1st page. RadGrid1.CurrentPageIndex or RadGrid1.MasterTableView.CurrentPageIndex are both 0. They have not updated. Also, when I apply a filter, the filter expression is still blank so nothing get applied. For information, I am creating the radgrid in the Page_Init and placing it in a Placeholder so I don't know if that has anything to do with this since the example is with a radgrid on the aspx page itself.

Here is what I did.

In the NeedDataSource

Dim MyBusinessObjectCollection1 As New MyBusinessObjectCollection(mstrSQL & ",0,0,''", rlbDistributionPatterns.Items.Count)
Dim startRowIndex As Integer = IIf((ShouldApplySortFilterOrGroup()), 0, RadGrid1.CurrentPageIndex * RadGrid1.MasterTableView.PageSize)
Dim maximumRows As Integer = IIf((ShouldApplySortFilterOrGroup()), MyBusinessObjectCollection1.SelectCount(), RadGrid1.MasterTableView.PageSize)
RadGrid1.AllowCustomPaging = Not ShouldApplySortFilterOrGroup()
Dim nDS As New DataSet
nDS = MyBusinessObjectCollection1.[Select](startRowIndex, maximumRows)
RadGrid1.VirtualItemCount = MyBusinessObjectCollection1.SelectCount
RadGrid1.DataSource = nDS.Tables("Geographies")

MyBusinessObjectCollection

Public Class MyBusinessObjectCollection
    Dim _maxItems As Integer = 0
    Dim _numberOfDPs As Integer = 0
    Dim mobjDatabase As New NSAPublicClasses_2010.Database()
    Dim mobjSqlDataReader As SqlDataReader
    Dim mobjDataSet As New DataSet()
    Public Sub New(ByVal sql As String, ByVal numberOfDPs As Integer)
        Try
            mobjDatabase.ConnectionString = ConfigurationManager.ConnectionStrings("iAnalyzeConnectionString").ConnectionString
            mobjDatabase.subOpenConnection()
            mobjDataSet = mobjDatabase.fnRetrieveData("DATASET", sql, "Details")
            _maxItems = mobjDataSet.Tables("Details").Rows.Count
            _numberOfDPs = numberOfDPs
        Catch ex As SqlException
            Throw New Exception(ex.Message & ",SQL:" & sql & ",frmGeographySelection.subLoadGroupTypes")
        End Try

    End Sub

    Public Function SelectCount() As Integer
        Return _maxItems
    End Function

    Public Function [Select](ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As DataSet
        Dim Random1 As New Random()

        Dim list As New List(Of MyBusinessObject)()

        Dim newDS As DataSet = New DataSet("Details")

        Dim geographiesTable As DataTable = newDS.Tables.Add("Geographies")
        geographiesTable.Columns.Add("Day", Type.GetType("System.String"))
        geographiesTable.Columns.Add("Selected", Type.GetType("System.Boolean"))
        geographiesTable.Columns.Add("Circ", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("Forced", Type.GetType("System.Boolean"))
        geographiesTable.Columns.Add("Coverage", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Version", Type.GetType("System.String"))
        geographiesTable.Columns.Add("SubVersionCategory", Type.GetType("System.String"))
        geographiesTable.Columns.Add("DeliveryCode", Type.GetType("System.String"))
        geographiesTable.Columns.Add("Zones", Type.GetType("System.String"))
        geographiesTable.Columns.Add("ParentChildInd", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("Geography", Type.GetType("System.String"))
        geographiesTable.Columns.Add("CirculationTypeDescription", Type.GetType("System.String"))
        geographiesTable.Columns.Add("Sub_Code", Type.GetType("System.String"))
        geographiesTable.Columns.Add("HouseHold_Count", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("TotalCoverage", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("LocationMarket", Type.GetType("System.String"))
        geographiesTable.Columns.Add("LocationNumber", Type.GetType("System.String"))
        geographiesTable.Columns.Add("LocationName", Type.GetType("System.String"))
        geographiesTable.Columns.Add("Distance", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Sales", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("TotalSales", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("BOS", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("CumeBOS", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("SPH", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("SPHIndex", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("VendorListID", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("DetailID", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("CD1", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("CD2", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("CD3", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Demographic1", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Demographic2", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Demographic3", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Demographic4", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("Demographic5", Type.GetType("System.Decimal"))
        geographiesTable.Columns.Add("CirculationTypeID", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("HomeDeliveredTotal", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("SingleCopyTotal", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("MixedTotal", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("HouseholdTotal", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("FoundGeographies", Type.GetType("System.Int32"))
        geographiesTable.Columns.Add("SubZipCount", Type.GetType("System.Int32"))

        For k As Integer = 1 To _numberOfDPs Step 1
            geographiesTable.Columns.Add("DP" & k, Type.GetType("System.Boolean"))
            geographiesTable.Columns.Add("CoverageDP" & k, Type.GetType("System.Decimal"))
        Next


        Dim i As Integer = startRowIndex
        While i < Math.Min(_maxItems, startRowIndex + maximumrows)
            'Dim details As New MyBusinessObject()
            Dim newRow As DataRow = newDS.Tables("Geographies").NewRow()

            newRow("Day") = mobjDataSet.Tables("Details").Rows(i)("Day")
            newRow("Selected") = mobjDataSet.Tables("Details").Rows(i)("Selected")
            newRow("Circ") = mobjDataSet.Tables("Details").Rows(i)("Circ")
            newRow("Forced") = mobjDataSet.Tables("Details").Rows(i)("Forced")
            newRow("Coverage") = mobjDataSet.Tables("Details").Rows(i)("Coverage")
            newRow("Version") = mobjDataSet.Tables("Details").Rows(i)("Version")
            newRow("SubVersionCategory") = mobjDataSet.Tables("Details").Rows(i)("SubVersionCategory")
            newRow("DeliveryCode") = mobjDataSet.Tables("Details").Rows(i)("DeliveryCode")
            newRow("Zones") = mobjDataSet.Tables("Details").Rows(i)("Zones")
            newRow("ParentChildInd") = mobjDataSet.Tables("Details").Rows(i)("ParentChildInd")
            newRow("Geography") = mobjDataSet.Tables("Details").Rows(i)("Geography")
            newRow("CirculationTypeDescription") = mobjDataSet.Tables("Details").Rows(i)("CirculationTypeDescription")
            newRow("Sub_Code") = mobjDataSet.Tables("Details").Rows(i)("Sub_Code")
            newRow("HouseHold_Count") = mobjDataSet.Tables("Details").Rows(i)("HouseHold_Count")
            newRow("TotalCoverage") = mobjDataSet.Tables("Details").Rows(i)("TotalCoverage")
            newRow("LocationMarket") = mobjDataSet.Tables("Details").Rows(i)("LocationMarket")
            newRow("LocationNumber") = mobjDataSet.Tables("Details").Rows(i)("LocationNumber")
            newRow("LocationName") = mobjDataSet.Tables("Details").Rows(i)("LocationName")
            newRow("Distance") = mobjDataSet.Tables("Details").Rows(i)("Distance")
            newRow("Sales") = mobjDataSet.Tables("Details").Rows(i)("Sales")
            newRow("TotalSales") = mobjDataSet.Tables("Details").Rows(i)("TotalSales")
            newRow("BOS") = mobjDataSet.Tables("Details").Rows(i)("BOS")
            newRow("CumeBOS") = mobjDataSet.Tables("Details").Rows(i)("CumeBOS")
            newRow("SPH") = mobjDataSet.Tables("Details").Rows(i)("SPH")
            newRow("SPHIndex") = mobjDataSet.Tables("Details").Rows(i)("SPHIndex")
            newRow("VendorListID") = mobjDataSet.Tables("Details").Rows(i)("VendorListID")
            newRow("DetailID") = mobjDataSet.Tables("Details").Rows(i)("DetailID")
            newRow("CD1") = mobjDataSet.Tables("Details").Rows(i)("CD1")
            newRow("CD2") = mobjDataSet.Tables("Details").Rows(i)("CD2")
            newRow("CD3") = mobjDataSet.Tables("Details").Rows(i)("CD3")
            newRow("Demographic1") = mobjDataSet.Tables("Details").Rows(i)("Demographic1")
            newRow("Demographic2") = mobjDataSet.Tables("Details").Rows(i)("Demographic2")
            newRow("Demographic3") = mobjDataSet.Tables("Details").Rows(i)("Demographic3")
            newRow("Demographic4") = mobjDataSet.Tables("Details").Rows(i)("Demographic4")
            newRow("Demographic5") = mobjDataSet.Tables("Details").Rows(i)("Demographic5")
            newRow("CirculationTypeID") = mobjDataSet.Tables("Details").Rows(i)("CirculationTypeID")
            newRow("HomeDeliveredTotal") = mobjDataSet.Tables("Details").Rows(i)("HomeDeliveredTotal")
            newRow("SingleCopyTotal") = mobjDataSet.Tables("Details").Rows(i)("SingleCopyTotal")
            newRow("MixedTotal") = mobjDataSet.Tables("Details").Rows(i)("MixedTotal")
            newRow("HouseholdTotal") = mobjDataSet.Tables("Details").Rows(i)("HouseholdTotal")
            If IsDBNull(mobjDataSet.Tables("Details").Rows(i)("FoundGeographies")) Then
                newRow("FoundGeographies") = 0
            Else
                newRow("FoundGeographies") = mobjDataSet.Tables("Details").Rows(i)("FoundGeographies")
            End If

            newRow("SubZipCount") = mobjDataSet.Tables("Details").Rows(i)("SubZipCount")

            newDS.Tables("Geographies").Rows.Add(newRow)

            For j As Integer = 1 To _numberOfDPs Step 1
                newRow("DP" & j) = mobjDataSet.Tables("Details").Rows(i)("DP" & j)
                newRow("CoverageDP" & j) = mobjDataSet.Tables("Details").Rows(i)("DP" & j)
            Next

            i += 1
        End While
        Return newDS
    End Function

page_init

       RadGrid1.ID = "RadGrid1"
       RadGrid1.Width = Unit.Pixel(1500)
       RadGrid1.Height = Unit.Pixel(700)
       RadGrid1.MasterTableView.EditMode = GridEditMode.InPlace
       RadGrid1.AllowPaging = True
       RadGrid1.AllowCustomPaging = True
       RadGrid1.PagerStyle.Mode = GridPagerMode.NextPrevAndNumeric
       RadGrid1.AutoGenerateColumns = False
       RadGrid1.ShowStatusBar = True
       RadGrid1.AllowSorting = True
       RadGrid1.AllowFilteringByColumn = True
       RadGrid1.Skin = "WebBlue"
       RadGrid1.ClientSettings.ClientEvents.OnGridCreated = "GetGridObject"
       RadGrid1.ClientSettings.Scrolling.FrozenColumnsCount = 2
       RadGrid1.ClientSettings.AllowColumnsReorder = True
       RadGrid1.ClientSettings.ColumnsReorderMethod = GridClientSettings.GridColumnsReorderMethod.Swap
       RadGrid1.ClientSettings.Selecting.AllowRowSelect = True
       RadGrid1.ClientSettings.Resizing.AllowColumnResize = True
       RadGrid1.ClientSettings.Scrolling.AllowScroll = True
       RadGrid1.ClientSettings.Scrolling.UseStaticHeaders = True
       RadGrid1.ClientSettings.Scrolling.SaveScrollPosition = True
       RadGrid1.MasterTableView.EnableViewState = True
       RadGrid1.MasterTableView.PageSize = 50
       RadGrid1.MasterTableView.AllowMultiColumnSorting = True
       RadGrid1.MasterTableView.DataKeyNames = New String() {"DetailID", "Day", "FoundGeographies", "SubZipCount"}
Me.PlaceHolder1.Controls.Add(RadGrid1)

[Vasil]

Hi David,

I am not able to test your code without the actual data, and could not be sure what is the problem in it.
Please take a look in our demo again since it is working correct. Indeed the data for the records is generated randomly, but the ID-s of the records are consistent from 1 to 100000 and you can see that the paging is working correct by the ID-s of the records. If you follow exact one of the samples it will work correct.

All the best,
Vasil
the Telerik team