Tips on how to optimize pagination quickly in ASP

Foxty [Original]

Recently has been studying how to write a primary page of the paging algorithm, probably sorted out, the idea is as follows:

First, an AutoNumber field (ID) is required in the database. Then on the first visit, take out all the records, customize the number of records per page pagesize, calculate the pages, and then build a one-dimensional array based on the pages PageID (PageCount), PageID (0) to save the initial conditions, Each element is then saved with the corresponding ID boundary code for each page.
（
1,id Boundary Code: If the database record ID record sequence is as follows 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
Suppose you need to sort by id order, PageSize = 5, PageCount = 4, PageID (4)
The values of the array PageID are PageID (0) = 1, PageID (1) = 5, PageID (2) = 10,pageid (3) =, PageID (4) = 16
When you visit page I, find the records between [PageID (I-1), PageID (i)) so that you can ensure that every record you take is only a pagesize record.
Assuming that you need to sort by the ID in reverse order,
The values of the array PageID are PageID (0) =, PageID (1) = one, PageID (2) = 7, PageID (3) = 2, PageID (4) = 1, when the page i is accessed directly lookup ID belongs to [PageID (i -1), PageID (i))

）
Save the array PageID () in application () for access so that the application () is initialized the first time the paging program is accessed. The code section is as follows: (hereinafter referred to as a new program)

<%
Time1 = Timer ()
Dim Conn
Set Conn = Server.CreateObject ("Adodb.connection")
Conn.Open "Driver={microsoft Access Driver (*.mdb)};D bq=" &server.mappath ("Db.mdb")
' Www.knowsky.com
Dim page,pagecounts,pageid,pagelist
Dim Rs,sql
Dim Isinit,i

Isinit = False ' flags to determine whether application ("PageID") is initialized
PageList = 20 ' Set display 20 data per page
Set Rs = Server.CreateObject ("Adodb.recordset")
page = request.querystring ("page") ' note page number needs to check type

If IsEmpty (Application ("PageID")) Then ' If Application (' PageID ') has not been initialized, initialize first
Response.Write ("Init app!<br>")
SQL = "SELECT * from test ordered by ID Desc" ' assumes that this is sorted by ID reverse order
Rs.Open sql,conn,1,1 ' Get Recordset object

If not (rs.eof or RS.BOF) Then
Rs.pagesize = PageList ' Sets the number of records per page
Pagecounts = Rs.pagecount
ReDim PageID (pagecounts) ' Redefine array PageID
For i = 0 to pagecounts ' starts assigning values to array PageID ()
If rs.eof Then Exit for
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= ' 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: (hereinafter referred to as the old program)
<%
Time1 = Timer ()
Dim Conn
Set Conn = Server.CreateObject ("Adodb.connection")
Conn.Open "Driver={microsoft Access Driver (*.mdb)};D bq=" &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 for
Response.Write (Rs (0) & "-----" &rs (1) & "<br>")
Rs.movenext
Next

For i = 1 to pagecounts
Response.Write ("<a href= ' test.asp?") Page= "&i&" ' > "&i&" </a> ")
Next
Time2 = Timer ()

Response.Write ("<br>" & (time2-time1) *1000)
%>

In fact, the overall idea is to create a application ("PageID") global array, where each element saves the ID interval of the page's area record, for example, application ("PageID") (0) Saves the ID of the first element, Then application ("PageID") (1) Saves the first ID of the next page ... And so on, when you need access to page I, simply look for the recordset in [Application ("PageID") (i-1), Application ("I"), so that you can only find the number of records you need each time, without having to look up all the records every time , however, this method is the first time access, that is, the need to create an array of application ("PageID") is relatively slow, when the nth visit (n>1) speed nearly 10 times times, I use the above 2 program test:
1, the database record has 32,000 records, the old program accesses a page to need 500 milliseconds, the new program only then achieves this time in the first visit, then each time only then needs 55 milliseconds.
2, to increase the data to 64,000 records, the old program access to a page needs about 1000 milliseconds, the new program is also the first time to visit this seems to be a piece, the next time still remain in 55 milliseconds or so.
3, to increase the data to 128,000 records, the old program access to a page needs about 1900 milliseconds, the new program first access needs about 2300 milliseconds, and then each visit only need about 70 milliseconds.
The note here is that every time the database changes, application ("PageID") will need to be assigned a value!

Research experience: (First of all, thank the leaves (Dvbbs) of the experience) as far as possible not to use their own paging program, Rs.recordcount very resource consumption. In turn, estimate rs.pagecount ... Also consumes resources, and with rs.getrows () The effect is also obviously improved.

After comparison, the leaf algorithm is relatively high in the record speed and efficiency. But it's not stable, and sometimes (rarely) it jumps from about 30 milliseconds to 1-200 milliseconds. At the back, the efficiency is significantly reduced to 50-80 milliseconds, and the lower the efficiency. The new algorithm is inefficient for the first time, about 500 milliseconds, but relatively stable, the back is generally 50 milliseconds, and as the record number of libraries changes, this speed is still the case. There will be no change. Next time the leaf and my algorithm combined to try, but the leaf algorithm is really good d, with versatility. I can only talk about this.