It's well written. I use it on the website, but it's not as good as this one. There is more than 1 million data in that table, and you can find tens of thousands after checking it. This is all I can do.
By the way, I will post one: Learn:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
The theoretically fastest method for paging web databases
From: News Center
Click: 2 Joining time: 2002-11-22 15:53:41
In the previous article, we talked about the three traditional pagination methods of databases and their pros and cons, and proposed a theoretically best pagination method. In this article, we will talk about this best pagination method in detail.
1: Conception.
When designing a web database, if we want to compile every record, then only by adopting the paging mode can the web database be presented to end users as soon as possible, and the user will not lose interest in browsing the page because of the 8-second principle. However, even if the page is adopted, when a multi-record database appears, it will inevitably make our users feel that the page turn speed is too slow. As my previous article said, almost all the three pagination methods of the previous article have some flaws. So, how can we make the database take the records we need every time? This is easy to implement. It can be achieved by returning multiple record sets with cursors, but it will be difficult to make one end of the database not consume much resources just because it is necessary to retrieve a page of records. Finally, after I kept rewriting the program and testing, I finally wrote the fastest web database paging method in theory.
2: The stored procedure specifically implemented.
Let's talk about this approach in combination with a BBS question. How to make a BBS record only one page that is needed per page at a time? And what parameters do we need to provide to the database? The following parameters may be available.
The first: is the current number of pages we need.
Second: The number of record sets for each page currently defined. This way you can modify the number of records for each page in the page program as needed. Of course, if you do not consider the scalability of the program, you can also directly specify that there are N records on each page in the database.
Third: An output parameter: It is to obtain the total number of records in the current table from the database. (Note that it is not the number of records for a page) It is equivalent to the Recordcount in the ADO paging method. If you don't need the total record number, you don't have to return it.
Let's look at 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 can ignore it. .
@int_pagenow int=0,
@int_pagesize int=0,
@int_recordcount int=0 output --It means to obtain the total number of posts on a certain 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 --Get the total number of posts on this page
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 there is no temporary record set, and the cursor does not need to traverse all record sets.
open cro_fastread --Open cursor
select @int_beginid=(@int_pagenow-1)*@int_pagesize+1 Give the first record ID of the page
select @int_endid = @int_beginid+@int_pagesize-1 The ID of the last record of the page
fetch absolute @int_beginid from cro_fastread into @int_pagebegin Pass his Id to a variable The Id at the beginning of the page
if @int_endid>@int_allid --Note here, if a certain page is less than the record of fixed page count. If there is only one page of records, and the records are less than what we define. Or on the last page. . .
fetch last from cro_fastread into @int_pageend --directly position the cursor absolutely to the last record and 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 and last id of the page to get the intermediate id. . . . (Note. Our BBS's numerical structure uses a very clever algorithm, which is to use an orderNum floating point number to complete the sorting...)
--Start clearing. . .
close cro_fastread
deallocate cro_fastread
return
Let’s take a look at the program operations in the Asp page. . .
pagenow=cint(request("pagenow")) -the current page.
if pagenow<=0 then pagenow=1
pagesize=10
set cmd=("")
=strconn
=4
="pro_pageview"
("tint_tableid",adInteger,adParamInput,,tint_tableid)
("int_pagenow",adInteger,adParamInput,,pagenow)
("int_pagesize",adInteger,adParamInput,,pagesize)
("int_recordcount",adInteger,adParamOutput)
set rs=
if then
"The number of records has exceeded or the record set is empty!"
end if
dim arrRs
arrRs= 'You can use getRows to quickly save the record set into a two-dimensional array to improve speed.
recordcount=("int_recordcount")
'Note that when the record is not enough to just divide the unit page record, we should also define it as one page. If the number of records is 2 pages and one more record, then our page number must also be 3 pages.
if (recordcount mod pagesize)=0 then
pagecount=recordcount\pagesize
else
pagecount=recordcount\pagesize+1
end if
<--Page Start -->
<!-- #include file="include\" -->Fixed paging functions are actually nothing more than pagenow+1 or pagenow-1, pagenow, pagecount
<!--End of Pagination-->
<div align="left" class="pblank" >
<%
'--------------Show tree structure! ----------------------
level=0
"<ul>"
for i=0 to ubound(arrRs,2)
if arrRs(1,i)>level then
"<ul>"
end if
if arrRs(1,i)<level then
for j=arrRs(1,i) to level-1
"</ul>"
next
end if
int_size=arrRs(5,i)
if int_size=0 then
str_size=" <