Unnecessary (and slow!) SQL request on ToDataSourceResult?

7 posts, 1 answers
  1. Indicia
    Indicia avatar
    18 posts
    Member since:
    Mar 2012

    Posted 06 Mar 2015 Link to this post

    I have a question. I have a rather large dataset (~ 1M records) containing track and trace log messages. The average number of messages per T&T code lies around 20 records. I have a page on which these log messages for a specified T&T code are displayed using a Kendo MVC Grid, without pagination (and no pagination is intended here). As the table is getting bigger, the web app response becomes slower and slower.

    I have done some profiling, and it turns out that the Json(result.ToDataSourceResult(request)) invocation in the Get ActionResult produces two queries. The first one is a (very slow, ~ 18 s) query which seems to take care of the 'Total'-property in the DataSource result. The second one is a much faster (~ 150 ms) query getting the actual records.

    Since we don't need pagination on this specific grid, I don't really understand why the total count is needed anyway. Can't Kendo be smart enough to skip getting the total count when we don't need it?

    I have searched the internet, and it seems like there are more people experiencing the same thing: http://stackoverflow.com/questions/15843703/kendo-mvc-todatasourceresult-extremly-slow-with-large-iqueryable
    The reactions there suggest to create a custom binding, but I would really prefer not to enter that path..

    Is there a solution for this?
  2. Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 10 Mar 2015 Link to this post

    Hi,

    Currently the Count() is executed always. You basically have two options:
    • Modify the implementation of the CreateDataSourceResult method (src\Kendo.Mvc\Extensions\QueryableExtensions.cs) and build a custom version of Kendo.Mvc.dll
    • Implement custom binding

    On a side note you mention that you have 1M records but don't want to implement paging. How do you plan to avoid selecting all items in memory?

    Regards,
    Atanas Korchev
    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.

     
  3. Indicia
    Indicia avatar
    18 posts
    Member since:
    Mar 2012

    Posted 20 Mar 2015 in reply to Atanas Korchev Link to this post

    I have 1M records in the database table, but the records are filtered to an average of ~20 records.
    I will take a look at the custom binding possibility. However, I would very much like an option to skip the count.
  4. Indicia
    Indicia avatar
    18 posts
    Member since:
    Mar 2012

    Posted 25 Mar 2015 Link to this post

    I have partly fixed the problem by pre-filtering the specific records.

    The two solutions you suggested are both not really acceptable for me. I started to code the custom binding, but realized I had to do the complete filtering myself. Which is not a nice thing to do, for a ~20 field entity. The pre-filtering option did the trick, at least for this part of the application.

    I still think it would be very nice to have the option to disable the count, or to do be able to implement a custom count action For some data sets, you just know that there are a lot of results, and you don't really have to know how many. It suffices to just press 'next page' until you have found what you're looking for. Kind of similar to a search engine like Google when you search for 'internet' for example (although they do give you a raw estimate of the results). 
  5. Answer
    Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 25 Mar 2015 Link to this post

    Hello Ict,

    Implementing custom filtering isn't that hard. Please check how this is done in the Kendo.Mvc.Examples\Controllers\Grid\CustomAjaxBindingController.cs file from the ASP.NET Sample application.

    Also feel free to open a new feature request in our feedback portal.

    Regards,
    Atanas Korchev
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  6. Neil
    Neil avatar
    38 posts
    Member since:
    Dec 2019

    Posted 26 Mar 2020 Link to this post

    I'm just throwing something here. I don't know if this is LINQ.
    But the problem with LINQ is that if you started to call its function.
    Example, if you have pageSize of 10, but then you need the Count()
    function, the Count() will loop through the list, which is not good...
    and it defeats the purpose why you have pagination...

  7. Tsvetomir
    Admin
    Tsvetomir avatar
    757 posts

    Posted 30 Mar 2020 Link to this post

    Hi Neil,

    The Count() function is used for getting the total amount of items. This total is used for the pager to decide how many pages there will be along with the information in the pager that displays the currently displayed items next to the total number of items in the data source. 

    That is why the recommended way for avoiding the call of the Count() method is to create a custom DataSourceResult instance and pass the data and total that should be retrieved explicitly by the developer.

     

    Regards,
    Tsvetomir
    Progress Telerik

    Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
    Our thoughts here at Progress are with those affected by the outbreak.
Back to Top