The last article talked about the three paging methods of the database and their pros and cons, and proposed a theoretical best paging method. This article we will tell this best paging method in detail. One: Content. When designing a web database, if we want to copy every record, only the paging mode can make the web database as soon as possible, do not presented to end users, nor will it be used by the 8 second principles to lose browsing this page. interest. However, even if you take a page, it is inevitable that our users feel too slow when our users feel flipped. Just like my last article, the three paging methods of almost a piece of articles have some defects. So, how we can make the database take the record every time, this is very good, there is a cursor to return multiple record sets, but if the end of the database will not be retrieved by retrieving a page It is very difficult to consume resources. Finally, after my continuous rewriting procedures and test, I finally prepared the web database paging method I think the theoretical theory. Two: The stored procedure of the specific implementation. We talk about this method in combination with a BBS issue. How to let a BBS only need a page of the need for each page? And what parameters we need to provide to the database? There may be the following parameters. First: is the current number of pages we need. Second: The number of record sets of each page of the current definition. This way you can modify the number of records per page in the page program as needed. Of course, if you don't think about the scalability of the program, you can also directly specify N / records in the database. Third: An output parameter: is how much the total number of records in the current table is drawn from the database. (Note that he is not a number of records) He is equivalent to RecordCount in the ADO page method. If you don't need a total number of records, you can use it without returning him. Let's see the code of the specific stored procedure. . . Create Procedure dbo.pro_pageview (@tint_tableid tinyint = 1, - this is the current layout ID of BBS, you don't have to manage him .. @ Int_pagenow int = 0, @int_pagesize int = 0, @ Int_recordcount int = 0 output - the total number of results posted a BBS forum ..) ASset nocount ondeclare @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 - affixed to the total number of stars forum 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 - defined here cursor operations, but Do not need temporary records, and the cursors don't need all records.
Open cro_fastread - Open the cursor select @int_beginid = (@ INT_PAGENOW-1) * @ int_pageSize 1 Detecting this page of the first record id select @int_endid = @ Int_beginid @ Int_pagesize-1 Device for the last record of the page ID FETCH ABSOLUTE @int_beginid from cro_fastread @int_pagebegin passed his ID to a variable to start the id if @int_endid> @int_allid - Here you should pay attention, if a page is not enough to fix the number of pages. If there is only one page record, and records less than our definition. Or is the last page. . . Fetch Last from cro_fastread @int_pagend - Directly locate the cursor to the last record, to draw his 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_pagend and @int_pagebegin Order by int_rootid desc, num_order desc - We can use the first ID of this page and the last ID to draw the intermediate ID. . . . (Note. Our BBS's numerical structure has a very clever algorithm that uses an ORDERNUM floating point to complete the sort ...) - Start the field. . . Close Cro_fastRead Deallocate Cro_fastRead Return Let's take a look at the program operation in the ASP page. . . PageNow = CINT (Request ("PageNow") - The current page.
if pagenow <= 0 then pagenow = 1pagesize = 10set cmd = server.CreateObject ( "adodb.command") cmd.ActiveConnection = strconncmd.CommandType = 4cmd.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.Executeif rs.eof then Response.Write "has exceeded the number of record or record set is empty!" Response.Endend ifdim arrRsarrRs = rs .getrows' can use GetRows to quickly save record sets to a two-dimensional array to increase speed. RecordCount = cmd.parameters ("int_recordcount") 'Note that when the record is not enough to complete the unit page record, we must define it as a page, such as a record number of 2 pages, at this time our page It is also possible to record 3 pages. IF (RecordCount Mod PageSize) = 0 THEN PageCount = RecordCount / PageSizeElse PageCount = RecordCount / PageSize 1END IF <- Page Start -> Fixed Paging Functions, Not PageNow 1 or Pagenow-1, Pagenow, PageCount
<%
'--------- Show the tree structure! -------------
Level = 0
Response.write "
"
For i = 0 to Ubound (arrrs, 2)
?? IF arrrs (1, i)> Level Then
???? Response.write
"
?? endiff
?? IF arrrs (1, i)
??? for j = arrrs (1, i) to level-1
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
"
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? endiff
? INT_SIZE = Arrrs (5, i)
?? if int_size = 0 THEN
???? Str_size = "
"
?? Else
???? Str_size = ""
?? endiff
?? Response.write
"& Server.htmlencode (Arrrs (4, I)) &" "& str_size?? IF ARRRS (7, i) <>" Ten Response.write "
"
• IF arrrs (8, i) <> "" Ten Response.write "
"
??? "? - [" & arrrs (9, i) & "]
"& Arrrs (10, I) &"
ID: "& Arrrs (0, I) &" Click: "& Arrrs (6, i) &" times]
("& int_size &" byte)
("& Arrrs (2, I) &")
"
??
??
?? level = arrrs (1, i)
??
NEXT
Response.write "
"
'--------- Show the tree structure! -------------
%>
<% rs.closset = NothingSet cmd = Nothingif err.Number <> 0 Then Response.Redirect "Bug.asp"%> Three: Features Let's take a look at the differences and characteristics of the three methods of his and tradition first: each Only only one page record and only form an record set, and the client can use the fastest fire line cursor to complete the page output. It doesn't have to output a record with the RS.NextRecordset record like a traditional cursor method. Second: The database is not used to use a temporary table, which is greatly improved than the speed of the dump. Third: Use a scrolling cursor, and the cursor completes the positioning only after two operations. The speed is also greatly improved. When I use this paging method, it is already possible to improve the page speed. Of course, when processing the tree structure, when the database is calculated, I use many ways to increase the speed as possible, such as using the dual-point interval method to copy the tree structure, all using the stored procedure to implement all SQL operations, using triggers and Database cursors to complete database algorithms, try to avoid excessive network transmission. Any operation is only available to the database for a parameter transmission.
