Access2000 Database 0.8 million record general fast paging class

The main idea is to use a statement to Count the number of records (instead of obtaining the RecordCount attribute when querying) and cache the records in Cookies. No further statistics are required during redirect. use the AbsolutePage attribute of ADO to jump to the page. for easy calling, the code is written as a class. The hardware environment is described in the main areas of the Code: AMDAthlonXP2600 +, 2

The main idea is to use a statement to Count the number of records (instead of obtaining the RecordCount attribute when querying) and cache the records in Cookies. No further statistics are required during redirect. use the AbsolutePage attribute of ADO to jump to the page. for ease of calling, the Code mainly involves the hardware environment: AMD Athlon XP 2600 +, 2

Main Idea: Use a statement to calculate (Count)RecordNumber (instead of obtaining the RecordCount attribute when querying), which is cached in Cookies. No further statistics are required during redirect. use the AbsolutePage attribute of ADO to jump to the page. classes are written for convenient calls. The Code mainly has descriptions.

Hardware environment: AMD Athlon XP 2600 +, 256 DDR
Software environment: MS Windows 2000 Advanced Server + IIS 5.0 + access 2000 + IE 6.0
Test results: the initial running time is from 250 (home page) to 400 (last page) in milliseconds ,(RecordAfter caching) page jumps are stable under 47 milliseconds. 1st pages are not more than 350 milliseconds to the last page

Applicable scope: for common paging. it is not applicable to complex queries. If the condition is "[Title] Like '% favorite %'", the query time is greatly increased, even if the Title field is indexed. :(
<% @ LANGUAGE = "VBScript" CODEPAGE = "936" %>
<% Option Explicit %>
<%
Dim intDateStart
IntDateStart = Timer ()
Rem # OpenDatabaseConnection
Rem ####################################### ##########################
Function f _ OpenConn ()
Dim strDbPath
Dim connstr
StrDbPath = "../db/test. mdb"
Connstr = "PRovider = Microsoft. Jet. OLEDB.4.0; Data Source ="
Connstr = connstr & Server. MapPath (strDbPath)
Set conn = Server. CreateObject ("Adodb. Connection ")
Conn. open connstr
End function
Rem ####################################### ##########################

Rem # disableDatabaseConnection
Rem ####################################### ##########################
Function f _ CloseConn ()
If IsObject (conn) Then
Conn. close
End If
Set conn = nothing
End function
Rem ####################################### ##########################
Rem get execution time
Rem ####################################### ##########################
Function getTimeOver (iflag)
Dim tTimeOver
If iflag = 1 Then
TTimeOver = FormatNumber (Timer ()-intDateStart, 6, true)
GetTimeOver = "execution time:" & tTimeOver & "seconds"
Else
TTimeOver = FormatNumber (Timer ()-intDateStart) * 1000, 3, true)
GetTimeOver = "execution time:" & tTimeOver & "millisecond"
End If
End function
Rem ####################################### ##########################
Class Cls_PageView
Private sbooInitState
Private sstrCookiesName
Private sstrPageUrl
Private sstrPageVar
Private sstrTableName
Private sstrFieldsList
Private sstrCondiction
Private sstrOrderList
Private sstrPrimaryKey
Private sintRefresh

Private sintRecordCount
Private sintPageSize
Private sintPageNow
Private sintPageMax

Private sobjConn

Private sstrPageInfo

Private Sub Class_Initialize
Call ClearVars ()
End Sub

Private Sub class_terminate ()
Set sobjConn = nothing
End Sub

Public Sub ClearVars ()
SbooInitState = False
SstrCookiesName = ""
SstrPageUrl = ""
SstrPageVar = "page"
SstrTableName = ""
SstrFieldsList = ""
SstrCondiction = ""
SstrOrderList = ""
SstrPrimaryKey = ""
SintRefresh = 0

SintRecordCount = 0
SintPageSize = 0
SintPageNow = 0
SintPageMax = 0
End Sub

Rem # SaveRecordCookies
Public Property Let strCookiesName (Value)
SstrCookiesName = Value
End Property

Rem # redirection address
Public Property Let strPageUrl (Value)
SstrPageUrl = Value
End Property

Rem # Table Name
Public Property Let strTableName (Value)
SstrTableName = Value
End Property

Rem # Field List
Public Property Let strFieldsList (Value)
SstrFieldsList = Value
End Property

Rem # query Conditions
Public Property Let strCondiction (Value)
If Value <> "" Then
SstrCondiction = "WHERE" & Value
Else
SstrCondiction = ""
End If
End Property

Rem # Sort fields, such as [ID] ASC, [CreateDateTime] DESC
Public Property Let strOrderList (Value)
If Value <> "" Then
SstrOrderList = "order by" & Value
Else
SstrOrderList = ""
End If
End Property

Rem # statisticsRecordNumber Field
Public Property Let strPrimaryKey (Value)
SstrPrimaryKey = Value
End Property

Rem # displayed on each pageRecordNumber of entries
Public Property Let intPageSize (Value)
SintPageSize = toNum (Value, 20)
End Property

Rem ##DatabaseConnection object
Public Property Let objConn (Value)
Set sobjConn = Value
End Property

Rem # current page
Public Property Let intPageNow (Value)
SintPageNow = toNum (Value, 1)
End Property

Rem # page Parameters
Public Property Let strPageVar (Value)
SstrPageVar = Value
End Property

Rem # Whether to refresh. 1 is Refresh, and other values are not refreshed
Public Property Let intRefresh (Value)
SintRefresh = toNum (Value, 0)
End Property

Rem # obtain the current page
Public Property Get intPageNow ()
IntPageNow = singPageNow
End Property

Rem # paging information
Public Property Get strPageInfo ()
StrPageInfo = sstrPageInfo
End Property

Rem # obtainRecordSet, two-dimensional array or string. IsArray () must be used for loop output.
Public Property Get arrRecordInfo ()
If Not sbooInitState Then
Exit Property
End If

Dim rs, SQL
SQL = "SELECT" & sstrFieldsList &_
"FROM" & sstrTableName &_
SstrCondiction &_
SstrOrderList

Set rs = Server. CreateObject ("Adodb. RecordSet ")
Rs. open SQL, sobjConn, 1, 1
If Not (rs. eof or rs. bof) Then
Rs. PageSize = sintPageSize
Rs. AbsolutePage = sintPageNow
If Not (rs. eof or rs. bof) Then
ArrRecordInfo = rs. getrows (sintPageSize)
Else
ArrRecordInfo = ""
End If
Else
ArrRecordInfo = ""
End If
Rs. close
Set rs = nothing
End Property

Rem # InitializationRecordQuantity
Private Sub InitRecordCount ()
SintRecordCount = 0
If Not (sbooInitState) Then Exit Sub
Dim sintTmp
SintTmp = toNum (request. Cookies ("_ xp _" & sstrCookiesName),-1)
If (sintTmp <0) Or (sintRefresh = 1) Then
Dim SQL, rs
SQL = "SELECT COUNT (" & sstrPrimaryKey &")"&_
"FROM" & sstrTableName &_
SstrCondiction
Set rs = sobjConn.exe cute (SQL)
If rs. eof or rs. bof Then
SintTmp = 0
Else
SintTmp = rs (0)
End If
SintRecordCount = sintTmp

Response. Cookies ("_ xp _" & sstrCookiesName) = sintTmp
Else
SintRecordCount = sintTmp
End If
End Sub

Rem # initialize paging information
Private Sub InitPageInfo ()
SstrPageInfo = ""
If Not (sbooInitState) Then Exit Sub

Dim surl
Surl = sstrPageUrl
If Instr (1, surl ,"? ", 1)> 0 Then
Surl = surl & "&" & sstrPageVar & "="
Else
Surl = surl &"? "& SstrPageVar &" ="
End If

If sintPageNow <= 0 Then sintPageNow = 1
If sintRecordCount mod sintPageSize = 0 Then
SintPageMax = sintRecordCount \ sintPageSize
Else
SintPageMax = sintRecordCount \ sintPageSize + 1
End If
If sintPageNow> sintPageMax Then sintPageNow = sintPageMax

If sintPageNow <= 1 then
SstrPageInfo = "homepage previous"
Else
SstrPageInfo = sstrPageInfo & "Homepage"
SstrPageInfo = sstrPageInfo & "Previous Page"
End If

If sintPageMax-sintPageNow <1 then
SstrPageInfo = sstrPageInfo & "last page of the next page"
Else
SstrPageInfo = sstrPageInfo & "next page"
SstrPageInfo = sstrPageInfo & "last page"
End If

SstrPageInfo = sstrPageInfo & "Page:"& SintPageNow &"/"& sintPageMax &""
SstrPageInfo = sstrPageInfo & "Total"& SintRecordCount &"EntriesRecord "& SintPageSize &"Entries/page"
End Sub

Rem # Long Integer Conversion
Private function toNum (s, Default)
S = s &""
If s <> "" And IsNumeric (s) Then
ToNum = CLng (s)
Else
ToNum = Default
End If
End function

Rem ## class initialization
Public Sub InitClass ()
SbooInitState = True
If Not (IsObject (sobjConn) Then sbooInitState = False
Call InitRecordCount ()
Call InitPageInfo ()
End Sub
End Class
Dim strLocalUrl
StrLocalUrl = request. ServerVariables ("SCRIPT_NAME ")

Dim intPageNow
IntPageNow = request. QueryString ("page ")

Dim intPageSize, strPageInfo
IntPageSize = 30

Dim arrRecordInfo, I
Dim Conn
F _ OpenConn
Dim clsRecordInfo
Set clsRecordInfo = New Cls_PageView

ClsRecordInfo. strTableName = "[myTable]"
ClsRecordInfo. strPageUrl = strLocalUrl
ClsRecordInfo. strFieldsList = "[ID], [Title], [LastTime]"
ClsRecordInfo. strCondiction = "[ID] <10000"
ClsRecordInfo. strOrderList = "[ID] ASC"
ClsRecordInfo. strPrimaryKey = "[ID]"
ClsRecordInfo. intPageSize = 20
ClsRecordInfo. intPageNow = intPageNow

ClsRecordInfo. strCookiesName = "RecordCount"
ClsRecordInfo. strPageVar = "page"

ClsRecordInfo. intRefresh = 0
ClsRecordInfo. objConn = Conn
ClsRecordInfo. InitClass

ArrRecordInfo = clsRecordInfo. arrRecordInfo
StrPageInfo = clsRecordInfo. strPageInfo
Set clsRecordInfo = nothing
F _ CloseConn
%>



Paging Test

<% = StrPageInfo %>

<%If IsArray (arrRecordInfo) ThenFor I = 0 to UBound (arrRecordInfo, 2)%> <%NextEnd If%>

ID	Description	Date
<% = ArrRecordInfo (0, I) %>	<% = ArrRecordInfo (1, I) %>	<% = ArrRecordInfo (2, I) %>

<% = StrPageInfo %>

<% = GetTimeOver (1) %>