Pagination and Sorting Issues

Question

I look at situations in database-oriented web applications where you need to rely on client-side table sorting when server-side sorting. One particular situation that bothers me is pagination.

When trying to split a page into a large table (say 10,000 rows) and also sort it by a specific column, what would be the best approach?

I understand that some of the problems associated with this:

• I cannot return the entire table to the client side at a time.
• I cannot sort up to 10,000 entries using javascript
• Sorting the table will include sorting the rows on all pages, not just the current page.

Do you have more questions to add to this list?

Which approach would lead to a good combination of client-side and server-side interactions to minimize server load?

---

Addition:

Well, sorting by database and returning the reqd page, the best page and the next page seems to be the best choice.

Now consider the following:

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

Quesion: If the end result is "a page (1 out of 10) sorted by username" or should there be a "page (3 out 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 simplified: Javascript sorting / paging is intended only for very small result sets - it is small enough so that the user does not notice that it affects performance.

On the server side, you can optimize server load:

The load can come in the form of frequent requests for more pages, a large number of rows / columns per page, and frequent requests for use. (We didn’t even talk about filtering)

Thus, depending on your users and actual usage, you may need some form of caching. Please note that these are suggestions for the time AFTER you know what your users are doing:

• For frequent page requests, consider making some Ajax requests preload the next few (and previous) pages and then swap (via Javascript) the table at the user's request.

• For large page sizes, consider storing lines in the application cache of the (most frequently used) applications (memory), so that the database does not require many times to re-peel the same huge chunks of data.

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

And always, always, always index the database accordingly.

---

Additional answer:

My very subjective answer is this: the user (s) want 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 that brings you back to the top of the list.

Another consideration is several sorting levels: do you want to sort by serial number, then by user name? See what Microsoft Excel, or any other application that your users are familiar with, does. Your users are likely to be fine with what they are used to - including being dropped to page 1.

Answer 2

Databases are true animals when sorting and selecting data. Therefore, your best bet, of course, is to tell the client to the server: "I need page X with Y-lines sorted by Z." Then the database performs its task, and the client displays the result. To improve performance, you can make the results of your client cache, and in addition, you could make your code a request for the next and previous pages after the current page has been selected so that they can be displayed instantly on request.

Answer 3

A better approach would be to sort and swap at the database level and return only a subset of the source data that will only be displayed on the screen. There is no javascript in this scenario.

If for some reason you cannot sort and place pages at the database level, you should do this from the server side of the script. There is no javascript in this scenario.

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

Answer 4

Determine the type of db that will be delivered to the specific page on the screen. . It will almost always be faster, be cached and facilitate such loading in the browser.

If you like, the client side allows further sub-sorting through javascript, etc., if the data is detailed enough and can benefit from this. . You may want to save which sub-sorting functions are selected so that they are remembered between pages.