This is a migrated thread and some comments may be shown as answers.

RadGrid Filtering performance issues

6 Answers 104 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Dash
Top achievements
Rank 1
Dash asked on 03 Sep 2014, 02:52 PM
Hi,

I'm fairly new to the radgrid control, have a radgrid up and running, but I am having some trouble with performance as the number of records grows especially with filters. At the moment, I am using ObjectDataSource and Custom paging

<asp:ObjectDataSource runat="server" ID="OrderAdminOrderDataSource" EnablePaging="false" SelectMethod="GetAllOrdersByCountryISO2Code"
    TypeName="Organo.Web.Application.Facades.OrderAdminFacade" DataObjectTypeName="Organo.Web.Domain.Model.Entities.Order">
    <SelectParameters>
        <asp:Parameter Name="countryISO2Code" />
        <asp:Parameter Name="orderStatusFilterLevel"/>
    </SelectParameters>
</asp:ObjectDataSource>

protected void OrderAdminGrid_OnNeedDataSource(object sender, GridNeedDataSourceEventArgs e)
       {
           OrderAdminGrid.VirtualItemCount =
           OrderAdminFacadeInstance.GetSelectRowCount(CountryDropDown.SelectedItem.Text,
           HttpContext.Current.Items["OrderStatusFilterLevel"].ToString());
           var startRow = (ShouldApplySortFilterOrGroup())
               ? 0
               : OrderAdminGrid.CurrentPageIndex*OrderAdminGrid.PageSize;
           var maxRows = (ShouldApplySortFilterOrGroup())
               ? OrderAdminGrid.VirtualItemCount
               : OrderAdminGrid.PageSize;
           OrderAdminGrid.AllowCustomPaging = !ShouldApplySortFilterOrGroup();
           OrderAdminGrid.DataSource =
               OrderAdminFacadeInstance.GetAllOrdersByCountryISO2Code(CountryDropDown.SelectedItem.Text
                   , HttpContext.Current.Items["OrderStatusFilterLevel"].ToString(), startRow,
                  maxRows);
        }

The second parameter to the select clause is just an int that the code bases what order statuses to return by doing a contains on the list returned here's the code:

protected void OrderAdminGrid_OnNeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    //var filterexpression = OrderAdminGrid.MasterTableView.FilterExpression;
    OrderAdminGrid.VirtualItemCount =
    OrderAdminFacadeInstance.GetSelectRowCount(CountryDropDown.SelectedItem.Text,
    HttpContext.Current.Items["OrderStatusFilterLevel"].ToString());
    var startRow = (ShouldApplySortFilterOrGroup())
        ? 0
        : OrderAdminGrid.CurrentPageIndex*OrderAdminGrid.PageSize;
    var maxRows = (ShouldApplySortFilterOrGroup())
        ? OrderAdminGrid.VirtualItemCount
        : OrderAdminGrid.PageSize;
    OrderAdminGrid.AllowCustomPaging = !ShouldApplySortFilterOrGroup();
    OrderAdminGrid.DataSource =
        OrderAdminFacadeInstance.GetAllOrdersByCountryISO2Code(CountryDropDown.SelectedItem.Text
            , HttpContext.Current.Items["OrderStatusFilterLevel"].ToString(), startRow,
           maxRows);
 
    //OrderAdminGrid.DataSource = OrderAdminFacadeInstance.GetAllOrdersByCountryISO2Code(CountryDropDown.SelectedItem.Text,
    //    HttpContext.Current.Items["OrderStatusFilterLevel"].ToString());
}

public IQueryable<Order> GetAllOrdersByCountryISO2Code(string countryISO2Code, string orderStatusFilterLevel, int startRow, int pageSize)
{
    var orders = countryISO2Code == "All"
        ? _orderRepository.GetAllFilteredByOrderStatus(GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)))
            .OrderByDescending(n => n.OrderDate).Skip(startRow).Take(pageSize)
        : _orderRepository.GetAllFilteredByOrderStatus(GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)), countryISO2Code)
            .OrderByDescending(n => n.OrderDate).Skip(startRow).Take(pageSize);
    return orders;
}
 
public int GetSelectRowCount(string countryISO2Code, string orderStatusFilterLevel)
{
    var orders = countryISO2Code == "All"
        ? _orderRepository.GetAllFilteredByOrderStatus(
            GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)))
            .OrderByDescending(n => n.OrderDate)
        : _orderRepository.GetAllFilteredByOrderStatus(
            GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)), countryISO2Code)
            .OrderByDescending(n => n.OrderDate);
    return orders.Count();
}

and finally this method filters out the records based on whether they are in the filter list or not.

public
IQueryable<Order> GetAllFilteredByOrderStatus(List<int?> filter, string countryISO2Code)
{
    int monthlyOrder = Convert.ToInt16(Enums.OrderType.MonthlyOrder);
 
    return
        this.FindAsQuery(
            x =>
                x.OrderType.OrderTypeId != monthlyOrder &&
                x.ShippingAddress.Country.CountryISO2Code == countryISO2Code &&
                filter.Contains(x.OrderStatusId));
 
}


So this all works great until we start getting more and more records. After looking at the sample for 300000 records with linqdatasource, I thought I'd try that or using an entitydatasource. The only problem is I don't know how to pass in the filter of the list of ints for orderstatuses I want to show or how to use the where clause. I'm open to any suggestions that might imporove performance.

Thanks in advance

Dash

6 Answers, 1 is accepted

Sort by
0
Dash
Top achievements
Rank 1
answered on 03 Sep 2014, 02:55 PM
woops on the double OnNeedDatasources code listings. 
0
Dash
Top achievements
Rank 1
answered on 03 Sep 2014, 03:02 PM
here's the method I left out

private List<int?> GetOrderStatusFilter(int filterLevel)
      {
          List<int?> returnFilter = null;
          switch (filterLevel)
          {
              case (int)Enums.OrderAdminOrderStatusFilterLevel.Administrators:
                  returnFilter = new List<int?>()
                  {
                      (int) Enums.OrderStatus.Cancelled,
                      (int) Enums.OrderStatus.Completed,
                      (int) Enums.OrderStatus.RMA,
                      (int) Enums.OrderStatus.Refunded,
                      (int) Enums.OrderStatus.ProcessingPayment,
                      (int) Enums.OrderStatus.WebSaved,
                  };
                  break;
              case (int)Enums.OrderAdminOrderStatusFilterLevel.CustomerService:
                  returnFilter = new List<int?>()
                  {
                      (int) Enums.OrderStatus.Cancelled,
                      (int) Enums.OrderStatus.Completed,
                      (int) Enums.OrderStatus.RMA,
                      (int) Enums.OrderStatus.Refunded,
                      (int) Enums.OrderStatus.ProcessingPayment,
                      (int) Enums.OrderStatus.WebSaved,
                  };
                  break;
              case (int)Enums.OrderAdminOrderStatusFilterLevel.Undefined:
                  returnFilter = new List<int?>()
                  {
                      (int) Enums.OrderStatus.Cancelled,
                      (int) Enums.OrderStatus.Completed,
                      (int) Enums.OrderStatus.RMA,
                      (int) Enums.OrderStatus.Refunded,
                      
                  };
                  break;
              default:
                  returnFilter = new List<int?>()
                  {
                      (int) Enums.OrderStatus.Cancelled,
                      (int) Enums.OrderStatus.Completed,
                      (int) Enums.OrderStatus.RMA,
                      (int) Enums.OrderStatus.Refunded,
                       
                  };
                  break;
          }
          return returnFilter;
      }
0
Dash
Top achievements
Rank 1
answered on 03 Sep 2014, 03:20 PM
I left out the code for the grid itself. My bad:

<telerik:RadGrid runat="server" ID="OrderAdminGrid"
                             AllowCustomPaging="True" ShowGroupPanel="False" PageSize="15"
                             AllowMultiRowSelection="True" AllowAutomaticDeletes="False"
                             AllowAutomaticInserts="False" AllowPaging="true"
                             ShowStatusBar="False" AllowSorting="True"
                             EnableViewState="True" AutoGenerateColumns="False" AllowFilteringByColumn="true"
                             OnItemCommand="OrderAdminGrid_OnItemCommand"
                             OnItemCreated="OrderAdminGrid_OnItemCreated"
                             OnItemDataBound="OrderAdminGrid_OnItemDataBound"
                             EnableLinqExpressions="True"
                             OnNeedDataSource="OrderAdminGrid_OnNeedDataSource"
                >
                 
                
                <groupingsettings casesensitive="False"></groupingsettings>
                <clientsettings allowcolumnsreorder="False" enablerowhoverstyle="True">
                   <Selecting AllowRowSelect="True"></Selecting>
                   <Resizing AllowRowResize="True" EnableRealTimeResize="True" ResizeGridOnColumnResize="True"
                       AllowColumnResize="True"></Resizing>
                </clientsettings>
                <pagerstyle mode="NextPrevNumericAndAdvanced" alwaysvisible="True"></pagerstyle>
                <mastertableview autogeneratecolumns="False" datakeynames="OrderId"
                    editmode="EditForms" nomasterrecordstext="No orders found." allowsorting="True"
                    allowmulticolumnsorting="False" CanRetrieveAllData="False">
                    <Columns>
                        <telerik:GridTemplateColumn HeaderStyle-CssClass="order-admin-select-column-header"
                            ItemStyle-CssClass="order-admin-select-column" AllowFiltering="False">
                            <ItemTemplate>
                                <asp:CheckBox ID="SelectCheckBox" runat="server"/>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="30px" />
                        </telerik:GridTemplateColumn>
                        <telerik:GridBoundColumn UniqueName="OrderNumber" AllowFiltering="True" DataField="OrderNumber" AutoPostBackOnFilter="True" HeaderText="Order Number"
                            HeaderStyle-CssClass="order-admin-order-number-column-header" ItemStyle-CssClass="order-admin-order-number-column"
                            ShowSortIcon="True" ShowFilterIcon="False">
                            <HeaderStyle HorizontalAlign="Center" Width="120px" />
                            <ItemStyle HorizontalAlign="Center" />
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="CustomerName" AllowFiltering="True" DataField="Customer.Address.ContactInfo.FullName" AutoPostBackOnFilter="True" HeaderText="Customer Name"
                            HeaderStyle-CssClass="order-admin-select-column-header" ItemStyle-CssClass="order-admin-select-column"
                            ShowFilterIcon="False" >
                            <HeaderStyle HorizontalAlign="Center" Width="110px" />     
                            <ItemStyle HorizontalAlign="Center" />
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="CustomerEmail" DataField="Customer.Address.ContactInfo.EmailAddress" AutoPostBackOnFilter="True" HeaderText="Customer Email"
                            ShowSortIcon="True" ShowFilterIcon="False" >
                            <HeaderStyle HorizontalAlign="Center" Width="150px" />   
                            <ItemStyle HorizontalAlign="Center" />
                        </telerik:GridBoundColumn>
                        <telerik:GridDateTimeColumn DataField="OrderDateLocal"  AllowFiltering="True" EnableRangeFiltering="True"  AutoPostBackOnFilter="True" HeaderText="Order Date"
                             ShowSortIcon="True" SortExpression="OrderDateLocal" PickerType="DatePicker" EnableTimeIndependentFiltering="True" FilterListOptions="VaryByDataType"
                             DataFormatString="{0:MM/dd/yyyy}" ShowFilterIcon="False">
                         <HeaderStyle HorizontalAlign="Center" Width="162px" />
                        <ItemStyle HorizontalAlign="Center" />
                       </telerik:GridDateTimeColumn>
                        <custom:FilteringGridBoundColumn UniqueName="OrderStatus" DataField="OrderStatus.Code" HeaderText="Order Status">
                            <HeaderStyle HorizontalAlign="Center" Width="120px" />
                            <ItemStyle HorizontalAlign="Center" />
                            </custom:FilteringGridBoundColumn>
                        <custom:FilteringGridBoundColumn UniqueName="ShippingMethod" DataField="Shipment.ShippingMethod.Name" HeaderText="Shipping Method">
                            <HeaderStyle HorizontalAlign="Center" Width="120px" />
                            <ItemStyle HorizontalAlign="Center" />
                            </custom:FilteringGridBoundColumn>
                        <custom:FilteringGridBoundColumn UniqueName="ShipmentStatus" DataField="Shipment.ShipmentStatus.Name" HeaderText="Shipment Status">
                            <HeaderStyle HorizontalAlign="Center" Width="120px" />
                            <ItemStyle HorizontalAlign="Center" />
                            </custom:FilteringGridBoundColumn>
                        <telerik:GridBoundColumn  DataField="OrderTotal" AutoPostBackOnFilter="True" HeaderText="Order Total"
                            DataFormatString="{0:C}" ShowSortIcon="True" ShowFilterIcon="True" >
                            <HeaderStyle HorizontalAlign="Center" Width="112px" />   
                        </telerik:GridBoundColumn>                  
                       <telerik:GridTemplateColumn HeaderText="View Detail"  AllowFiltering="False" >
                            <ItemTemplate>
                                <div>
                                    <asp:ImageButton ID="ViewDetailButton" runat="server" ImageUrl="images/mag.png" OnClientClick='<%#String.Format("openRadWin(\"{0}?pstordrnum=\",\"{1}\"); return false;", GetViewDetailPageUrl(), Eval("OrderNumber")) %>' />
                                </div>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="40px" />
                        </telerik:GridTemplateColumn>
                        <telerik:GridTemplateColumn HeaderText="Return" AllowFiltering="False" Visible="False">
                            <ItemTemplate>
                                <div>
                                    <asp:ImageButton ID="ReturnButton" runat="server" ImageUrl="images/Return.png" Enabled="False" CommandArgument='<%# Eval("OrderId") %>'/>
                                </div>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="50px" />
                        </telerik:GridTemplateColumn>
                        <telerik:GridTemplateColumn HeaderText="Notes" AllowFiltering="False" Visible="True">
                            <ItemTemplate>
                                <div>
                                    <asp:ImageButton ID="NotesButton" runat="server" ImageUrl="images/Comment.png" Enabled="True" CommandArgument='<%# Eval("OrderId") %>'/>
                                </div>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="50px" />
                        </telerik:GridTemplateColumn>
                       <telerik:GridTemplateColumn HeaderText ="Process Refund" Visible="False" AllowFiltering="False" >
                           <ItemTemplate>
                                <div>
                                    <asp:ImageButton Visible="False" ID="DoProcessReturn" runat="server" ImageUrl="images/refund.png" Enabled="True" CommandArgument='<%# Eval("OrderId") %>' />
                                </div>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="50px" />
                       </telerik:GridTemplateColumn>
                        <telerik:GridTemplateColumn HeaderText ="Cancel Order" Visible="False" AllowFiltering="False" >
                           <ItemTemplate>
                                <div>
                                    <asp:ImageButton Visible="False" ID="CancelOrderButton" runat="server" ImageUrl="images/BigRedX.png" Enabled="True" CommandArgument='<%# Eval("OrderId") %>' />
                                </div>
                            </ItemTemplate>
                            <HeaderStyle HorizontalAlign="Center" Width="50px" />
                       </telerik:GridTemplateColumn>
                    </Columns>
                </mastertableview>
            </telerik:RadGrid>
0
Angel Petrov
Telerik team
answered on 08 Sep 2014, 11:21 AM
Hello Dash,

I am experiencing difficulties understanding the exact requirement. Do you want to obtain a reference to the filter value of the OrderStatus column and use it to filter the records in the NeedDataSource event? If that is the case when filtering will be applied you can use the below provided code to obtain a reference to the targeted value.

C#:
string value = OrderAdminGrid.MasterTableView.GetColumnSafe("OrderStatus").CurrentFilterValue;

Note however that when following this approach the records will be filtered twice, initially by the custom logic and a second time by the built-in grid filtering functionality. That said passing an additional value to filter the records may not improve the performance or at least not significantly.

Could you please elaborate more on how many records will be extracted from the database? Also please try binding the grid to a LINQ data source control and test whether this decreases the load time. Finally I suggest examining this help article in which you can find helpful tips and tricks on how to optimize the performance.

 

Regards,
Angel Petrov
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Dash
Top achievements
Rank 1
answered on 09 Sep 2014, 09:38 PM
I currently have 20k records but expect something on the order of 200000 records eventually. I first tried using the objectdatasource because it let me put in select parameters. Like this:
<asp:ObjectDataSource runat="server" ID="OrderAdminOrderDataSource" EnablePaging="false" SelectMethod="GetAllOrdersByCountryISO2Code"
    TypeName="Organo.Web.Application.Facades.OrderAdminFacade" DataObjectTypeName="Organo.Web.Domain.Model.Entities.Order">
    <SelectParameters>
       <asp:Parameter Name="countryISO2Code" />
        <asp:Parameter Name="orderStatusFilterLevel"/>
    </SelectParameters>
</asp:ObjectDataSource>

The second select parameter is used here:
public IQueryable<Order> GetAllOrdersByCountryISO2Code(string countryISO2Code, string orderStatusFilterLevel, int startRow, int pageSize)
        {
            var orders = countryISO2Code == "All"
                ? _orderRepository.GetAllFilteredByOrderStatus(GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)))
                    .OrderByDescending(n => n.OrderDate).Skip(startRow).Take(pageSize)
                : _orderRepository.GetAllFilteredByOrderStatus(GetOrderStatusFilter(Convert.ToInt32(orderStatusFilterLevel)), countryISO2Code)
                    .OrderByDescending(n => n.OrderDate).Skip(startRow).Take(pageSize);
            return orders;
        }      
 
 public IQueryable<Order> GetAllFilteredByOrderStatus(List<int?> filter, string countryISO2Code)
        {
            int monthlyOrder = Convert.ToInt16(Enums.OrderType.MonthlyOrder);
 
            return
                this.FindAsQuery(
                    x =>
                        x.OrderType.OrderTypeId != monthlyOrder &&
                        x.ShippingAddress.Country.CountryISO2Code == countryISO2Code &&
                        filter.Contains(x.OrderStatusId));
 
        }

This worked except the performance was terrible. So I am trying to adapt this to use linqdatasource. But I couldn't figure out how to use the second select parameter like with the object datasource. Also filtering isn't working with the linqdatasource. I've tried all kinds of things to speed up this grid and am running low on ideas.



0
Angel Petrov
Telerik team
answered on 12 Sep 2014, 02:54 PM
Hi Dash,

Actually you should be able to define a select parameters declaratively for the LinqDataSource without any problem. Moreover it is rather strange that filtering is not working when such data source is used. Could you please send us a runnable sample that we can debug locally. That way we will be able to inspect what parameters are passed and why are you observing such behavior.

Regards,
Angel Petrov
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
Tags
Grid
Asked by
Dash
Top achievements
Rank 1
Answers by
Dash
Top achievements
Rank 1
Angel Petrov
Telerik team
Share this question
or