web| Page | data | In the database we talked about the three kinds of pagination methods and their pros and cons of database tradition, and put forward a kind of theoretically best paging method, this article we will elaborate on this best paging method.
One: Idea.
When designing a Web database, if we want to compile every record, then only take the paging mode to make the Web database as soon as possible, rendering it to the end users, and not because of the 8-second principle of the user lost interest in browsing the page. But even with a paging approach, when there are multiple records of the database, it will inevitably make our users feel too slow to page. As I said in my last article, there are some flaws in the three pagination methods of almost every article. So, how do we get the database to fetch the records we need each time, this is a good implementation, and a cursor can return multiple recordsets, but it's hard to get the end of the database to consume a lot of resources just because it needs to retrieve a page of records. Finally, after my continuous rewriting program and test, I finally wrote the Web database paging method that I think is the fastest in theory.
Second: The specific implementation of the stored procedures.
We'll talk about this approach in conjunction with a BBS question. How to make a BBS every page only realistic need one page record? And what do we need to provide the database with those parameters? The following parameters may be available.
The first: is the number of pages we need today.
Second: The number of record sets for each page currently defined. This allows you to modify the number of records per page in the page program as needed. Of course, if you do not consider the scalability of the program, you can also directly in the database to specify that each page has n records.
Third: One output parameter: The number of total records in the current table is obtained from the database. (Note that he is not a record number of pages) he is equivalent to the RecordCount in the ADO paging method. If you don't need a total record number, you don't have to return him.
Let's look at the code for the specific stored procedure ...
CREATE PROCEDURE Dbo.pro_pageview
(
@tint_tableid Tinyint=1, this is the BBS's current layout ID, you can not care about him.
@int_pagenow int=0,
@int_pagesize int=0,
@int_recordcount int=0 Output--is to get the total number of BBS a page.
)
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--Gets the total number of tiles for the layout
DECLARE cro_fastread cursor Scroll
For the select int_id from Tab_discuss where tint_level=0 and tint_tableid= @tint_tableid ORDER BY int_id Desc--This defines cursor operations, but not pro Time record set, and cursors do not need to traverse all recordset.
Open Cro_fastread--opening cursors
Select @int_beginid = (@int_pagenow-1) * @int_pagesize +1 gets the first record ID of the page
Select @int_endid = @int_beginid + @int_pagesize-1 to obtain the ID of the last record of the page
Fetch absolute @int_beginid from Cro_fastread to @int_pagebegin pass his ID to a variable the ID of the page start
If @int_endid > @int_allid-Note that if a page is less than a fixed number of pages. If there is only one page record and the record is less than the number we have defined. Or the last page ...
Fetch last from Cro_fastread into @int_pageend--directly position the cursor absolutely to the final record, and get his ID number ...
Else
Fetch absolute @int_endid from Cro_fastread to @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 derive the middle ID .... Attention Our digital structure of the BBS uses a very clever algorithm, is to use a ordernum floating-point number can complete the sorting ... ）
--Start to clear ...
Close Cro_fastread
Deallocate Cro_fastread
Return
Let's look at the program operation in the ASP 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 "now has exceeded the record number or the record set is empty!" "
Response.End
End If
Dim Arrrs
Arrrs=rs.getrows ' can use GetRows to quickly save a recordset to a two-dimensional array to increase speed.
Recordcount=cmd. Parameters ("Int_recordcount")
' Note that when the record is not sufficient to divide the Unit page record, we also define it as a page, if the record number is 2 pages and one record, at this time we also have to 3 pages of page records.
if (RecordCount mod pagesize) =0 Then
Pagecount=recordcount\pagesize
Else
Pagecount=recordcount\pagesize+1
End If
<--Paging Start-->
The fixed paging function is actually nothing more than pagenow+1 or Pagenow-1,pagenow,pagecount
Three: Characteristics
We're going to look at him and the traditional three ways the difference and characteristics
First: Only one page record is returned at a time, and only one record set is formed, and the client can use the fastest FireWire cursor to complete the 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 significantly faster than the dump record set.
Third: A scrolling cursor is used, and the cursor completes the positioning with only two operations. Speed is also greatly improved.
When I adopt this method of paging, I can already feel the increase of paging speed obviously. Of course, in dealing with tree structures, database operations, I used a number of methods to improve the speed as much as possible, such as: the use of the two-division interval method to compile the calendar tree structure, all the use of stored procedures to achieve all SQL operations, using triggers and database cursors to complete the database algorithm, so as to avoid excessive network transmission. Any operation can be completed only once with a parameter pass to the database.
Interested friends can come to my forum to see: Http://tec.xj169.net/bbs
(Full text: Only this article to thank Shini brother to the question of paging questions, without his persistent discussion, there will not be the birth of this article, but also to thank Bigeagle and Liao, their pagination algorithm also gave me a lot of inspiration. ）
