About paging queries and performance issues

Paging query is a frequent problem, we first look at the reasons for the existence of paging query:

User-friendly: Users can not see all of the data at once, so a page of the turn look better.

Improve performance: It is slower to extract all data from the database at once.

So now I'm going to try to refute the above reasons:

Is it really convenient? Let's consider the following situation.

If the data is only 20.

If the data exceeds 1000.

The first is obviously no paging query. The odd thing is that the second doesn't have to be, because no one is willing to turn to the end of a page, and if the user queries more than he cares about, I think we should let him re-enter the query, just like we do with Google.

But as a friendly application interface, we always want users to be able to fully understand his query results, so it is necessary to tell the user: "You have found how much data, but currently only the first 1000, if you want to see all the data, then how should ... ”

Will performance improve?

If the amount of data is small, obviously performance will not be significantly improved, on the contrary, performance can be greatly reduced. Because the database executes unnecessary queries and query conditions.

If the volume of data is large, performance may not be significantly improved because you always have to perform an extra count query, and combining SQL is most likely to result in a full table scan. Of course, this depends on the principle of the implementation of the database.

It can be imagined that the relationship between the performance and the amount of data in a paging query should be a curve, which degrades performance when the data is small, and may improve performance (depending on the database) when the volume of data is large. The key is to pass the test to find the inflection point of the curve. Performance is not based on experience and feeling, but through testing.

In addition, if the data is all taken out at once, it does have a spatial performance impact, but now the memory is very cheap ...

Negative impact

For a well-designed web application, it is not good to pass PageNo and pagesize across classes, and these two data clearly belong to the presentation layer. Of course, if you use Ror, I didn't say.

Significantly improve programming complexity, especially when considering database independence.

Strange phenomenon: Why does not a large database directly provide paging query? Oracle's ROWNO is not for paging, and SQL Server's top is not.

Conclusion

Extremetable, Displaytag, and JSF DataTable provide a simple way of paging, which is paging through the result set. Very convenient to use, but also makes the logic clear, greatly improve the efficiency. In most cases, you can use this approach directly.

If you pass the test and find that the way it affects performance, consider using a paging query.

For users with a large number of applications, because of memory reasons, you can also consider paging query. However, I personally recommend caching methods: the same query in a cache ...

Using a reasonable design, shielding developers to deal with the paging logic. For example, the paging logic and count queries are placed in the parent class, and the developer is responsible for combining the query criteria. Specifically look at the design model bar.

Please contact the site, timely note your name. Contact Email: edu#chinaz.com (change # to @).