Paging query is a common problem. First, let's look at the reasons for paging query:
- Convenient for users: users cannot view all data at a time, so it is better to view data on one page.
- Performance Improvement: it is slow to extract all data from the database at one time.
Now I will try to refute the above reasons:
- Is it really convenient? Let's consider the following situation:
- If there are only 20 data records.
- If there are more than 1000 data records.
The first option obviously does not require paging query. This is strange because no user is willing to flip a page by page to the end. If the data queried by the user exceeds the data range he cares about, I think we should ask him to re-enter the query conditions, just like we use Google.
However, as a friendly application interface, we always hope that the user can fully understand the query results, so it is necessary to tell the user: "How much data have you found, however, currently, only the first 1000 entries can be displayed. If you want to view all the data, what should you do..."
- Will the performance be improved?
- If the data size is small, the performance will not be significantly improved. On the contrary, the performance will be greatly reduced. Because the database executes unnecessary query and query conditions.
- If the data size is large, the performance will not be significantly improved, because you always need to execute an additional count query, and when combining SQL statements, it is very likely to cause a full table scan. Of course, this depends on the implementation principle of the database.
- As you can imagine, the relationship between the performance impact of paging query and the data volume should be a curve. When the data volume is small, the performance will be reduced. When the data volume is large, it is possible (depending on different databases) will improve the performance. The key is to test and find the inflection point of the curve.Performance is not obtained based on experience and perception, but obtained through testing.
- In addition, if all the data is retrieved at a time, it will indeed affect the space performance. However, the memory is very cheap now...
- Negative Impact
- For a well-structured web application, it is really uncomfortable to pass pageno and pagesize between classes. The two data obviously belong to the presentation layer. Of course, if you use Ror, you do not have to say it.
- Significantly improves programming complexity, especially when considering database independence.
- Strange phenomenon: Why does not a large database directly provide paging queries? Oracle rowno is not used for paging, and SQL Server top is not.
- Conclusion
- Extremetable, displaytag, and JSF datatable all provide a simple paging method, that is, pagination in the result set. It is easy to use, and the logic is clear, greatly improving the work efficiency. This method can be used directly in most cases.
- If the test shows that the above method affects the performance, use paging query.
- For applications with a large number of users, you can also consider querying by page because of memory. However, I personally recommend caching the same query in a cache...
- Use a reasonable design to shield developers from handling paging logic. For example, if you place paging logic and count queries in the parent class, developers are responsible for combining query conditions. For details, see the design mode.
