foxty [original work]
has recently been studying how to write a high-small paging algorithm. I have sorted it out and the ideas are as follows:
First, there needs to be an automatic numbering field (ID) in the database. Then on the first visit, take out all the records, customize the number of records on each page PageSize, calculate the number of pages, and then create a one-dimensional array PageId (PageCount) based on the number of pages. PageId (0) saves the initial test conditions of the record, and then Corresponding to each element, save the ID boundary code corresponding to 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
Assuming that you need to sort by ID, PageSize = 5, Pagecount = 4, PageId(4)
The values of the array PageId are PageId(0) = 1, PageId(1) = 5, PageId(2) = 10, PageId(3) = 15, PageId(4) = 16
When accessing the i-th page, directly search for the records between [PageId(i-1), PageId(i)). This ensures that only PageSize records are retrieved each time.
Suppose you need to sort by ID in reverse order,
The values of the array PageId are PageId(0) = 16, PageId(1) = 12, PageId(2) = 7, PageId(3) = 2, PageId(4) = 1. When accessing the i-th page, directly Find ID belonging to [ PageId(i-1) , PageId(i) )
)
Save the array PageId() in Application() for easy access, so that Application() is only initialized the first time the pager is accessed. The code part is as follows: (hereinafter referred to as the 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.downcodes.com
Dim Page,PageCounts,PageId,PageList
Dim Rs,Sql
Dim IsInit,i
IsInit = False 'The flag is, used to determine whether Application ("PageId") is initialized
PageList = 20 'Set 20 pieces of data to be displayed on each page
Set Rs = Server.CreateObject("Adodb.Recordset")
Page = Request.QueryString("Page") 'Note that the page number needs to be checked for type
If IsEmpty(Application("PageId")) Then 'If Application("PageId") has not been initialized yet, initialize it first
Response.Write("Init app!<br>")
Sql = "Select * From test Order By Id Desc" 'Assume that this is sorted in reverse order by ID
Rs.open Sql,Conn,1,1 'Get the recordset 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) 'Redefine the array PageId
For i = 0 To PageCounts 'Start assigning values to the 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
SetConn=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)};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 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 global array of Application("PageId"), and each element saves the ID range of the record in the page. 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 to access the i-th page, just look for the ID directly in [Application("PageId")(i- 1) , Application("i") ) In this way, you only need to search the required number of records each time, instead of searching all the records every time. However, this method is used on the first access When the array Application("PageId") needs to be created, it is a little slower. When accessed for the Nth time (N>1), the speed is nearly 10 times faster. I used the above two programs to test:
1. There are 32,000 records in the database. The old program takes about 500 milliseconds to access one page. The new program only reaches this time during the first access, and then only takes about 55 milliseconds each time.
2. Increase the data to 64,000 records. The old program takes about 1,000 milliseconds to access one page. The new program also reaches this level when it first accesses it. It still remains at about 55 milliseconds every time thereafter.
3. Increase the data to 128,000 records. The old program takes about 1900 milliseconds to access one page, the new program takes about 2300 milliseconds to access one page for the first time, and then each access only takes about 70 milliseconds.
What needs to be noted here is that every time the database is modified, Application("PageId") needs to be reassigned!
Research experience: (First of all, thank you Ye Zi (DVBBS) for your experience) Try not to use the built-in paging program, Rs.RecordCount is very resource-consuming. In turn, it is estimated that Rs.PageCount... also consumes resources, and the effect of using Rs.GetRows() is also significantly improved.
After comparison, the speed and efficiency of the leaf algorithm are relatively high when the records are relatively high. But it's not very stable, sometimes (rarely) it jumps from around 30ms to 1-200ms. Afterwards, the efficiency drops significantly to 50-80 milliseconds, and the later the efficiency becomes lower. The efficiency of the new algorithm is relatively low for the first time, about 500 milliseconds, but it is relatively stable. Later, it is generally about 50 milliseconds, and as the number of records in the library changes, this speed remains the same. Nothing will change. Next time I will try combining Ye Ye with my algorithm, but Ye Ye's algorithm is indeed very good and versatile. I can only use it to chat.