分页和排序的问题 [英] Issues with pagination and sorting

问题描述

我正在研究面向数据库的Web应用程序中的情况，当应该依赖于客户端对表的排序而不是服务器端的排序。困扰我的一个特殊情况是分页。

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.

当尝试对一个大表（例如10000行）进行分页，以及按特定列对其进行排序时，会是什么是最好的方法吗？

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:

• 我无法一次性将整个表格返回给客户端


• 我无法使用javascript排序多达10000条记录


• 对表进行排序将涉及对所有页面中的行进行排序，而不仅仅是对当前页面进行排序。

所以你还有其他问题要添加到这个列表？

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?

附加：

好的，在数据​​库上排序并返回reqd页面，一个prev页面和下一页似乎是最好的选择。

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

现在考虑一下：

用户是按序列号排序的表格（第3页，共10页）现在，用户点击名为username的标题，希望按用户名对表进行排序。

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：最终结果应该是page（1 of 10）排序用户名或应该是页面（3/10）按用户名排序？

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?

推荐答案

客户端最好保持简单：Javascript排序/分页仅适用于非常小的结果集 - 小到足够小用户不会注意到性能损失。

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:

• 对于频繁的页面请求，请考虑使用Ajax请求预加载下几个（和之前的）页面，然后根据用户请求将行（通过Javascript）交换到表。

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

对于频繁求助，一个好的方法是在SQL中保留一个只有结果的缓存表。

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.

其他回复：

我非常主观的回答是：用户（我）想从任意页面排序。让他们（我）。没有什么比你想要的更烦人了，并且让应用程序将你带回到列表的开头。

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.

另一个考虑因素是多级排序：您是否希望按序列号实现排序，然后是用户名？考虑Microsoft Excel的功能或用户熟悉的任何其他应用程序。您的用户可能会对他们习以为常的内容感到满意 - 包括被扔回第1页。

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.

这篇关于分页和排序的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！