Improve performance of dynamic paging in ASP (turn)

Dynamic | pagination | Performance Original: Zhanglifeng Zhang Wu

Overview
Now there are a lot of introduction to the use of ASP to achieve dynamic pagination of the article, the same method, that is, every time using ADO to return the original data to meet the conditions of the set of specified pages. But in the actual engineering application, the raw data volume is usually very big, the raw data processing is slow, if every time the raw data is to be processed once, it will seriously affect the performance of the application running.
There are two main ways to solve the above problems: one way is to make the query condition relatively fixed, use the relative fixed query condition to process the raw data, generate a middle library of small data quantity, and operate the intermediary library every time. This will improve the performance of the program, but it will affect the flexibility of the program, and the server side also need to regularly process the raw data maintenance. Another way is to save the results of the query on the server side. This does not improve the performance of the query, but the server side can respond quickly when the client side is paged. The implementation of the first approach is relatively simple, this paper introduces the implementation of the second approach.

Implementation methods
The server-side query results are saved in a dynamic array, that is, a two-dimensional dynamic array is declared during the Session_OnStart process. When the server side receives the application submitted by the client side, first of all to determine whether the application is conditional query or page change, if the condition query is the query criteria and the last submitted query conditions are different, such as different then execute the query, the query results are saved in the array, and then return to the client side of the first page of content, Otherwise, the contents of the corresponding page are returned directly from the array.
Program implementation
1. Defining two-dimensional arrays and other variables
Sub Session_OnStart
Dim TempDb ()
ReDim Preserve TempDb (1,2)
Session ("StoredArray") = TempDb
' Define a session array
Session ("Ipagecount") =0
Session ("Ipageno") =0
......
End Sub

2. Calling stored procedure return data

Sub GetRecordSet (STRBBMC,STRKSSJ,
STRZZSJ, Strnodecode, Strfxzl)
' parameter is the report name and each restriction condition
Select Case STRBBMC
Case "Transaction summary sheet"
Strcnn= "PROVIDER=MSDASQL;DSN=SQLDB;
Uid=sa;pwd=123456;database=vlog; "
Set objcnn=server.createobje ("ADODB. Connection ")
Objcnn.commandtimeout = 9999999
Objcnn. ConnectionTimeout = 99999999
Objcnn. CursorLocation = adUseClient
Objcnn. Open strcnn ' Opens the connection
Set objRS =server.createobject
("ADODB.") Recordset ")
Objrs.pagesize = Ipagesize
Objrs.cachesize = Ipagesize
Objrs.open "Sszhatmlog '" and "STRKSSJ &",
' and Strzzsj & ', ' ' & Strnodecode & & ',
' "& Strfxzl &" ", Objcnn,adopenstatic,
adlockreadonly,1
' Execute stored procedure return query result
......
End Sub

3. Save query results to a dynamic array

Sub Saverecordset ()
If objrs.eof = False Then
Objrs.movelast
Session ("Irowcount") = Objrs.recordcount
Session ("Ifieldcount") = ObjRs.Fields.Count
Session ("Ipagecount") = Objrs.pagecount
ReDim Preserve Temparray (Session
("Irowcount"), Session ("Ifieldcount"))
' Temparray is a two-dimensional dynamic array,
To redefine the size of a recordset based on its size
Objrs.movefirst
Icount=0
Do While Objrs.eof=false
icount = icount + 1
For I= 1 to session ("Ifieldcount")
Temparray (icount,i) =objrs.fields.item
(i-1). Value
Next
Objrs.movenext
Loop
Session ("StoredArray") = Temparray
Objrs.close
Else
Session ("Ipagecount") = 0
End If
End Sub

4. Display the contents of a record

Sub ShowRecord ()
......
Localarray=session ("StoredArray")
Ishowtotal= (iPageCurrent-1) *ipagesize+1
Irowloop = 1
Do While irowloop < = Ipagesize and ishowtotal
< = Session ("Irowcount")
Response.Write ("< TR >")
For i = 1 to session ("Ifieldcount")
Response.Write ("< TD >"
& LocalArray (ishowtotal,i)) Next
Response.Write ("</tr >")
Ishowtotal = ishowtotal + 1
Irowloop = irowloop + 1 loop
Response.Write ("</table >")
If ipagecurrent < > 1 and
Ipagecurrent < session
("Ipagecount") Then
% >
< Center >< A href= "db_pag.asp?page=<%=
IPageCurrent-1% > "> Previous page </a >< A href=
"db_pag.asp?page=<%= ipagecurrent + 1% >" >
Back Page </a ></center >
<%
Else
If ipagecurrent < > 1 Then
% >
< Center >< A href= "db_pag.asp?page=<%=
IPageCurrent-1% > "> Previous page </a ></center >
<%
End If
If ipagecurrent < session ("Ipagecount") Then
% >
< Center >< A href= "db_pag.asp?page=
<%= ipagecurrent + 1 > "> Next </a >
</center >
<%
End If
End If
End Sub

5. Main program

If Request.QueryString ("page") = "" Then
' Submit the query request and the query condition is different from the previous one
......
Call GetRecordSet (STRBBMC,STRKSSJ,STRZZSJ,
STRNODECODE,STRFXZL)
Call Saverecordset
Else
Ipagecurrent=cint (request.querystr