An efficient paging algorithm for ASP

In general, there are two kinds of paging algorithms, one is the traditional ADO paging, the other is the select top paging algorithm. For small data tables, such as ten thousand or twenty thousand of the amount of data table, I tend to use the ADO algorithm, for large data tables, you must adopt the latter algorithm.

First, the traditional ADO paging algorithm.

This algorithm, easy to use, easy to start, for the careful database is preferred, its implementation efficiency is very high, the database with the function of the cursor when the page is also very convenient.

The code typically used is as follows:
<%
Dim recordcountnum,page,i,j
LISTNUM = "30" ' shows the number of records per page
Sql= "Select Id,title,time from table1 ORDER by time Desc"
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Rs. Open sql,conn,1,1
If rs.eof and Rs.bof Then
Else
Recordcountnum=rs.recordcount
Rs.pagesize = ListNum
page = Request ("page")
If page = "" or page < 1 Then
Page=1
End If
If (Page-rs.pagecount) > 0 Then
Page=rs.pagecount
End If
Rs.absolutepage=page
J=rs.recordcount
j=j-(page-1) *listnum
I=0
Do but not Rs. Eof and I<listnum
Response.Write per record information: &rs ("id") & "<br>"
I=i+1
Rs.movenext
Loop
' Flip the code slightly ...
%>

The paging algorithm of this ADO cursor, the use of this paging method is extremely inefficient when the data is very large, because the data table's full data will be re-read every time the page is loaded, although the cursor is used very simply and can be used when the data table is small in size.

So, we need to introduce another efficient select top paging algorithm. The code is as follows:

<%
' Number of records per page
Dim pagesize
Pagesize= "30"

' Read the total number of records, total pages, author note
Dim totalrecords,totalpages
Sqlstr= "Select count (ID) as recordsum from table1"
Set Rs=conn. Execute (sqlstr,0,1)
Totalrecords=rs ("Recordsum")
If Int (totalrecords/pagesize) =totalrecords/pagesize Then
Totalpages=totalrecords/pagesize
Else
Totalpages=int (totalrecords/pagesize) +1
End If
Rs.close
Set rs=nothing

' Current page number, author note
Dim page
Page=request ("page")
If IsNumeric (page) =false Then
Response.Write "<script language=javascript>alert (' parameter Error! ');"
Response.Write "Window.close ();</script>"
Response.End
End If
If page= "" or page<1 Then page=1
If page-totalpages>0 Then page=totalpages
Page=int (page)

If Page=1 Then
Sql= ' select top ' &pagesize& ' id,title,time from table1 ORDER by time Desc '
Else
Sql= ' select top ' &pagesize& ' id,title,time from table1 where time< (select Min. from (select top) &page Size* (page-1) & "time from table1 ORDER by time Desc" as T) Order by time Desc "
End If
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Rs. Open sql,conn,1,1
Do but not Rs. Eof
Response.Write per record information: &rs ("id") & "<br>"
Rs.movenext
Loop
Rs.close
Set rs=nothing
' Page-flipping code omitted ...
%>

This is a very efficient page-splitting algorithm. When the data in the data table is hundreds of millions of times, the response time for this paging algorithm is very short, usually within dozens of milliseconds. The principle is very simple, that is, every paging, I only take the required dozens of records, the use of select top is based on this consideration.

In the example of the two pagination algorithms above, Flymorn uses time field times to sort by, because in most of the systems I've contacted, we need to show the user's recent updates (including newly added records and newly modified old records) in the front, If you only use an AutoNumber ID as the sort field, the old information that the user edits will not be displayed in front of you. That's why Flymorn uses the Time field.

This also involves the problem of aggregating indexes. By default, we are using an AutoNumber ID as the primary key, and as an aggregation index column, if the above algorithm uses such an ID column to sort, the efficiency will be higher, the database response time will be less; however, I mentioned that the recently updated content needs to be shown in front of the question, so, We must use the Time field to sort. Therefore, for a higher paging efficiency, we can design this time field as an aggregated indexed column when designing the database.

Through this design, the entire paging efficiency will be very high increase.

However, there is another small problem with this time field as an aggregated indexed column. Because the data table is in the arrangement of data, is sorted according to the aggregated indexed columns, when the user adds the data, there is no problem at the end of the datasheet; When the user edits the information, the database needs to follow the aggregated index column, and the information that has just been edited is also mentioned at the end of the table. It's going to take some time. That is, when we use the Time field to aggregate indexed columns, we need to spend a little more time on the UPDATE data.

However, in general comparison, the author believes that the key to the efficient paging algorithm of SELECT top is to avoid full table scanning, as far as possible only to get the required fields, sorted fields preferably aggregated indexed columns, practice shows that the aggregated indexed columns to sort the SQL statement response time is the fastest. After this processing, for the SQL Server database, even tens of millions of data, there is no need to fear the paging algorithm has lost its response.

The above is an example of an ASP language to write algorithms, of course, can also be transformed into other such as asp.net,php language used. In order to better use such paging code, you can also rewrite the above algorithm into a stored procedure.