This post is original, such as reprint, please keep this information.
Http://blog.9cbs.net/hunkdong/archive/2005/04/12/343852.aspx
Use SQL, and the Oracle Database Map can have the following three methods!
Let us take a look at how if we want to take the data from 1000 to 1010 in the database.
1. Use a temporary table method. (Mainly writing SQL statements directly)
a) Row the order as required
b) Create a temporary table
c) Remove the data from Arts 0 to 1010 from the database
d) put these data into the temporary table
e) Rows the temporary table again in the opposite way to a)
f) Then just show the top 10 in the temporary table.
g) destroy the temporary table
2. Method using Object
a) Row the order as required
b) Remove the data from Arts 0 to 1010 from the database
c) Take 10 of these 1010 data into one Object
d) completely invert this record in this object
e) Show out the data in Object
Obviously the second method is better than the first method, which reduces the resource consumed by the system, destroying the temporary table, but they all have a common weakness. That is, they have to take out the 0th to 1010th from the database. The data of the strip has caused very little records in query, but network transmission data is very large!
So a better paging approach should be:
Retrieve the data of the block area of the page only from the database every time you turn your page. Thus, although the database is queried each time, the number of records that queries is small, the network transmission data is not large, and if the connection pool can be used, it can be skipped by the most time consuming database connection process. There are various mature optimization techniques in the database to improve query speed, which is much more effective than making a cache in the application server.
For the SQLServer database, if you want to get the item 1000-1010 record:
SELECT TOP 10 * from (select top 1010 * from docdetail order by lastmodidate asc, id) Temptbl1 Order By LastModidate Desc, ID DESC) TempTBL2 Order by LastModidate ASC, ID ASC
For Oracle Database If you want to go to Section 1000-1010 Record Due to the ROWNUM in Oracle, it is assigned before the query is sorted, so its corresponding way should be:
Select * from (select my_table. *, rownum as temptbl_rownum from (select * from docdetail) TempTBL WHERE ROWNUM> = 1000 WHERE TEMPTBL_ROWNUM> = 1000
When the above SQL statement is executed, the network transmission data is reduced from 1010 1010.
Through the change in the above paging method, there is a big improvement in our system.
I have a customer to use the number of Chinese files to 120,000. When we use the first method when searching this table, the page display time is about 10 seconds, and uses the first Three ways and now the page display time is only about 2-3 seconds.
Of course, there may be a better paging method, I always feel that as the data in the database is increasing, the speed of the system will slow down, I posted this article here, just discussed with you, and No better way, I hope everyone will not reply! Discuss together!
: D (End)
