Issues with pagination and sorting

Question

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 that is bugging me is pagination.

When trying to paginate a large table (say 10000 rows), as well as sort it by a particular column, what would be the best approach to take?

I understand that some issues related to this are:

• I cant return the whole table to the client side in one go
• I can't sort as many as 10000 records with javascript
• sorting the table will involve sorting rows in all pages, not just the current page.

So do you have any more issues to add to this list?

What approach would lead to a good mix of client side and server side interaction so that server load is minimized?

---

ADDITION:

Okay, sorting on the database and returning the reqd page, a prev page and a next page seems to be the best bet.

Now consider this:

The user is on page (3 of 10) of the table sorted by serial number. Now the user clicks on the header named "username", wanting to sort the table by username.

Quesion: Should the end result be "page (1 of 10) sorted by username" or should it be "page (3 of 10) sorted by username"?

I know this is a very subjective question, but what would you recommend and why?

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 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.