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

[Julio]

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,

[Petur Subev]

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