How to quickly optimize paging in ASP

I have been studying how to write a small and high paging algorithm recently. I have summarized the following ideas:
 
First, an automatic Id field is required in the database ). Then, during the first access, all the records are taken out, and the pagesize of each page is customized to calculate the number of pages. Then, a one-dimensional array pageid (pagecount) and pageid (0) are created based on the number of pages) save the record test conditions, and then save the ID boundary code for each page for each element.
(
1. ID boundary code: If the sequence of the database record IDS is as follows: 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16
For example, pagesize = 5, pagecount = 4, pageid (4)
The values of the array pageid are respectively pageid (0) = 1, pageid (1) = 5, pageid (2) = 10, pageid (3) = 15, pageid (4) = 16
When you access page I, You can directly find records between [pageid (I-1), pageid (I) to ensure that each retrieved record is only a pagesize record.
If you want to sort the IDs in reverse order,
The values of the array pageid are respectively pageid (0) = 16, pageid (1) = 12, pageid (2) = 7, pageid (3) = 2, pageid (4) = 1, when you access page I, You can directly find that the ID belongs to [pageid (I-1), pageid (I ))

)
Save the array pageid () in application () for access. In this way, only application () is initialized when the paging program is accessed for the first time (). The Code is as follows: (this is called a new program)

<%
Time1 = timer ()
Dim Conn
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("DB. mdb ")
'Www .knowsky.com
Dim page, pagecounts, pageid, pagelist
Dim RS, SQL
Dim isinit, I
 
Isinit = false' indicates whether the application ("pageid") is initialized.
Pagelist = 20' set 20 data entries per page
Set rs = server. Createobject ("ADODB. recordset ")
Page = request. querystring ("page") 'check the page number type.

If isempty (Application ("pageid") then' if application ("pageid") has not been initialized, initialize
Response. Write ("init app! <Br> ")
SQL = "select * from test order by ID DESC" 'it is assumed that the order is in descending order of IDs.
Rs. Open SQL, Conn, 'Get Record set object

If not (Rs. EOF or Rs. bof) then
Rs. pagesize = pagelist 'set the number of records per page
Pagecounts = Rs. pagecount
Redim pageid (pagecounts) 'redefines the array pageid
For I = 0 to pagecounts ', the array pageid () is assigned.
If Rs. EOF then exit
Pageid (I) = RS ("ID ")
Rs. Move (pagelist)
Next
Rs. movelast
Pageid (pagecounts) = RS ("ID ")
Application. Lock ()
Application ("pageid") = pageid
Application. Unlock ()
End if
Rs. Close
End if
Idstart = clng (Application ("pageid") (page-1 ))
Idend = clng (Application ("pageid") (page ))
SQL = "select * from test where ID <=" & idstart & "and ID>" & idend &""
Rs. Open SQL, Conn, 1, 1
While not Rs. EOF
Response. Write (RS (0) & "--" & RS (1 ))
Rs. movenext
Wend
Rs. Close
Set rs = nothing
Conn. Close
Set conn = nothing
 
 
For I = 1 to ubound (Application ("pageid "))
Response. Write ("<a href = http://www.dvbbs.net/tech/asp/'test1.asp? Page = "& I &" '> "& I &" </a> ")
Next
Time2 = timer ()
 
Response. Write ("<br>" & (Time2-Time1) * 1000)
'Application. Contents. Remove ("pageid ")
%>

The traditional paging code is as follows: (the old program is called below)
<%
Time1 = timer ()
Dim Conn
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("DB. mdb ")
 
Dim page, pagecounts, pagelist
Dim RS, SQL
 
Pagelist = 20
Page = request. querystring ("page ")
Set rs = server. Createobject ("ADODB. recordset ")
SQL = "select * from test order by ID DESC"
Rs. Open SQL, Conn, 1, 1
 
If page = "" Then page = 1
If not (Rs. EOF or Rs. bof) then
Rs. pagesize = pagelist
Pagecounts = Rs. pagecount
Rs. absolutepage = page
End if
 
For I = 1 to pagelist
If Rs. EOF then exit
Response. Write (RS (0) & "-----" & RS (1) & "<br> ")
Rs. movenext
Next
 
For I = 1 to pagecounts
Response. Write ("<a href = http://www.dvbbs.net/tech/asp/'test.asp? Page = "& I &" '> "& I &" </a> ")
Next
Time2 = timer ()
 
Response. Write ("<br>" & (Time2-Time1) * 1000)
%>
 
In fact, the general idea is to create a global array of application ("pageid"), each element stores the ID range of the records in the page, for example, application ("pageid ") (0) Save the ID of the first element, and then application ("pageid") (1) Save the first ID of the next page ............ And so on, when you need to access page I, You can directly find the record set ID in [Application ("pageid") (I-1), application ("I, in this way, you only need to query the number of required records each time, instead of checking all records every time. However, this method is used for the first access, that is, it takes a little longer to create an Array application ("pageid"). When the n-times of access (n> 1), the speed is nearly 10 times faster, I used the above two programs for testing:
1. There are 32000 records in the database. It takes about 500 milliseconds for the old program to access a page. It takes about 55 milliseconds for the new program to access the page for the first time.
2. Add data to 64000 records. It takes about 1000 milliseconds for the old program to access a page. This is also the first time the new program accesses the page, the value remains at least 55 milliseconds later.
3. Increase the data to 128000 records. It takes about 1900 milliseconds for the old program to access a page, about 2300 milliseconds for the first access of the new program, and about 70 milliseconds for each access.
Note that every time the database is changed, the application ("pageid") needs to be assigned a value again!

Research tips: (first of all, thanks to Ye (dvbbs) Try not to use the built-in paging program. Rs. recordcount consumes a lot of resources. Estimate the order of Rs. pagecount ...... Resources are also consumed, and Rs. getrows () is also significantly improved.
 
After comparison, the speed and efficiency of the leaf algorithm are relatively high when the record is relatively high. But it is not stable. Sometimes (very few) It will jump from about 30 milliseconds to 1-milliseconds. After that, the efficiency will be significantly reduced to 50-80 milliseconds, and the lower the efficiency. The efficiency of the new algorithm is relatively low for the first time, which is about 500 milliseconds, but relatively stable. The latter is generally around 50 milliseconds. This speed remains the same as the number of records in the database changes. There will be no changes. Next time, we will try to combine the leaf algorithm with my algorithm. However, the leaf algorithm is really good D and versatile. I can only talk about this.