Jax的大数据量分页

<script type="text/javascript">google_ad_client = "pub-2048279401139630";google_ad_slot = "8856771542";google_ad_width = 728;google_ad_height = 90;document.write("<s"+"cript type='text/javascript' s"+"rc='http://pagead2.googlesyndication.com/pagead/show_ads"+"."+"js'></scr"+"ipt>");</script>

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

创建人：理想国（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万以上

' ** 请把改进结果和测试结果发给我们：Utoper@163.com　或 welsham@163.com
' ******************************************************************

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
    page=Request.QueryString("page")
    order = Request.QueryString("order")
    query = Request.QueryString
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

 <script type="text/javascript">google_ad_client = "pub-2048279401139630";google_ad_slot = "8856771542";google_ad_width = 728;google_ad_height = 90;document.write("<s"+"cript type='text/javascript' s"+"rc='http://pagead2.googlesyndication.com/pagead/show_ads"+"."+"js'></scr"+"ipt>");</script>