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);
}​

Withy

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? – Verlinevermeer

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 – Trinatrinal

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? – Verlinevermeer

@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. – Verlinevermeer

If you use client-side sorting/paging, how do you handle the situation of IE7/8 with 200+ records getting very sluggish? – Lapsus

@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. – Quadrant

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. – Lapsus

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<T> to process paging and sorting **on the SQL server side and not in your C# program like you do now. – Zambrano

@Quadrant 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. – Verlinevermeer

@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) – Verlinevermeer

@Magrangs That isn't always the developers decision to make sadly. – Lapsus

@KevinB Yep I agree, although it should be ;-) – Verlinevermeer

@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. – Quadrant

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. – Quadrant

@Quadrant 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). – Verlinevermeer

@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 :-) mleibman.github.com/SlickGrid/examples/… – Verlinevermeer

WOW ! where do I start @All, I want to do server side because in future I will be having like millions of records – Withy

Answer 1

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.

Pellucid

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 ! – Withy

Answer 2

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.

Zambrano