An 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 number of pages, piaoyi notes
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, Piaoyi 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 turn code omitted...
%>
This is a very efficient paging algorithm. When the amount of data in the data table reaches millions, the response time of the above paging algorithm is very short, usually within a few tens of milliseconds. The principle is very simple. Every time I paginate, I only take the dozens of records I need. Using SELECT TOP is based on this consideration.
In the examples of the two paging algorithms above, flymorn uses the time field time to order by sort, because in most systems I have been exposed to, we need to update the user recently (including newly added records and new modifications). The content of the old record (old record) is displayed in the front. If only the automatically numbered ID is used as the sorting field, the old information edited by the user will not be displayed in the front. This is why flymorn uses the time field.
This involves the issue of aggregated indexing. By default, we use the automatic numbered ID as the primary key and use it as the aggregate index column. If we use such ID columns to sort in the above algorithm, it will be more efficient and the database will respond less time; however, I mentioned the question that the recently updated content needs to be shown in the previous one, so we have to use the time field to sort it. Therefore, in order to achieve higher paging efficiency, we can design this time field as an aggregate index column when designing the database.
After such a design, the overall paging efficiency will be greatly improved.
However, there is another minor problem with using this time field as the aggregate index column. Because when the data table is arranged, it is physically sorted according to the aggregate index column. When the user adds the data, there is no problem, just add it at the end of the data table; when the user edits the information, the database needs to be based on This aggregated index column also mentions the newly edited information to the end of the table, which takes a certain amount of time. That is to say, when we use the time field as the aggregate index column, we need to spend a little more time when UPDATE data.
However, in a comprehensive comparison, Piaoyi believes that the key to the efficient paging algorithm of SELECT TOP is to avoid full table scanning and try to obtain only the required fields. The sorted fields are preferably aggregated index columns. Practice shows that aggregated index columns are The response time of the sorted SQL statement is the fastest. After this processing, for the SQL SERVER database, even if there is tens of millions of data, there is no need to worry that the paging algorithm will lose its response.
The above is an algorithm written with the ASP language as an example, and of course it can also be modified into other languages such as ASP.NET and PHP. In order to better use such paging code, you can also rewrite the above algorithm into a stored procedure.
Finally, leave a small question: When SELECT TOP paging, when the page is turned to the end, what will happen if the sorting field column is not the aggregate index column?