A theory the fastest Web database paging method
One: the idea.
Web database design, if we want to record for each calendar, then only can we make the paging mode Web database as soon as possible and make a good show to the end-user, 8 seconds will not lose the principles of user browsing the page interest. But even if the approach taken by paging, when the database record, there is also inevitable that our customers will feel when the page is too slow. I like the articles said, almost on a three page article, methods have some shortcomings. So, how do we let the database each time we need to check the records, the realization of this good, there are more than the record set cursor to return can be achieved, but if one end of the database will not have to search a record of just The large consumption of resources has been difficult. Finally, I continue to rewrite and test procedures, and finally to write a theory I think the fastest way to the Web page database.
Second: the specific realization of the stored procedure.
We combine the issue of a BBS and talk about this approach. BBS how to make a reality of each page only needs a record of it? And we need to provide those parameters to the database do? May have the following parameters.
First: we need the current page.
Second: The current definition of each page of the record number set. So that you can modify the page process the record number of each page. Of course, if you do not consider the procedures for scalability, you can also directly in the database for each page can N be a record.
Third: one output parameters: drawn from the database table the current number of total number of records. (Note that he was not a record of the number of pages) that he is equivalent to ADO paging law Recordcount. If you do not need the total number of records can be returned to him.
We look at the specific code stored procedure. . .
CREATE PROCEDURE dbo.PRO_pageview
@ tint_tableid tinyint = 1, - this is the BBS of the current layout Id, you can not control him. .
@ int_pagenow int = 0,
@ int_pagesize int = 0,
@ int_recordcount int = 0 output - is to draw the total BBS posted a few pages. .
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 - come to the page number of the total paste
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 definition of the cursor operation here, but do not need to set the provisional record, but the cursor does not need to traverse all the records of all sets.
open cro_fastread - open cursor
select @ int_beginid = (@ int_pagenow-1) * @ int_pagesize +1 to draw the page the first record of Id
select @ int_endid = @ int_beginid + @ int_pagesize-1 reached a record last page of the Id
fetch absolute @ int_beginid from cro_fastread into @ int_pagebegin his Id to the start page of a variable Id
if @ int_endid> @ int_allid - would like to note that if a fixed number of pages less than a page of the record. If there is only one record, but the definition of a record number is less than we. Or when the last page. . .
fetch last from cro_fastread into @ int_pageend - directly to the absolute position the cursor to the end of a record, come to his id number. . .
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 will be able to use the page id of the first and the last id of the id to come to the middle. . . . (Note. The number of BBS our structure has a very clever algorithm, that is, a floating-point numbers to complete the sequencing orderNum...)
- The beginning of the clearance. . .
Let us look at the asp operating procedures page. . .
pagenow = cint (request ( "pagenow")) - the 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 "has now exceeded the record number or record set is empty!"
arrRs = rs.getrows' records can be used to set getRows quickly saved to a two-dimensional array to improve the speed.
recordcount = cmd.Parameters ( "int_recordcount")
'Note that when the record is not sufficient just divisible unit records page, we have to be defined as a page, such as the number of records a record of more than two, the number of pages at a time when we have to record 3.
if (recordcount mod pagesize) = 0 then
pagecount = recordcount \ pagesize
pagecount = recordcount \ pagesize +1
<- To start page ->
<! - # Include file = "include \ tablepage.asp" -> fixed paging function, in fact, is nothing more than pagenow +1 or pagenow-1, pagenow, pagecount
<! - Page end ->
<div align="left" class="pblank">
'--------- Show tree structure! -------------
level = 0
for i = 0 to ubound (arrRs, 2)
if arrRs (1, i)> level then
if arrRs (1, i) <level then
for j = arrRs (1, i) to level-1
Response.Write "</ ul>"
int_size = arrRs (5, i)
if int_size = 0 then
str_size = "<No>"
str_size = ""
Response.Write "<li> <img src=face\"&arrRs(3,i)&"> <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 "<connect>"
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) & "th </ font>] <font color=444444> (" & int_size & "bytes) </ font> <font color=ff0000> (" & arrRs (2, i )&")</ font> </ li> "
level = arrRs (1, i)
Response.Write "</ ul>"
Shows the tree structure'--------- finished! -------------
<! - The start page ->
<! - # Include file = "include \ tablepage.asp" ->
<! - Page end ->
<! - Cleared and debugging ->
set rs = nothing
set cmd = nothing
if err.number <> 0 then Response.Redirect "bug.asp"
We take a look at him and the traditional distinction between the three methods and characteristics of
First: Every time a record is only returned, but only set a record, but the client can use the fastest FireWire output cursor to complete the page. Rather than the traditional method using the cursor to the output record rs.nextrecordset record.
Second: Database temporary table is not used, so that a record set more than the speed of dump greatly enhanced.
Third: the use of a scroll cursor, and cursor after only two to complete the positioning operation. Speed is greatly increased.
When I used this kind of paging method, has the feeling it is clear that the increased speed paging. Of course, in dealing with tree structure, database computing, I used a number of ways to increase speed as much as possible, such as: the use of two inter-district law for the calendar tree structure, all the stored procedures used to achieve all sql operations, the use of flip-flop and database cursor to complete the database of algorithms, so try to avoid excessive network traffic. Any action and only one parameter database can be completed.
Interested friends can go to my forum to look at: http://tec.xj169.net/bbs
(Full text End: This article only to thank Big Brother for the paging problemquestioned, not his dedication to explore, there will not be the birth of this article, would also like to thank Yuan Liao bigeagle with their paging algorithm also gave me a lot of inspiration.)
ASP Database Related Articles
- Arabic numerals the amount of conversion of the stored procedure for the Chinese
- Application database unique constraint and capture asp database errors
- The implementation of DTC with asp
- Data Shaping technology - the necessary configuration data providers
- Data Shaping technology - Data configuration
- PivotChart and PivotTable functionality expansion
- Data access page to bind to the disconnected recordset
- Online maintenance of the database using ASP
- In ASP using Oracle Object for OLE visit Oracle 8
- Compress and repair Access databases (recommended)
- Using ASP to access a variety of commonly used type of database (4)
- Access database table read example
- ASP code to restore database
- Online Access database compression
- Using ASP to access a variety of commonly used type of database (1)
- Using ASP to access a variety of commonly used type of database (2)
- Using ASP to access a variety of commonly used type of database (3)
- odbc and ole db database in connection What is the difference?
- Stored procedure on page
- A theory the fastest Web database paging method
Can't Find What You're Looking For?
Rating: Not yet rated