The purpose of this article is to realize paged browsing of big data and optimize the speed.
To build a web application, the paging browsing function is essential; this problem has also been the most common problem for a long time, and it has been well solved. Among them, there are many paging algorithms for ASP programs, such as using the PageSize attributes of the ado object. Calculate pages based on the total number of records, then jump to the record set and then output; there are also ways to write stored procedures to implement paging data. Each of these aspects has its own advantages and disadvantages. Below I will introduce a paging algorithm that is very fast in actual projects;
key points: SQL ( Implemented using TOP and automatic numbering)
Page script (browser fallback function)
environment:
IIS/SQL Server/Access
Table structure:
create table content table(
Automatic number IDENTITY(int, 1,1) not null,
Classification code<I>var</I>char(20) null,
Title<I>var</I>char(255) NULL,
Content<I>var</I>char(4000) null,
time datetime null,
)
Implementation principle:
This table is set up with an automatic numbering field. The characteristic of this field is to generate non-duplicate shaping, including that the field will still remain 'fluid' after deleting the record (Note: Usually in the construction of system tables, this field is rarely used. , because the number cannot be freely managed, but it is used here mainly because I want to omit the code for number maintenance in the article).
Pagination:
Then the first step is to query the data of a page; if there are 100 records and 20 records are used for one page, then the usual paging algorithm is "Total number of pages = total records divided by the control number of paging [If there is a remainder, the total number of pages plus one ]", this approach leads to the need to generate a large record set of all records; therefore, some people have proposed using the paging algorithm of stored procedures. The former is an asp script to generate a large record set, which is quite slow, and the latter is killing chickens. Although I often write stored procedures, according to my thinking, I find that writing stored procedures is completely redundant.
In SQL, many friends who are new to it know the role of the Top modified keyword; for example: select TOP 1 * from table1 -- In this way, a record set with only one record is returned from the Table1 table. The ultimate goal of paging optimization is to avoid Generating an excessively large record set can be fully controlled through TOP; now the query table should select Top 20 automatic numbering, title, content, and time from the content table.
But now there is still a problem, that is, how to position it. It is impossible for Top to automatically position and output a certain page for us. This is where the clause is designed to output the correct content according to a specific condition; note: the order by sorting of records is very important. , this determines the success or failure of this algorithm;
The demonstration here is the DESC method, which is arranged in reverse order. For example, for software updates on a website, the most recent update is placed first, and this is the reverse order method.
OK, let's take a look at the actual code. First, we must determine whether it is the start page.
dim strSQL,i,endID,isBeginPage
const Cnt_PageSize = 20 'Define the size of each page of records
'Determine whether it is an operation to enter the next page by checking the value of the Page parameter passed by the browser
isBeginPage = isEmpty(request("Page")) or request("Page")="" or request("Page")<>"next"
'Here is the core of paging
if isBeginPage then 'If it is the start page
'Query = List the records whose classification code is equal to the parameter flbm, in reverse order, and only list the first Cnt_PageSize pen (Cnt_PageSize is a constant definition, such as 20)
strSQL = "select TOP " & Cnt_pageSize & " automatic numbering, title, content, time from content table where classification encoding = '" & TRIM(SQLEncode(request("flbm"))) & "' order by automatic numbering desc"
else 'if not the start page
if request("Page")="next" then 'This is written here to enhance the performance of the code. If the parameter is next, it means to take the content of the page
'Query = List the records whose classification code is equal to the parameter flbm and should be less than the automatic number endID (endID is also a parameter), and sort them in reverse order, and only list the first Cnt_PageSize pen (Cnt_PageSize is a constant definition, such as 20)
strSQL = "select TOP " & Cnt_pageSize & " automatic numbering, title, content, time from content table where classification encoding = '" & TRIM(SQLEncode(request("flbm"))) & "' and automatic numbering<" & request ("endID") & " order by automatic number desc"
End if
end if
'Open the data connection to execute SQL and create a record set
set rs = Cnn.Execute(strSQL)
if not rs.Eof then 'Write here to determine whether it is Eof. It is not necessary, but it has its special meaning here.
call TableTitle 'Here is a self-written function used to create table tags
call beginTr 'Here is the tr mark to create the table
for i=0 to rs.fields.Count-1 'Traverse the recordset fields
call AddCol(rs(i).name) 'Output field name
Next
call endTr
while not rs.eof 'Loop the contents of the record set and output
call beginTr
for i=0 to rs.fields.Count-1
call AddRow(ASPEncode(rs(i).value))
Next
call endTr
endID = rs("automatic numbering") 'Here saves the automatic numbering value of each output
rs.MoveNext
Wend
call TableBottom 'So far, simply output all the contents of the record set
'The page turning mark is output here, vbaIIF is a self-written function
The prototype is <I>function</I> vbaIIF(a,b,c)
if a then
vbaIIF=b
else
vbaIIF =c
end if
end <I>function</I>
The implementation of the previous page is achieved by calling the browser function history.back(1) through a script. Then there is no need to regenerate data on the server side when returning to the page, and the speed does not need to be considered.
When on the home page, the link to the previous page should be invalid. This is achieved through vbaIIF(isBeginPage, "disabled", ""). If it is the home page, add the disabled attribute to the tag. If it is not the home page, add history.back (1); Script command, used to roll back the browsing page.
The next page is to pass the Page parameter and endID parameter. Page is set to next to indicate the action of the next page. endID indicates the end number of the current record set, and the next page will be paginated from this.
response.Write("〈a href=""#"" onclick=""java<I>script</I>:" & vbaIIF(isBeginPage,"","history.back(1);") & "" " " & vbaIIF(isBeginPage," disabled ","") & "〉Previous page〈/a〉|〈a href=""TypeOptions.asp?flbm=" & request("flbm") & "&Page=next&endID =" & endID & """〉Next page〈/a〉")
else
'Here, by judging whether the record set is empty, we can solve the problem of continuing to turn pages until the last page.
if not isBeginPage then
'Determine whether it is an empty record and it is not the starting page, then generate a script for the rollback page. The effect is that after entering the page, it will automatically return to the previous page.
response.Write "〈<I>script</I> language=java<I>script</I>〉" & vbCrlf
Response.Write "history.back(1);" & vbCrlf
Response.Write "〈/<I>script</I>〉"
Response.End
else 'If it is the start page record, it will be empty, and it will prompt that there is no content.
Response.Write "〈font color=blue〉There is no content in this category〈/font〉"
end if
endIf
Summary: Through front-end scripts and SQL query skills, high-performance paging programs are simple and fast.
I hope peers can come up with better real-time data paging algorithms.
Note: Since the forum limits HTML characters, key symbols are in Chinese capital letters.