Dynamic | pagination | performance from Computerworld
Improving the performance of dynamic paging in ASP
People's Bank Jinan Branch Clearing Center Zhanglifeng
Zhang Wu, Shandong Institute of Water Conservancy Science
--------------------------------------------------------------------------------
Overview
----now have a lot of introduction to the use of ASP to implement 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 these 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
----Saves the server-side query results in a dynamic array that declares a two-dimensional dynamic array 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.QueryString ("page"))
Strkssj=session ("STRKSSJ")
End If
If session ("Ipagecount") = 0 Then
Response.Write "Sorry! There are no records to meet the criteria.
Response.Write "< Br >"
Else
Call ShowRecord ()
End If
Conclusion
----The key of this program is the definition of the session array and the implementation of its assignment, which can improve the performance of the application with large amount of data by applying the session array.
