Best approach to database paging

Pagination | data | Database One: a cliché question.
As we know, Recordset paging is a very common problem in database processing. And when we design the network database, that is to consider the transmission bandwidth problem, the paging problem often puzzles every database program designers.

Solution Rollup for paging problems
As a solution, each database designer might cite a number of methods. However, after subdivision, it can be grouped into three categories. One: ADO record set paging, two: storage set paging, three, database cursor paging.
One: The famous ADO record set paging.
When it comes to the famous, it may be the simplest and most common method of paging. (probably the most used) is the use of ADO with the paging function to achieve pagination.
The specific process is that the database returns a complete set of records according to the query statement. Then there is a client-side cursor for paging after the client. Most of them can be implemented by the Recordset object that is brought by ADO. The attributes that may be involved are:
Recordset.pagesize: The size of the output record set per page
Recordset. AbsolutePage: The current output of the page (with more than two properties can actually complete paging output)
Recordset.pagecount: Current total number of pages.
This method is good, some people say is very good, others say efficiency is not high. In fact, according to the actual application of the situation to determine, if it is a stand-alone database, or LAN environment, or a small database record, then he is a good pagination method, and if not related to the network and update less environment, then he can be said to be the best paging method. Because it can be a cached recordset, the next few pages of records can not be taken from the database. However, if the network is involved, or if the update is frequent. He is not very practical.

Enumerate programs. (We all use network issues to consider)
　
Nowpage=request ("Nowpage") ' Current output page
If nowpage= "" or nowpage<1 then nowpage=1

Set Rs=server. CreateObject ("Adodb.recordset")
Rs. Cursortype=1
Sql= "SELECT * FROM table1"
Rs.Open Sql,strconn (strconn is a connection field, defined)

Rs. Pagesize=20 ' the size of the current page
If CInt (nowpage) >rs.pagecount then Nowpage=rs.pagecount
Rs. Absolutepage=nowpage

' and then output the current page's record
'.............


You can also provide program property settings.
Home: nowpage=1
Front Page: nowpage=nowpage-1
Next page: nowpage=nowpage+1
Last page: Nowpage=rs.pagecount
Total record number: Rs.recordcount
Total Pages: Rs.pagecount

Two: Dump record set paging.
This method was born in the network era, is the use of server-side powerful processing process, the target data inventory to a temporary database, and add a self-added field to divide the page, and finally the need for a fixed number of records set back.

The advantage is that you only need to interact once and return a set of records for a fixed page.
The disadvantage is: if the record set increases, it is necessary to establish a temporary record set each time, but also more time-consuming, but reduced network traffic.
Example:

（
From Worx English version of <<professional Active Server Pages 3.0>>
ISBN1861002610
The key place I have made the Chinese translation
）
CREATE PROCEDURE usp_pagedauthors
@iPage int,
@iPageSize int
As
BEGIN
--Disable row counts
SET NOCOUNT on

--Declare variables
DECLARE @iStart INT--Start record
DECLARE @iEnd INT--end record
DECLARE @iPageCount INT--Total number of pages

--Create the temporary table
--Create temporary tables.

CREATE TABLE #PagedAuthors (
This self-added field is crucial to the completion of the paging label.
ID int IDENTITY,
au_id varchar (one) is not NULL,
au_lname varchar () not NULL,
au_fname varchar is not NULL,
Phone char (not NULL),
Address varchar (=) NULL,
City varchar (a) NULL,
State char (2) NULL,
Zip char (5) NULL,
Contract bit not NULL
)

--Populate the temporary table
--First to the record set above.
INSERT into #PagedAuthors (au_id, au_lname, au_fname,
Phone, address, city, state, zip, contract
SELECT au_id, au_lname, au_fname,
Phone, address, city, state, zip, contract
From authors


--Work out how many pages there are at total
SELECT @iPageCount = COUNT (*)
From authors

SELECT @iPageCount = CEILING (@iPageCount/@iPageSize) + 1

--Check the page number
IF @iPage < 1
SELECT @iPage = 1

IF @iPage > @iPageCount
SELECT @iPage = @iPageCount

--Calculate the start and End Records
SELECT @iStart = (@iPage-1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1

--Select only those records so fall within our page
-The SQL statement is to select a fixed set of records.

SELECT au_id, au_lname, au_fname,
Phone, address, city, state, zip, contract
From #PagedAuthors
WHERE ID > @iStart
and ID < @iEnd


DROP TABLE #PagedAuthors

--Turn back in record counts
SET NOCOUNT off

--Return the number of records left
Return @iPageCount
End


The output can be used as the fastest type of ADO "FireWire cursor" output can be
<%
Dim cmdauthors
Dim Rsdata
Dim IPage
Dim Ilastpage
Dim Squote

Squote = Chr (34)

' Get the requested data
If request.querystring ("PAGE") = "" Then
IPage = 1
Else
IPage = CInt (Request.QueryString ("PAGE"))

If IPage < 1 Then
IPage = 1
End If
End If

' Create the objects
Set cmdauthors = Server.CreateObject ("Adodb.command")
Set rsauthors = Server.CreateObject ("ADODB. Recordset ")

With Cmdauthors
. ActiveConnection = strconn
. CommandText = "Usp_pagedauthors"
. CommandType = adCmdStoredProc

. Parameters.Append. CreateParameter ("Return_value", adinteger, _
adParamReturnValue)
. Par