Optimize Pagination & Sorting with ObjectDataSource having EnableCaching = true

| | August 8, 2015

I’m using an ODS(ObjectDataSource) backed-up with a Linq-To-SQL class to populate Gridview on my page.

Considering the performance – I’ve disabled the Viewstate of the Gridview and enabled caching in the ODS.

Apart from this, I’ve also optimized the Search method in the Linq-to-SQL class to use the .skip & .take methods to fetch only a ‘pageful’ of records.


Now, the problem is that due to caching the ODS is unable to ‘sort’ the record set on its own. As detailed on this thread:

http://stackoverflow.com/questions/1974162/gridview-sorting-doesnt-work-when-i-enable-caching-in-custome-paging-and-sorting/2251499#2251499

People recommend to use custom sort and implement ‘Comparer’ but I believe this ruins my performance benefits.

http://forums.asp.net/t/1344883.aspx

I’m ready to make a DB-trip while sorting but how to separate that when the Caching is turned-on?

FYI, I already have an AJAX update panel in which I’ve got this Gridview (EnableViewstate = false) and ODS (EnableCaching=true). Hope I’m on the right path … suggestions are appreciated.


I’ve to perform sorting on app side using the ‘Custom-sort’ (that is add extra methods to enable sorting on a generic collection of objects). This solution was not acceptable because it demanded that I pull ALL the records from the DB and then perform sorting on them!

Firstly, I don’t believe app can do better/faster sorting then DB. And secondly – this ruins the whole performance benefit that I’m getting due to optimized pagination – I’m using .skip() and .take() LINQ methods to fetch only a ‘pageful’ of records.

Well, finally I had to invent a fix of
my own. It might be limited to my kind
of scenario but for sure its much
easier and also preserves the
optimization of both pagination as
well as data-caching.

MY SOLUTION:
I’ve tapped the Gridview’s ‘Sorting’ event. The ‘custom-sort’ error triggers if I allow the ODS to try to do the sorting on its own on the cached data. Instead of that, now I perform sorting manually and cancel the sorting-event.
For this – I just have to make the ‘orderBy’ parameter explicit in ODS and set it to the new sort-expression in Gridview’s ‘Sorting’ event. This will refresh the Grid and at the end I do:

odsOrganization.SelectParameters["orderBy"].DefaultValue = GetSortExpr(e.SortExpression);
...
e.Cancel = true;
  • this tells the ODS to cancel sorting
    (which I already performed before
    canceling the event – as above). ITs
    like I’m cheating the ODS and handling
    the sorting in background. And thanks
    to ODS that it senses that the
    ‘SelectParameters[“orderBy”]’ has
    changed and performs a select once
    again. I The previous sort
    automatically gets stored in the
    ‘odsOrganization.SelectParameters[“orderBy”].DefaultValue’
    which I can use in sub-sequent
    iterations.

I’m still testing this one but its amazing that just by updating the parameter’s DefaultValue ODS goes back to fetch the data. This preserves the cache until the user performs sort (it takes data from cache for other operations) and goes back to the DB to sort. Hope its going to work for me!

One Response to “Optimize Pagination & Sorting with ObjectDataSource having EnableCaching = true”

  1. Will this work?

    http://dotnetarchitect.wordpress.com/2009/04/07/gridview-sorting-trick-when-using-object-datasource-with-custom-objects/

Leave a Reply