Theoretically the fastest Web Database paging Method

This article describes the best paging method in theory.

I. concept.
When designing a Web database, if we want to edit each record, we can only use the paging mode to make the Web database as soon as possible and present it to end users, this will not cause users to lose interest in browsing the page because of the 8-second principle. However, even if we adopt the paging method, when there is a database with multiple records, it will inevitably make our users feel that the page turning speed is too slow. As I mentioned in my previous article, almost all three paging methods in the previous article have some defects. So, how can we make the database take the records we need each time? This is a good implementation, and it can be achieved by returning multiple records sets with a cursor, however, it would be difficult to make the database end consume resources because it just needs to retrieve one page of records. Finally, after constantly writing programs and tests, I finally compiled the fastest paging Method for Web databases in theory.

II. Specific stored procedures.
Let's talk about this method based on a BBS question. How can we make each page of a BBS only need one page of record? What parameters do we need to provide to the database? The following parameters may exist.
First, we need the current page number.
Second, the number of records on each page defined currently. In this way, you can modify the number of records on each page in the page program as needed. Of course, if you do not consider program scalability, you can also specify N records for each page in the database.
Third, an output parameter is used to obtain the total number of records in the current table from the database. (Note that he is not the number of records on a page) He is equivalent to Recordcount In ADO paging method. If you do not need the total number of records, you do not need to return them.
Let's look at the specific stored procedure code...

Create procedure dbo. PRO_pageview
(

@ Tint_tableid tinyint = 1, -- this is the Id of the current page of BBS ..
@ Int_pagenow int = 0,
@ Int_pagesize int = 0,
@ Int_recordcount int = 0 output -- gets the total number of posts on a page of BBS ..

)

AS
Set nocount on

Declare @ int_allid int
Declare @ int_beginid int, @ int_endid int
Declare @ int_pagebegin int, @ int_pageend int
　　
Select @ int_allid = count (*) from tab_discuss where tint_level = 0 and tint_tableid = @ tint_tableid
Select @ int_recordcount = @ int_allid -- obtains the total number of stickers for the layout.
　　　　　　
Declare cro_fastread cursor scroll
For select int_id from tab_discuss where tint_level = 0 and tint_tableid = @ tint_tableid order by int_id desc -- the cursor operation is defined here, but no temporary record set is required, in addition, the cursor does not need to traverse all record sets.
　　
Open cro_fastread -- open the cursor
Select @ int_beginid = (@ int_pagenow-1) * @ int_pagesize + 1 get the first record Id of the page
Select @ int_endid = @ int_beginid + @ int_pagesize-1 gets the Id of the last record of the page
　　
Fetch absolute @ int_beginid from cro_fastread into @ int_pagebegin pass his Id to the Id at the beginning of the page
If @ int_endid> @ int_allid -- Note that if a page does not have a fixed number of records. If there is only one page of records, and the number of records is less than what we define. Or the last page...
Fetch last from cro_fastread into @ int_pageend -- directly positions the cursor to the last record to obtain its ID number...
Else
Fetch absolute @ int_endid from cro_fastread into @ int_pageend
　　　　　　　　
Select int_id, tint_level, tint_children, var_face, var_subject, datalength (txt_content) as int_len, sint_hits, var_url, var_image, var_user, dat_time, tint_tableid, bit_kernul from tab_discuss where tint_tableid = @ tint_tableid and int_rootid between @ int_pageend and @ int_pagebegin order by int_rootid desc, num_order desc -- we can use the first id and the last id of the page to get the intermediate id .... (Note. We use a clever algorithm for the number structure of this BBS, that is, using an orderNum floating point number to complete sorting ...)

-- Start cleaning...
Close cro_fastread
Deallocate cro_fastread
　　
Return

Let's take a look at the program operations on the Asp page...

Pagenow = cint (request ("pagenow") -- current page.

If pagenow <= 0 then pagenow = 1
Pagesize = 10

Set cmd = server. CreateObject ("adodb. command ")
Cmd. ActiveConnection = strconn
Cmd. CommandType = 4
Cmd. CommandText = "pro_pageview"

Cmd. Parameters. Append cmd. CreateParameter ("tint_tableid", adInteger, adParamInput, tint_tableid)
Cmd. Parameters. Append cmd. CreateParameter ("int_pagenow", adInteger, adParamInput, pagenow)
Cmd. Parameters. Append cmd. CreateParameter ("int_pagesize", adInteger, adParamInput, pagesize)
Cmd. Parameters. Append cmd. CreateParameter ("int_recordcount", adInteger, adParamOutput)

Set rs = cmd. Execute
If rs. eof then
Response. Write "the number of records has exceeded or the record set is empty! "
Response. End
End if

Dim arrRs
ArrRs = rs. getrows can be used to quickly save the record set to a two-dimensional array to improve the speed.

Recordcount = cmd. Parameters ("int_recordcount ")
'Note: when the record is not enough to just remove the Unit page record, we also need to define it as one page. For example, if the record number is two pages and one more record, at this time, our page number should also be a 3-page record.
If (recordcount mod pagesize) = 0 then
Pagecount = recordcount \ pagesize
Else
Pagecount = recordcount \ pagesize + 1
End if

<-- Start by page -->
<! -- # Include file = "include \ tablepage. asp" --> fixed paging function, in fact, nothing more than pagenow + 1 or pagenow-1, pagenow, pagecount
<! -- End by page -->

<Div align = "left" class = "pblank">
<%
'--------- Display tree structure! -------------
Level = 0
Response. Write "<ul>"
For I = 0 to ubound (arrRs, 2)
If arrRs (1, I)> level then
Response. Write "<ul>"
End if
If arrRs (1, I) <level then
For j = arrRs (1, I) to level-1
Response. Write "</ul>"
Next
End if
Int_size = arrRs (5, I)
If int_size = 0 then
Str_size = "<NO content>"
Else
Str_size = ""
End if
Response. Write "<li> <a href = showTitle. asp? Int_id = "& arrRs (0, I) &" & tint_tableid = "& tint_tableid &" class = ptitle target = BoardAnnounce> "& server. HTMLEncode (arrRs (4, I) & "</a>" & str_size
If arrRs (7, I) <> "then Response. Write" <connection>"
If arrRs (8, I) <> "then Response. Write" <image>"
Response. write "-[" & arrRs (9, I) & "] <font color = 444444>" & arrRs (10, I) & "</font> [<font color = 920092> ID:" & arrRs (0, I) & "click:" & arrRs (6, I) & "Times </font>] <font color = 444444> (" & int_size & "bytes) </font> <font color = ff0000> (" & arrRs (2, i) & ") </font> </li>"
　　
　　
Level = arrRs (1, I)
　　
Next

Response. Write "</ul>"
'--------- The tree structure is displayed! -------------

%>
</Div>
<Div align = "left">
<! -- Start by page -->
<! -- # Include file = "include \ tablepage. asp" -->
<! -- End by page -->
</Div>
<! -- Cleaning and debugging -->
<%
Rs. close
Set rs = nothing
Set cmd = nothing
If err. number <> 0 then Response. Redirect "bug. asp"
%>

Iii. Features
Let's take a look at the differences and features between him and the traditional three methods.
First, only one page of records is returned each time, and only one record set is formed. In addition, the client can use the fastest FireWire cursor to complete page output. Instead of using rs. nextrecordset records to output records like the traditional cursor method.
Second, the database does not use temporary tables, which is much faster than dumping the record set.
Third, a scroll cursor is used, and the cursor is positioned only after two operations. The speed is also greatly improved.
　　
When I use this paging method, I can obviously feel that the paging speed is improved. Of course, when dealing with tree structures and Database Operations, I have used many methods to increase the speed as much as possible, for example, using the binary Interval Method to compile the tree structure of the calendar, all SQL operations are implemented using stored procedures, and database algorithms are implemented using triggers and database cursors to avoid excessive network transmission. Any operation can only be completed by passing parameters with the database once.
Interested friends can go to my forum to see: http://tec.xj169.net/bbs