Paging | data | Speed The purpose of this article is to achieve large data paging browsing and optimize the speed.
Build a Web application, paging browsing is essential; This issue has long been the most common problem, has been a better solution, which like the ASP program paging algorithm, such as the use of pagesize these attributes ADO objects, by the total number of records to calculate the page, and then jump recordsets and then output , there are also writing stored procedures to achieve paging data, these aspects have advantages and disadvantages, the following I introduce a practical project in the speed of a very fast paging algorithm;
Key points: SQL (implemented with top and AutoNumber)
Page script (browser fallback feature)
Environment:
Iis/sql server/access
Table structure:
CREATE Table Content Table (
AutoNumber IDENTITY (int, 1,1) not NULL,
Classification encoding varchar (m) NULL,
Title varchar (255) NULL,
Content varchar (4000) NULL,
Time datetime NULL,
)
Implementation principle:
This table sets the AutoNumber field, which is characterized by the creation of a repeat shape, including the field that remains ' after the record has been deleted. Water Nature ' (note: Usually in the system table construction, this kind of field uses very few, because the number cannot manage freely, but uses it here, mainly wants to omit the number maintenance code in the article).
Page pagination:
So the first step is to query the data of a page; If you have 100 records, press 20 to record a page, then the usual pagination algorithm is "total page = Total records in addition to pagination control number [more than the number of words, the total number of pages plus]", such a practice will have to produce all the records of a large recordset; Another proposed the use of stored procedures paging algorithm, the former is the ASP script to produce a large recordset, so the speed is rather slow, the latter is overkill, although I often write stored procedures, but according to my thinking will find that write stored procedures completely redundant.
In SQL, many friends who have just contacted it know the role of the top modifier keyword; For example: SELECT top 1 * FROM table1-This enables you to return a recordset with only one record from the Table1 table the ultimate goal of paging optimization is to avoid generating too large a recordset, through top can be fully controlled; Now the query table should be the select Top 20 AutoNumber, title, content, time from content table.
But now there is a problem, that is, how to position, top can not automatically position the output of a page to us, which is designed to the WHERE clause, according to a specific condition output the correct content; Note: The sequence of records is very important, which determines the success or failure of the algorithm;
The demo here is Desc way, in reverse order, such as the website software update, is the most recent update put the front, and this is the reverse way.
OK, let's take a look at the actual code first to determine whether it is the start page.
Dim strsql,i,endid,isbeginpage
Const CNT_PAGESIZE = 20 ' defines the size of each page record
' To determine whether to go to 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"
' This is the core of paging
If Isbeginpage then ' if the start page
' query = List records with category encoding equal to parameter flbm, sorted in reverse order, and lists only the front cnt_pagesize pens (cnt_pagesize is a constant definition, such as 20)
strSQL = "SELECT Top" & cnt_pagesize & "AutoNumber, title, content, time from content table where category code = ' & TRIM (Sqlencode (Request) (" FLBM ) & "' ORDER by auto-numbering desc"
Else ' If it is not the start page
If Request ("page") = "Next" then "this is written to enhance the performance of the code, if the parameter is next, the page content is removed
' Query = list the category encoding equal to the FLBM of the parameter and be less than the AutoNumber Endid (Endid is also a parameter), and arrange in reverse order, listing only the front cnt_pagesize pen (cnt_pagesize is a constant definition, such as 20)
strSQL = "SELECT Top" & cnt_pagesize & "AutoNumber, title, content, time from content table where category code = ' & TRIM (Sqlencode (Request) (" FLBM ")) &" ' and AutoNumber < ' & Request ("Endid") & "ORDER by automatic numbering desc"
End If
End If
' Open a data connection to execute SQL and set up a recordset
Set rs = Cnn.execute (strSQL)
If not Rs. EOF Then ' here write to determine if it is EOF can not, but, here has its special significance
Call Tabletitle ' Here is a custom function to create a table tag
Call Begintr ' Here is to create a form TR tag
For i=0 to Rs.fields.count-1 ' Traverse recordset field
Call Addcol (RS (i). Name) ' OUTPUT field name
Next
Call ENDTR
While not rs.eof ' loops the recordset contents and outputs
Call Begintr
For I=0 to Rs.fields.count-1
Call AddRow (Aspencode (RS (i). Value)
Next
Call ENDTR
Endid = RS ("autonumber") ' Save the AutoNumber value for each output
Rs. MoveNext
Wend
Call Tablebottom ' So far, simply output the recordset content
' Here is the output page mark, Vbaiif is a self write function
Prototype for function Vbaiif (a,b,c)
If a then
Vbaiif =b
Else
Vbaiif =c
End If
End Function
The implementation of the previous page is implemented by scripting the function History.back (1) of the browser, so there is no need to regenerate the data on the server side when the page is back, regardless of the speed.
On the homepage, the link on the previous page should be invalid, through Vbaiif (isbeginpage, "Disabled", "") to achieve, if the first page, then add the disabled attribute in the tag, if not the home page, then add History.back (1); Script instruction to roll back the browsing page.
The next page is passed the page parameter and the Endid parameter, and the page is set to next for the action of the next page, Endid represents the end number of the current Recordset, and the following page will be paginated.
Response. Write ("〈a href=" "#" "Onclick=" "javascript:" & Vbaiif (Isbeginpage, "", "history.back (1);") & "" "& Vbaiif (Isbeginpage," Disabled "," ") &" prev 〈/a〉|〈a href= "" Typeoptions.asp?flbm= "& Request (" FLB M ") &" &page=next&endid= "& Endid &" "" Next page 〈/a〉 ")
Else
' This solves the problem of whether the recordset is empty to continue paging at the end
If not Isbeginpage then
' To determine if it is a blank record and not the start page, then generate a script that returns the page, and the effect is that the page will automatically return to the previous page.
Response. Write "〈script language=javascript〉" & VbCrlf
Response.Write "History.back (1);" & VbCrlf
Response.Write "〈/script〉"
Response.End
Else ' If the start page record is empty, the hint has no content
Response.Write "〈font color=blue〉 no content under this category 〈/fon
