ASP efficient paging algorithm

Generally, two paging pages are used.AlgorithmThe first is the traditional ADO paging, and the second is the select top paging algorithm. For small data tables, such as tables with 10 thousands or 20 thousands of the data volume, I prefer to use the ADO algorithm. For large data tables, the latter algorithm must be used.

Let's talk about the traditional ADO paging algorithm.

This algorithm is easy to use and easy to use. It is the first choice for careful databases, and its execution efficiency is very high. It is also convenient for the database's built-in cursor function to flip pages.

generally, the Code is as follows:
<%
dim recordcountnum, page, I, j
listnum = "30" '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 while not Rs. EOF and I response. write "each record information:" & RS ("ID") & "
"
I = I + 1
Rs. movenext
loop
''page turning code ......
%>

Due to the paging algorithm of ADO cursors, every time a page is loaded, all the data in the data table must be read again. Although the cursor is very simple to use, it can also be used when the data table capacity is not large; however, when the data volume is very large, the efficiency of using this paging method is undoubtedly extremely low.

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

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

'The total number of records read, total number of pages, author's 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's 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 (time) from (select top" & pagesize * (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 while not Rs. EOF
Response. Write "each record information:" & RS ("ID") & "<br>"
Rs. movenext
Loop
Rs. Close
Set rs = nothing
''Page turning code is omitted ......
%>

This is a very efficient paging algorithm. When the data volume in a data table is tens of thousands, the response time of the paging algorithm above is very short, usually within dozens of milliseconds. The principle is very simple, that is, each paging, I only need to take dozens of records, the use of select top is based on this consideration.

In the examples of the two paging algorithms above, flymorn uses the time field for order by sorting, because in most of the systems I work, we all need to display the user's latest updates (including newly added records and newly modified old records) in front. If we only use the ID of the automatic number as the sorting field, the old information edited by the user cannot be displayed. This is why flymorn uses the time field.

This involves the issue of aggregate indexes. By default, we use an automatic ID as the primary key and used as an aggregate index column. If we use this ID column in the above algorithm for sorting, the efficiency will be higher, the database response time will be less; however, I mentioned that recently updated content needs to be displayed in front of the problem, so we must use the time field for sorting. Therefore, for higher paging efficiency, we can design this time field as an aggregate index column during database design.

After this design, the overall paging efficiency will be greatly improved.

However, taking this time field as an aggregate index column has another small problem. Because the data tables are physically sorted by the aggregated index columns during data arrangement, when you add data, there is no problem. Just add the data at the end of the data table; when you edit the information, the database needs to refer the Newly Edited information to the end of the table based on the aggregated index column, which takes some time. That is to say, when we use the time field as the aggregate index column, we need to spend a little more time updating the data.

However, in terms of comprehensive comparison, the author believes that the key to the efficient paging algorithm of select top is to avoid full table scanning and to obtain only the required fields as much as possible. The sorting fields should preferably be clustered index columns, practice shows that the response time of SQL statements sorted by aggregate index columns is the fastest. After such processing, even if the SQL Server database has tens of millions of data records, there is no need to worry that the paging algorithm will lose response.

The above is an algorithm written in the ASP language as an example. Of course, it can also be transformed into other languages such as ASP. NET and PHP. To better use such paging code, you can also rewrite the above algorithm into a stored procedure.