Best database paging Method

Best database paging Method

I. A conventional Problem

We know that record set paging is a very common problem in database processing. When we design a network database, that is, to consider the transmission bandwidth problem, the paging problem is always plagued by every databaseProgramDesign personnel.

Ii. Solutions to paging Problems

Speaking of the solution, each database designer may cite many methods. However, it can be classified into three categories. I. Ado record set paging; II. dedicated storage record set Paging

Iii. Database cursor Paging

I. Paging of the famous ADO record set.

It is famous because it may be the simplest and most common paging method. (Probably the most commonly used) is to use the paging function provided by ADO to implement paging.

The specific process is that the database returns a complete record set based on the query statement. After arriving at the client, there will be a client cursor paging. Most of them are implemented by ADO's built-in recordset object. The following attributes may be involved:

Recordset. pagesize: the size of each page's output record set

Recordset. absolutepage: current output page (with the above two attributes, the page output can be completed)

Recordset. pagecount: current total number of pages

This method is good. Some people say it is very good, and others say it is not efficient. In fact, this is determined based on the actual application situation. If it is a single-host database, or a LAN environment, or the database has a small number of records, it is a good paging method, in addition, if it does not involve networks and environments with few updates, it can be said that it is the best paging method. Because it can be converted into a cache record set, you can retrieve the records of several pages in the future without using the database. However, if the network is involved or the update is frequent. He is not very practical.

List 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 the connection field and has been defined)

Rs. pagesize = 20' current page size

If CINT (nowpage)> Rs. pagecount then nowpage = Rs. pagecount

Rs. absolutepage = nowpage

'Then output the record of the current page

'.............

You can also set program properties.

Homepage: nowpage = 1

Front: nowpage = nowpage-1

Next page: nowpage = nowpage + 1

Last page: nowpage = Rs. pagecount

Total records: Rs. recordcount

Total number of pages: Rs. pagecount

Ii. Paging of the dump record set.

This method was born in the Internet era. It uses the powerful processing process on the server to first store the target database in a temporary database and add an auto-incrementing field to divide the page, finally, return the required fixed number of records.

Advantage: Only one interaction is required and a fixed one-page record set is returned.

The disadvantage is that when the record set increases, a temporary record set needs to be created each time, which also consumes time, but reduces the amount of network transmission.

Example:

(

<Professional Active Server Pages 3.0>

Isbn1861002610

The key point is that I have translated Chinese.

)

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 a temporary table.

Create Table # pagedauthors (

-- This auto-increment field is critical, that is, it is used to mark pages.

Id int identity,

Au_id varchar (11) not null,

Au_lname varchar (40) not null,

Au_fname varchar (20) not null,

Phone char (12) not null,

Address varchar (40) null,

City varchar (20) null,

State char (2) null,

Zip char (5) null,

Contract bit not null

)

-- Populate the temporary table

-- First, it is transferred to the above record set.

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 in 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 that fall within our page

-- This SQL statement selects a fixed record set.

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 on record counts

Set nocount off

-- Return the number of records left

Return @ ipagecount

End

The output end can be output in sequence using the fastest type of Ado "FireWire cursor ".

<%

Dim authentication authors

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 primary authors = server. Createobject ("ADODB. Command ")

Set rsauthors = server. Createobject ("ADODB. recordset ")

With login authors

. Activeconnection = strconn

. Commandtext = "usp_pagedauthors"

. Commandtype = adcmdstoredproc

. Parameters. append. createparameter ("return_value", adinteger ,_

Adparamreturnvalue)

. Parameters. append. createparameter ("@ ipage", adinteger ,_

Adparaminput, 8, ipage)

. Parameters. append. createparameter ("@ ipagesize", adinteger ,_

Adparaminput, 8, 10)

Set rsdata =. Execute

End

'Create the table

'Start building the table

Response. Write "<Table border = 1> <thead> <tr>"

For each fldf in rsdata. Fields

Response. Write "<TD>" & fldf. Name & "</TD>"

Next

Response. Write "</tr> </thead> <tbody>"

'Now loop through the records

While not rsdata. EOF

Response. Write "<tr>"

For each fldf in rsdata. Fields

Response. Write "<TD>" & fldf. Value & "</TD>"

Next

Response. Write "</tr>"

Rsdata. movenext

Wend

Response. Write "</tbody> </thead> </table> <p>"

'Now some paging controls

SME = request. servervariables ("script_name ")

Response. Write "<a href =" & squote & SME &"? Page = 1 "& squote &"> first page </a>"

'Close the recordset and extract the number of records left

Rsdata. Close

Ilastpage = login authors. parameters ("return_value ")

'Only give an active previous page if there are previous pages

If ipage <= 1 then

Response. Write "<span> previous page </span>"

Else

Response. Write "<a href =" & squote & SME &"? Page = "& ipage-1 & squote &"> previous page </a>"

End if

'Only give an active next page if there are more pages

If ilastpage = ipage then

Response. Write "<span> next page </span>"

Else

Response. Write "<a href =" & squote & SME &"? Page = "& ipage + 1 & squote &"> next page </a>"

End if

Response. Write "<a href =" & squote & SME &"? Page = "& ilastpage & squote &"> last page </a>"

'Clean up

Set rsdata = nothing

Set primary authors = nothing

%>

Method 3: select a record set for the server-side game table.

This method is highly controversial.

It selects a record set using a game table on the server, and then returns multiple record sets at a time. Each record set has a record. Then, the recordset. nextrecord method is used to output each record set.

Many foreign websites have verified this because the first is recordset. nextrecord has ADO. the game table is not the fastest FireWire game table. Second, many people think that recordset is used. the nextrecord method is equivalent to an interaction with the server, so this method is the kind of method that can multiply the access volume of the database when many people access the database concurrently...



Listing procedures: (Author: bigeagle)

If exists (select * From sysobjects where id = object_id ("up_topiclist "))

Drop proc up_topiclist

Go

Create proc up_topiclist

@ A_forumid int, @ a_intdays int, @ a_intpageno int, @ a_intpagesize tinyint

As

Declare @ m_intrecordnumber int

Declare @ m_intstartrecord int

Select @ m_intrecordnumber = @ a_intpagesize * @ a_intpageno

Select @ m_intstartrecord = @ a_intpagesize * (@ a_intpageno-1) + 1

If @ a_intdays = 0 -- if the number of days is not limited

Begin

/* Calculate the number of qualified records */

Select "recordcount" = count (*)

From BBS where layer = 1 and forumid = @ a_forumid

/* Output record */

/* Define the scroll cursor first */

Set rowcount @ m_intrecordnumber

Declare m_curtemp scroll cursor

For

Select a. ID, A. Title, D. username, A. faceid,

'Contentsize' = datalength (A. content ),

'Totalchilds '= (select sum (totalchilds)

From BBS as B

Where a. rootid = B. rootid ),

'Lasttime' = (select max (posttime)

From BBS as C

Where a. rootid = C. rootid)

From BBS as

Join bbsuser as D on A. userid = D. id

Where layer = 1 and forumid = @ a_forumid

Order by rootid DESC, layer, posttime

Open m_curtemp

Fetch absolute @ m_intstartrecord from m_curtemp

While @ fetch_status = 0

Fetch next from m_curtemp

Set rowcount 0

/* Clear the scene */

Close m_curtemp

Deallocate m_curtemp

End



Else -- if the number of days is limited

Begin

/* Calculate the number of qualified records */

Select "recordcount" = count (*)

From BBS where layer = 1 and forumid = @ a_forumid

And dateadd (day, @ a_intdays, posttime)> getdate ()

/* Output record */

/* Define the scroll cursor first */

Set rowcount @ m_intrecordnumber

Declare m_curtemp scroll cursor

For

Select a. ID, A. Title, D. username, A. faceid,

'Contentsize' = datalength (A. content ),

'Totalchilds '= (select sum (totalchilds)

From BBS as B

Where a. rootid = B. rootid ),

'Lasttime' = (select max (posttime)

From BBS as C

Where a. rootid = C. rootid)

From BBS as

Join bbsuser as D on A. userid = D. id

Where layer = 1 and forumid = @ a_forumid

And dateadd (day, @ a_intdays, posttime)> getdate ()

Order by rootid DESC, layer, posttime

Open m_curtemp

Fetch absolute @ m_intstartrecord from m_curtemp

While @ fetch_status = 0

Fetch next from m_curtemp

Set rowcount 0

/* Clear the scene */

Close m_curtemp

Deallocate m_curtemp

End

Go

Note: If the command object for calling the stored procedure in ASP is cm, set rs1_cm.exe cute and use set rs = Rs. nextrecordset to retrieve the next record.

Iii. Test results.

We can see so many paging methods. So what is the best paging method? Perform a test.

Test Tool: Microsoft Web application stress tool 1.1

Test Platform: Win2000 Server Chinese edition + iis5.0 + SQL Server 7.0

Data Records: 8000 records (non-identical stock history records)

Simulation Environment: 56 K model/2 m leased line/10 MB leased line

Number of tests: 3

Test result: Server cursor >== stored procedure page> ADO page

(Symbol >=: indicates that it is basically the same, but sometimes slightly larger. symbol >>> is far greater)

It seems that there is little difference between the two, and the server-side cursor is slightly larger than the stored procedure in multiple record sets. But it is much more efficient than ADO cursor paging.

So what is the best paging method? What is our ideal paging method?

In fact, the client passes a page number, and the server directly generates a required page record set through one query, and returns it to the client in the form of a record set.