前段时间一直被公司后台数据翻页卡顿的问题困扰,因为数据量比较大,使用的是Adodb的Recordset对象进行数据遍历。
当时猜测是由于Recordset每次都读取了所有数据,并且每次翻页都刷新了页面,没有利用Recordset的缓存优势进行翻页,所以导致数据加载非常慢。
解决办法:
1. Recordset + JS无刷新翻页
使用Recordset方式,由于是因为Recordset的缓存没有得到有效的利用导致的卡顿,所以每次翻页时不进行刷新操作,使用JS进行数据翻页(首次读取时,已经遍历出所有页,使用JS控制显隐)
由于首次会遍历出所有数据,所以首次加载会很慢,并且占用不必要的内存;但是后续翻页会极快。
2. 通过标识列进行数据筛选
因为我们的数据表存在标识列,并且不涉及到数据删除以及修改,所以可以使用ID进行数据筛选
例如:
PageSize:10
Page 1: ID BetWeen 1 AND 11
Page 2: ID BetWeen 2 AND 21
Page X: ID BetWeen X And X*10+1
这种方案因为需要标识列的连续性,所有使用场景有限制。但是性能极佳,在可能情况下,还是推荐使用这种方式。
3. 通过数据库进行分页
在SQLSERVER建立存储过程,通过存储过程将查询后的结果进行筛选后返回至业务端。
这种方式适用范围广,对数据的连续性、完整性没什么要求,查询效率也挺高,所以大部分人都选用存储过程分页。最主要是使用存储过程的话,便于维护。
示例代码
CREATE PROCEDURE [dbo].[queryDataByPage]
(
@TableName nvarchar(4000),
@ColumnName nvarchar(4000),
@Where nvarchar(4000),
@OrderBy nvarchar(4000),
@PageSize int,
@PageIndex int,
@TotalCount int output
)
AS
BEGIN
Declare @SQLString nvarchar(4000), @Total nvarchar(4000)
Set @SQLString = '
Select
*
From
(
Select
Row_Number() Over ( Order By ' + @OrderBy + ' ) As RowNumber,
P.*
From
(
Select
' + @ColumnName + '
From
' + @TableName + '
Where
' + @Where + '
) P
) As NewTable
Where
RowNumber BetWeen ' + str(@PageIndex * @PageSize + 1) + ' And ' + str((@PageIndex + 1) * @PageSize)
Set @Total = 'Select @TotalCount = Count(*) From ( Select ' + @ColumnName + ' From ' + @TableName + ' Where ' + @Where + ' ) P '
Exec sp_executesql @Total,N'@TotalCount int output',@TotalCount output
Exec sp_executesql @SQLString
END
<%
'''''''''''''''''''''''''''''
' 数据库分页
'使用SQLSERVER存储过程进行分页
'传入参数
'@TableName 数据表名
'@ColumnName 列名
'@Where 查询条件
'@PageSize 页大小
'@PageIndex 页码
'@OrderBy 排序
'执行完成后,返回一个Recordset对象
'以及一个全局变量strTotalCount
' @LiuGang
'''''''''''''''''''''''''''''
Dim strTotalCount
strTotalCount = 0
Class SQLPageClass
Private strTableName
Public Property Let setTableName(ByVal strVar)
strTableName = strVar
End Property
Private strColumnName
Public Property Let setColumnName(ByVal strVar)
strColumnName = strVar
End Property
Private strWhere
Public Property Let setWhere(ByVal strVar)
strWhere = strVar
End Property
Private strPageSize
Public Property Let setPageSize(ByVal strVar)
strPageSize = strVar
End Property
Private strPageIndex
Public Property Let setPageIndex(ByVal strVar)
strPageIndex = strVar
End Property
Private strOrderBy
Public Property Let setOrderBy(ByVal strVar)
strOrderBy = strVar
End Property
Private strProcedureName
Public Property Let setProcedureName(ByVal strVar)
strProcedureName = strVar
End Property
Public Property Get Version
Version = strVersion
End Property
Public Property Get Author
Author = strAuthor
End Property
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 分页查询参数
Dim CmdSP,adoRS,adCmdSPStoredProc,adParamReturnValue,adParaminput,adParamOutput,adInteger,adoField,adVarChar,adLongVarChar
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Class_Initialize()
strAuthor = "LiuGang"
strVersion = "v1.0"
'这些值在 VB 中是预定义常量,可以直接调用,但在 VBScript 中没有预定义
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
adLongVarChar = 201
End Sub
Public Function PageToRecordSet(strConnectionObject,strRecordSet)
Set CmdSP = Server.CreateObject("ADODB.Command") '建一个command对象
CmdSP.ActiveConnection = strConnectionObject '建立连接
CmdSP.CommandText = strProcedureName '定义command 对象调用名称
CmdSP.CommandType = adCmdSPStoredProc '设置command调用类型是存储过程 (adCmdSPStoredProc = 4)
CmdSP.Parameters.Append CmdSP.CreateParameter("@TableName", adVarChar, adParamInput, 4000, strTableName)
CmdSP.Parameters.Append CmdSP.CreateParameter("@ColumnName", adVarChar, adParamInput, 4000, strColumnName)
CmdSP.Parameters.Append CmdSP.CreateParameter("@Where", adVarChar, adParamInput, 4000, strWhere)
CmdSP.Parameters.Append CmdSP.CreateParameter("@OrderBy", adVarChar, adParamInput, 2000, strOrderBy)
CmdSP.Parameters.Append CmdSP.CreateParameter("@PageSize", adInteger, adParamInput, , strPageSize)
CmdSP.Parameters.Append CmdSP.CreateParameter("@PageIndex", adInteger, adParamInput, , strPageIndex)
CmdSP.Parameters.Append CmdSP.CreateParameter("@TotalCount", adInteger, adParamOutput, , 0)
Set adoRS = CmdSP.Execute
Set strRecordSet = adoRS
End Function
Private Sub Class_Terminate()
adoRS.Close : Set adoRS = Nothing
strTotalCount = CmdSP("@TotalCount")
Set CmdSP.ActiveConnection = Nothing
Set CmdSP = Nothing
End Sub
End Class
'调用示例
Set SqlPageClass = New SQLPageClass
With SqlPageClass
.setTableName = " TableName "
.setColumnName = " * "
.setWhere = " 1=1 "
.setPageSize = " 10 "
.setPageIndex = " 0 "
.setOrderBy = " Id Desc "
.setProcedureName = "[DataBaseName].[dbo].[queryDataByPage]"
End With
Call SqlPageClass.PageToRecordSet(ConnectionObj, Rs)
Response.Write Rs(0) & "<br/>"
Set SqlPageClass = Nothing
Response.Write strTotalCount & "<br/>"
'ASP使用Command对象操作SQLSERVER时,如果要取Output参数的话,一定要先关闭所有的Rs对象,才可以得到。
%>