server|sqlserver| Paging
There are three ways to pagination with SQL and Oracle databases!
Let's take a look at how the two methods are implemented if we want to get data from 1000th to 1010th in the database.
1. Methods of using temporary tables. (The main thing in the system is to write SQL statements directly)
A) Arrange the order by the order you want
b) Create a temporary table
c The data from article No. 0 to 1010th is removed from the database
D. Put the data into a temporary table
(e) Arrange the order of the temporary table by the reverse way of a.
f) and then simply display the first 10 of the temporary table.
(g) Destruction of temporary tables
2. Method of using Object
A) Arrange the order by the order you want
b from the database to remove No. 0 to 1010th of the data
(c) Pour the 10 from these 1010 data into an object
D) completely invert the record in this object.
E to show the data in object
Obviously the second method is better than the first one. It reduces the resources needed to create and destroy temporary tables, but they all share a common weakness. That is, they have to take out of the database No. 0 to 1010th of the data so that the query out of the number of records, but the amount of data transmission network is very large!
Therefore, a better paging approach should be:
Retrieve data from a block of page size only from the database each time you page. In this way, although each page will need to query the database, but the number of records to query out a small amount of data transmission network, if the use of connection pool can be skipped the most time-consuming to establish the database connection process. At the database end, there are a variety of mature optimization techniques used to improve the query speed, than in the application server layer cache more effective.
For SQL Server databases to get 第1000-1010条 records:
Select Top * FROM (
Select Top * FROM (
Select Top 1010 * out Docdetail ORDER by lastmodidate ASC, Id ASC
) temptbl1 ORDER BY lastmodidate Desc, Id desc
) temptbl2 ORDER BY Lastmodidate Asc,id ASC
For Oracle databases to get 第1000-1010条 records because rownum in Oracle is assigned before the query is sorted. So the corresponding writing should be:
SELECT * FROM (
Select My_table.*, rownum as Temptbl_rownum from (
Select * from Docdetail-lastmodidate asc,id ASC
) Temptbl where RowNum <1010
where Temptbl_rownum >=1000
When the above SQL statement is completed, the amount of data transferred from the network is reduced from the previous 1010 to 10
Through the above paging mode of change, the performance of our system is greatly improved
One of my clients is using the Oracel database, where the number of documents is 120,000, when we use the first method to page pages when we search this table, the page is displayed for about 10 seconds, while the third method now takes about 2-3 seconds for the page to display.
Of course, there may be a better way to pagination, always feel as the data in the database, the system will slow down the speed, I posted this article, just want to discuss with you, there is no better way, I hope we do not hesitate to reply! Discuss it together!
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
