Jax的大数据量分页

利用存储过程实现大数据量的分页。

创建人：理想国（http://www.utoper.com）－－welsham；来源技术：Jax－－理想国开发的技术框架 

还没有进行详细测试，一般测试的结果：普通机子，百万数据量，不会超过5秒；而网上的那存储过程分页，一般要10秒。

用pagingSQL.sql生成数据结构；在pagingSQL最后将添加一些原始数据，可设置@i的最大值，以决定添加的数据量，时间足够和硬盘足够，可设100万以上
执行paging.asp可查看结果。

http://dl2.csdn.net/down4/20070911/11110206823.rar

page.asp

 

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
'****************************************************************** 
'** Jax的大数据量分页
'** 创建人：理想国（http://www.utoper.com）－－welsham；来源技术：Jax－－理想国开发的技术框架

'** 数据表Uto_bkDoc，字段：docID（主键ID）、subject（标题）、content（内容）,creator（创建人）,creatorID（创建人ID），inTime（创建时间）
'** 用pagingSQL.sql生成数据结构；在pagingSQL最后将添加一些原始数据，可设置@i的最大值，以决定添加的数据量，时间足够和硬盘足够，可设100万以上

'** 请把改进结果和测试结果发给我们：[e-mail]　或 [e-mail]
'******************************************************************

Dim rs,conn,connStr
Dim pageSize,page,recordCount,query,order
Dim time1
time1=timer()
'数据库配置
connStr="Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=localhost;uid=test;pwd=test2006;database=ASPNETMisDB;"

'初始化conn和rs
Call initConn(conn)
Call initRs(rs)

'演示：生成SQL及调用分页，建立rs
pageSize=Request.QueryString("pageSize")
page=Request.QueryString("page")
Call getData(pageSize,page,query,order)
'调用视力，显示数据
Call view()

'清除conn和rs
Call clearConn(conn)
Call clearRs(rs)


Function getData(pageSize,page,query,order)
    Dim sqls(11),orders,UB,i
    Dim top,docID,kw,creator,creatorID
    
    top=getQuery(query,"top")
    If top="" then top="0"
    
    docID=getQuery(query,"docID")
    kw=Trim(getQuery(query,"kw"))
    creator=getQuery(query,"creator")
    creatorID=getQuery(query,"creatorID")
    '这是Jax的安全检测，防止SQL注入
    'If Not oDoSafe.pFilter(Array(top,docID,kw,creator,creatorID),Array(12,21,21,21,12)) then Exit Function
    
    sqls(0)=CLng(top)
    sqls(1)="d.docID,d.subject,d.creator,d.creatorID,d.inTime"        
    sqls(2)="Uto_bkDoc As d"
    sqls(3)=""
    If docID<>"" then sqls(3)=sqls(3)&" and d.docID in("& docID &")"
    If kw<>"" then sqls(3)=sqls(3)&" and d.kw Like '%"& kw &"'"
    If creator<>"" then sqls(3)=sqls(3)&" and d.creator Like '%"& creator &"%'"
    If creatorID<>"" then sqls(3)=sqls(3)&" and d.creatorID="& creatorID
    If sqls(3)<>"" then sqls(3)="Where"&Mid(sqls(3),5)    
    
    sqls(4)=""
    
    If order="" then order="inTime_ASC"
    orders=Split(order,"-")
    UB=UBound(orders)
    sqls(5)=""
    For i=0 to UB
        orders(i)=Split(orders(i),"_")
        Select Case orders(i)(0)
            Case "inTime"
                sqls(5)=sqls(5)&",d."&orders(i)(0)&" "&orders(i)(1)
        End Select
    Next
    sqls(5)="Order By "&Mid(sqls(5),2)
    sqls(6)="d.docID"
    sqls(7)="1"
    sqls(8)=""
    sqls(9)=""
    If pageSize="" then    pageSize=25
    If page="" then page=1
    sqls(10)=CLng(pageSize)
    sqls(11)=CLng(page)
'调用
    getData=getList(conn,rs,sqls)
End Function
    
    
'数据库操作
Sub initConn(conn)
    Set conn=Server.CreateObject("ADODB.Connection")
    conn.Mode=3
    conn.open connStr
End Sub
Sub clearConn(conn)
    conn.Close
    Set conn=nothing
End Sub
Sub initRs(rs)
    Set rs=Server.CreateObject("ADODB.RecordSet")
End Sub
Sub clearRs(rs)
    Set rs=nothing
End Sub

Function getQuery(ByVal qUrl,qName)
    Dim qStart,qEnd
    If qUrl<>"" then
        qUrl="&"&qUrl
        qStart=InStr(qUrl,"&"&qName&"=")
        If qStart>0 then
            qStart=qStart+Len(qName)+2
            qEnd=InStr(qStart,qUrl,"&")
            If qEnd>=qStart then
                getQuery=Mid(qUrl,qStart,qEnd-qStart)
            else
                getQuery=Mid(qUrl,qStart)
            End if
        else
            getQuery=""
        End if
    else
        getQuery=""
    End if
End Function

'分页调用
Function getList(conn,rs,sqls)
    '参数转化，预防错误    
    sqls(10)=CLng(sqls(10))
    sqls(11)=CLng(sqls(11))
    
    '调用分页存储过程
    set cmd=Server.CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection=conn
        .CommandType=4
        .CommandText="Uto_paging"
        
        .Parameters.Append(cmd.CreateParameter("@pTop",3,1,,sqls(0))) 'select top的条数,0时表示所有
        .Parameters.Append(cmd.CreateParameter("@pField",200,1,300,sqls(1))) '字段
        .Parameters.Append(cmd.CreateParameter("@pTable",200,1,700,sqls(2))) '表
        .Parameters.Append(cmd.CreateParameter("@pWhere",200,1,650,sqls(3))) 'where
        .Parameters.Append(cmd.CreateParameter("@pWhere2",200,1,650,sqls(4))) 'where2，二次查询筛选
        .Parameters.Append(cmd.CreateParameter("@pOrder",200,1,100,sqls(5))) 'order等
        .Parameters.Append(cmd.CreateParameter("@pID",200,1,25,sqls(6))) '主键
        .Parameters.Append(cmd.CreateParameter("@pIDType",200,1,1,sqls(7))) '主键类型,1为int，2为char
        .Parameters.Append(cmd.CreateParameter("@pStart",200,1,300,sqls(8))) '头部
        .Parameters.Append(cmd.CreateParameter("@pEnd",200,1,1000,sqls(9))) '尾部
        
        .Parameters.Append(cmd.CreateParameter("@page_size",3,1,,sqls(10))) '页大小
        .Parameters.Append(cmd.CreateParameter("@page_no",3,1,,sqls(11))) '页码
        .Parameters.Append(cmd.CreateParameter("@RecordCount",3,2)) '返回的记录数
        .Execute()
        getList=cmd.Parameters("@RecordCount")
        set rs=cmd.Execute()
    End With
    recordCount=getList
    Set cmd=nothing
End Function

Sub view()
 %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Jax的大数据量分页</title>
</head>

<body>

<p>Jax的大数据量分页：</p>

<table width="100%" border="0" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
  <tr>
    <td width="15%" height="25" align="center" bgcolor="#EFEFEF">ID</td>
    <td width="23%" height="25" bgcolor="#EFEFEF">标题</td>
    <td width="23%" height="25" align="center" bgcolor="#EFEFEF">创建人</td>
    <td width="39%" height="25" align="center" bgcolor="#EFEFEF">创建时间</td>
  </tr>
<% 
If recordCount=0 then
 %>
 <tr>
    <td height="25" colspan="4" align="center" bgcolor="#FFFFFF">没有找到记录</td>
  </tr>
<% 
else
    Do While Not rs.EOF
 %>
  <tr>
    <td height="25" align="center" bgcolor="#FFFFFF"><%= rs("docID") %></td>
    <td height="25" bgcolor="#FFFFFF"><%= rs("subject") %></td>
    <td height="25" align="center" bgcolor="#FFFFFF"><%= rs("creator") %></td>
    <td height="25" align="center" bgcolor="#FFFFFF"><%= rs("inTime") %></td>
  </tr>
<% 
        rs.MoveNext
    loop
    rs.Close
 %>
  <tr>
    <td height="25" colspan="4" align="center" bgcolor="#EFEFEF">共有<%= recordCount %>条记录，当前第<%= page %>页，每页显示<%= pageSize %>条</td>
  </tr>
  
<% 
End if
 %>
 
</table>

<p>运行时间：<%= (timer()-time1)*1000 %>ms</p>

</body>
</html>
<% 
End Sub
 %>

 

pagingSQL.sql

 

CREATE TABLE [Uto_bkDoc] (
    [docID] [int] IDENTITY (1, 1) NOT NULL ,    
    [subject] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,    
    [content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,    
    [creator] [nvarchar] (20) NULL ,
    [creatorID] [int] NULL ,
    [inTime] [datetime] NULL ,    
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


ALTER TABLE [Uto_bkDoc] ADD     
    CONSTRAINT [DF_Uto_bkDoc_inTime] DEFAULT (getdate()) FOR [inTime],
    CONSTRAINT [PK_UTO_BKDOC] PRIMARY KEY  NONCLUSTERED 
    (
        [docID]
    )  ON [PRIMARY] 
GO

CREATE PROCEDURE Uto_paging
@pTop int,@pField nvarchar(300),@pTable nvarchar(700),@pWhere nvarchar(650),@pWhere2 nvarchar(650),@pOrder nvarchar(100),@pID nvarchar(25),@pIDType nvarchar(1)
,@pStart nvarchar(300),@pEnd nvarchar(1000),@page_size int,@page_no int,@RecordCount int output
 AS
Begin
    Declare @sqlstr nvarchar(4000),@IDStr nvarchar(2200)
    Declare @lower int,@upper int,@upper1 int
    Set @lower=(@page_no-1)*@page_size
    Set @upper=@lower+@page_size
    Set @upper1=@upper+1
    
    Set nocount on
    Set @sqlstr=N'Set @i=0'+char(13)
    Set @sqlstr=@sqlstr+N'Set @IDStr=N'''' '+char(13)
    Set @sqlstr=@sqlstr+N'Select '
    IF @pTop>0
        Set @sqlstr=@sqlstr+N'Top '+Cast(@pTop As nvarchar(10))+N' '
    Set @sqlstr=@sqlstr+N'@i=@i+1,@IDStr=Case when @i>'+Cast(@upper As nvarchar(10))+N' then @IDStr '
    Set @sqlstr=@sqlstr+N'when @i>'+Cast(@lower As nvarchar(10))+N' and @i<'+Cast(@upper1 As nvarchar(10))+N' then @IDStr+'
    IF @pIDType='1'
        Set @sqlstr=@sqlstr+N'Cast('+@pID+N' As nvarchar(10))+'','' '
    IF @pIDType='2'
        Set @sqlstr=@sqlstr+@pID+N'+'','' '
    Set @sqlstr=@sqlstr+N'else N'''' End '
    Set @sqlstr=@sqlstr+N'From '+@pTable+N' '+@pWhere+N' '+@pOrder
    EXECUTE sp_executesql @sqlstr,N'@i int output,@IDStr nvarchar(2200) output', @RecordCount output,@IDStr output
    
    Set nocount off
    IF Len(@IDStr)>0
        Begin
            Set @IDStr=Left(@IDStr,Len(@IDStr)-1)
            IF @pIDType='2'
                Set @IDStr=''''+Replace(@IDStr, ',', ''',''')+''''
            Set @sqlstr='Select '+@pField+' From '+@pTable+' Where '+@pID+' in ('+@IDStr+')'
            IF Len(@pWhere2)>0
                Set @sqlstr=@sqlstr+' and '+@pWhere2
            IF Len(@pStart)>0
                    Set @sqlstr=@pStart+@sqlstr
            IF Len(@pEnd)>0
                    Set @sqlstr=@sqlstr+@pEnd
            Else
                    Set @sqlstr=@sqlstr+' '+@pOrder
        End
    else
        Begin
            Set @sqlstr='Select '+@pField+' From '+@pTable+' Where '+@pID+'='
            IF @pIDType='1'
                Set @sqlstr=@sqlstr+'-1'
            Else
                Set @sqlstr=@sqlstr+''''''
        End
    Exec(@sqlstr)
End
GO



/**//*
添加原始数据，可修改@i<3000的值，以决定测试的数据量
*/
Declare @i int
Set @i=0
While @i<100000
BEGIN
    Insert Into Uto_bkDoc(subject,creator) Values('题'+Cast(@i As varchar(10)),'system')
    Set @i=@i+1
END