Kendo Grid Performance while doing the Read Request (Filtering, Sorting, Grouping)

2 posts, 0 answers
  1. Julio
    Julio avatar
    2 posts
    Member since:
    Oct 2012

    Posted 25 Nov 2013 Link to this post

    Hi,

    I've read several posts (stackoverflow, kendo forums, ...), but I don't find a 100% effective solution for my scenario. My team is facing a performance problem with our current code to filter/sort/group/... records in the Kendo Grid, this is a simple example of one of our possible flows:

    MVC Controller Action:
    [HttpPost]
    public ActionResult Read([DataSourceRequest] DataSourceRequest request)
    {
        // userService uses a client to call WebAPI action
        var usersList = this.userService.UsersList;
          
        var dataSource = usersList.ToDataSourceResult(request);
        return this.Json(dataSource, JsonRequestBehavior.AllowGet);
    }
    Client Action:
    public UserResponseDto Users()
    {
        var requestUri = this.ApiRequestUri("/User/User/Get/");
        var response = this.GetAs<UserResponseDto>(requestUri);
        return response;
    }
    WebAPI Controller Action:
    [HttpGet]
    public UserResponseDto Get()
    {
        var result = this.userService.GetUsers(null);
        var response = new UserResponseDto { Users = result };
        return response;
    }
    The Repository:
    public IEnumerable<UserDto> Users(UserRequestDto userRequestDto)
    {
        // calls an store procedure and retrieves data as DTO
        var parameters = new[]
               {
              SqlHelper.BuildParameter(SqlDbType.VarChar, "@sUserId", ParameterDirection.Input, userRequestDto.UserId)
       };
      
        using (var sqlAccess = this.sqlAccessFactory.Invoke(//some parameters))
        {
            var collectionPocos =
                sqlAccess.ExecProcedureEntity<UserPoco>(
                    "SVC_GetUser", parameters.ToArray(), true);
      
            return
                collectionPocos.Select(
                    sp =>
                    new UserDto
                    {
                        // Fill the properties
                    });
        }
    }

    As you can see we’re getting all the records from API and then we use this Kendo function “ToDataSourceResult” that makes all the logic and show just required records in the grid. 

    The problem is that there are some cases where the data set has (bi)millions of records, fetching all of the rows is a very expensive process, so we're trying to do the filtering, sorting, grouping,.. in the stored procedure to reduce the amount of data per request. As a team we discussed of possible solutions:
    • Create a functionality to get from the Kendo DataSourceRequest object (filters, sorting, page,…) and dynamically build the request and send them to the WebAPI. 
    • Build the where clause based on the filters that we have and send it as parameter to the SP, like in this post.
    • Use OData (according this article Kendo UI MVC Wrappers doesnt support OData)
    Does anybody could provide me some guidance if this is a known issue, if there is a workaround or solution?

    Some extra information of our System:
    • It's a N-Tier application
    • MVC is used for the front-end tier
    • All the Data Base calls must be done using Stored Procedures
    • The WebAPI is the one that calls the stored procedures
    • In future all the tiers will be placed in different servers, probably with different firewall configurations (so, the idea is to minimize the size of data that travels between them)
    • Application uses the Kendo UI MVC Wrappers 
    • There's a custom solution to apply our metadata in the Kendo Wrapper, so instead of this code
    @(
        Html.Kendo().Grid<User>()
            .Name("UsersList")
            .DataSource(dataSource => dataSource
                .Ajax()
                .Read(read => read.Action("Read", "User"))
            )
            .Scrollable(s => s.Virtual(true).Enabled(true))
    )
    we have this:
    @(
        Html.Flex().GridFor(m => m.UsersList, "Id", "Read", "User")
    )

    Some links:

    http://stackoverflow.com/questions/15843703/kendo-mvc-todatasourceresult-extremly-slow-with-large-iqueryable
    http://www.kendoui.com/forums/kendo-ui-web/grid/handle-filter-generated-parameters-on-server-side-for-a-grid-serverfiltering-true.aspx
    http://stackoverflow.com/questions/13738249/get-paging-filtering-from-telerik-extension-grid-and-pass-to-stored-procedure
    http://www.telerik.com/community/forums/aspnet-mvc/grid/best-approach-for-large-amounts-of-data.aspx
    http://blogs.msdn.com/b/webdev/archive/2013/02/25/translating-odata-queries-to-hql.aspx


    Thanks in advance,
  2. Petur Subev
    Admin
    Petur Subev avatar
    1882 posts

    Posted 27 Nov 2013 Link to this post

    Hello Julio,

    I posted a reply in the support ticket that you opened on the same subject. For reference I will paste it here too:

    Basically as noted in the documentation the paging is executed on a database level thanks to the LINQ Expression engine (this should be highly efficient and you do not have to worry even for large databases).
    http://docs.kendoui.com/getting-started/using-kendo-with/aspnet-mvc/helpers/grid/faq#how-do-i-implement-paging,-sorting,-filtering-and-grouping?
    If for some reason you want to change this and use stored procedures like you mentioned then you need to consider implementing custom binding as explained here:
    http://docs.kendoui.com/getting-started/using-kendo-with/aspnet-mvc/helpers/grid/custom-binding#custom-ajax-binding

    Kind Regards,
    Petur Subev
    Telerik
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  3. UI for ASP.NET MVC is VS 2017 Ready
Back to Top