Server side paging and sorting Best practice?

Question

I am using the following method to get data for my jqgrid's subgrid and its working completely fine.

Note that this method is used to implement server-side sorting and paging.

Now the query I have is as you can see in the line

List<SomeEntity> myList = _service.GetSomeData(id); here a database call is made and all records are fetched.

So I was just not very sure, So I just wanted to know if this is in with the best practice to implement serverside paging and

public JsonResult GetData(string folderId, string sidx, string sord, int page, int rows) {
    int id = int.Parse(folderId);
    List < SomeEntity > myList = _service.GetSomeData(id);

    const int pageSize = 5;

    // total
    double totalPages = Math.Ceiling((double) myList.Count() / pageSize);

    // sort
    if (sord == "asc") {
        myList = myList.OrderBy(m = > m.Name).ToList();
    }
    else {
        myList = myList.OrderByDescending(m = > m.Name).ToList();
    }

    // paging
    myList = myList.Skip((page - 1) * pageSize).Take(pageSize).ToList();

    var jsonData = new {
        total = totalPages, records = domainList.Count, page,

        rows = myList
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

The question you may have to ask yourself if this is completely necessary. You can handle paging and sorting via the front end using many different components (slickgrid, jqGrid, datatables etc.) which can handle large amounts of data. What is the reason for wanting to do it server-side? — mbx-mbx, Sep 21 '12 at 13:39
typically, handling it server-side reduces the amount of data to send to the client - which is handy if they are on a ropey connection. also means that navigating between pages will pick up any new data entered since the first page was loaded — logical Chimp, Sep 21 '12 at 13:40
Thankfully, ropey connections are in the minority these days as most people have a broadband connection. Would this site be used from mobile devices by any chance? — mbx-mbx, Sep 21 '12 at 13:45
@logicalChimp Agreed but there are ways of dealing with this using timestamps. I do a very similar thing but ask the server at regular intervals 'what has changed since this timestamp' (via ajax) and I only get back the latest changes which is typically very small (and then dynamically update the grid). Initially the first load of the page receives the largest amount of data (as the timstamp is 0) but we haven't had any issues with large amounts of json data. — mbx-mbx, Sep 21 '12 at 13:52
If you use client-side sorting/paging, how do you handle the situation of IE7/8 with 200+ records getting very sluggish? — Kevin B, Sep 21 '12 at 13:56
@Magrangs The problem with client side paging is that in most situations the first page is all that is ever needed, so by only fetching and sending that one page then you will improve the performance (often significantly) of generating that page. When the user wants more than just the first page they are also unlikely to use more than just a few pages. Viewing more than just a few pages tends to be rather unusual. — Servy, Sep 21 '12 at 13:57
As far as this question goes, I usually do the filtering and sorting with the database query, it tends to be more optimized at that task. I don't see anything wrong with what you are doing. — Kevin B, Sep 21 '12 at 14:00
It could be important to know how `_service.GetSomeData(id)` is defined. Do you use Entity Framework or LINQ To SQL? Which type return `_service.GetSomeData(id)`? In any way you should **not cast the `_service.GetSomeData(id)` to `List` to process paging and sorting **on the SQL server side** and not in your C# program like you do now. — Oleg, Sep 21 '12 at 14:04
@Servy I guess it depends on the situation and whether it is a website or a web application. Also combined with server side caching, in our situation, only the very first page request receives the largest amount of data (which is not really an issue in terms of speed either). Subsequent requests are very very quick with no issues with paging and sorting large amounts of client side data. — mbx-mbx, Sep 21 '12 at 14:09
@KevinB Don't support IE7 and have minimal support for IE8. Also use a component that can handle these large datasets like slickgrid or datatables. (we are lucky in our situation where we do not have to support these older browsers) — mbx-mbx, Sep 21 '12 at 14:12
@Magrangs That isn't always the developers decision to make sadly. — Kevin B, Sep 21 '12 at 14:14
@Magrangs `only the very first page request receives the largest amount of data` EXACTLY! The first page will be viewed more often than all of the other pages combined. People usually find what they need on the first page of most paginated results in general, and add into that people going to the page when they didn't actually need to view the results at all (hit the wrong page, didn't know what was there, just looking around, etc.). The first page of any paginated dataset should be optimized as much as possible. — Servy, Sep 21 '12 at 14:19
In a best case I would fetch only the first page when it is viewed, and then when viewing any other page fetch either all pages (if all is reasonable) or a group of several pages and page on the client side from then on, but that is more work so in practice it's often not worth bothering. — Servy, Sep 21 '12 at 14:20
@Servy Generally on websites, yes, in web apps where the user might have a large amount of tasks to do, not always. Anyway, I'm not going to get into a debate (these comments are large enough as it is). — mbx-mbx, Sep 21 '12 at 14:24
@KevinB Just for your reference here is an example of slickgrid handling 500,000 rows. Also I believe the only browser that is not suported is IE6 :-) http://mleibman.github.com/SlickGrid/examples/example-optimizing-dataview.html — mbx-mbx, Sep 21 '12 at 14:42
WOW ! where do I start @All, I want to do server side because in future I will be having like millions of records — Yasser Shaikh, Sep 21 '12 at 15:34

score 3 · Accepted Answer · answered Sep 21 '12 at 14:03

It looks like you're returning all the data from your service (_service) and then paging the results. Also, it looks like you are going to make the same request everytime a paging request is made. If true, then I think this is inefficient.

Your service (_service) should handle the Take and Skip functionality (pass as parameters), thereby reducing the number of records that are fetched and sent over the wire. You didn't post the code for your GetSomeData(id) method. Is it returning an IEnumerable or IQueryable? This will also have a bearing on performance/efficiency.

I hope I haven't misunderstood your code or question.

yes, thats it I should have the take and skip methods used while querying my db and should fetch only required data which is 5 at a time. Thanks man ! — Yasser Shaikh, Sep 21 '12 at 15:35

score 1 · Answer 2 · edited May 23 '17 at 12:18

Your main problem is that you assign result of _service.GetSomeData(id) to List<SomeEntity>. In any way you should use ObjectQuery<SomeEntity> or IQueryable<SomeEntity> (depend on the used database technology) to be able to use sorting and paring on the SQL Server side. Your current code just get all the data, then sort it and get the required page in the next statement. I could recommend you to look at the code from the answer or more recent code from another answer more examples how sorting, paging and filtering of data can be implemented for jqGrid.

Server side paging and sorting Best practice?

2 Answers2