Issues With Pagination And Sorting

I'm looking into situations in database-oriented web applications when one should rely on client side sorting of tables over sorting on the server side. One particular situation th

Solution 1:

The client side is best kept simple: Javascript sorting/paging is only for very small result sets - small enough that a user will not notice a performance hit.

The server side is where you can optimize server load:

Load can come in the form of frequent requests for more pages, large numbers of rows/columns per page, and frequent requests for resorting. (We haven't even talked about filtering)

So depending on your users and actual usage, you may need some form of caching. Note, these are suggestions for a time AFTER you know what your users are doing:

• For frequent page requests, consider having some Ajax requests preload the next few (and previous) pages, then swap in the rows (via Javascript) to the table upon user request.

• For large page sizes, consider keeping the rows in a "most-recently-used" application (memory) cache, so that the database is not required to spit out the same huge chunks of data over and over again.

• For frequent resorting, a good approach is to keep a cache table in SQL with only the results.

And always, always, always index the database appropriately.

---

Additional response:

My very subjective response is this: The user (me) wants to sort from an arbitrary page. Let them (me). There is nothing more annoying than being where you want to be, and having an application throw you back to the beginning of the list.

Another consideration is multiple levels of sorting: do you want to implement sorting by serial number, then username? Consider what Microsoft Excel does, or any other application that your users are familiar with. Your users will probably be fine with what they are accustomed to - including being thrown back to page 1.

Solution 2:

Databases are true beasts at sorting and selection of data. So your best bet is definitely to have the client say to the server, "I want page X with Y rows sorted by Z." Then the database does its thing, and the client shows the result. To improve performance you could make your client cache results, and furthermore you could make your code request the next and previous pages after the current one has been fetched so that they can be shown instantly on request.

Solution 3:

The best approach would be to do the sorting and paging at the database level and return only a subset of the original data that will only be shown on the screen. No javascript in this scenario.

If for some reasons you cannot sort and page at the database level, then you should do it with a server side script. No javascript in this scenario either.

And the worst approach would be to do the sorting and paging with javascript which of course is not recommended at all for obvious reasons.

Solution 4:

Have the db sort what gets delivered to the particular page on screen. It will almost always be faster, be cached and ease such a load on the browser.

If you like, have the client side allow further sub-sorting via javascript, etc if the data is detailed enough and could benefit from it. You may want to store what sub-sorting features are selected so they are remembered between pages.