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