程序修改 订阅所有【程序修改】的日志

动网分页

编辑:dnawo 日期:2007-09-06

字体大小: 小 中 大
在WEB应用程序中，数据查询过程的优化始终是大家比较关心的一个问题，今天我们来看看动网在这方面是怎么做的：

复制内容到剪贴板程序代码 程序代码
<%
If IsSqlDataBase=1 And IsBuss=1 Then
    Set Cmd = Server.CreateObject("ADODB.Command")
    Set Cmd.ActiveConnection=conn
    Cmd.CommandText="dv_list"
    Cmd.CommandType=4
    Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)
    Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)
    Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)
    Cmd.Parameters.Append cmd.CreateParameter("@tl",3)
    Cmd.Parameters.Append cmd.CreateParameter("@topicmode",3)
    Cmd.Parameters.Append cmd.CreateParameter("@totalrec",3,2)
    Cmd("@boardid")=Dvbbs.BoardID
    Cmd("@pagenow")=page
    Cmd("@pagesize")=Cint(Dvbbs.Board_Setting(26))
    Cmd("@topicmode")=TopicMode
    Cmd("@tl")=0
    Set Rs=Cmd.Execute
    If Not Rs.EoF Then
        SQL=Rs.GetRows(-1)
        Set topidlist=Dvbbs.ArrayToxml(sql,rs,"row","topic")
    Else
        Set topidlist=Nothing
    End If
Else
    Set Rs = Server.CreateObject ("adodb.recordset")
    If Cint(TopicMode)=0 Then
        Sql="Select  TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,hidename From Dv_Topic Where BoardID="&Dvbbs.BoardID&" And IsTop=0 order By LastPostTime Desc"
    Else
        Sql="Select  TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,hidename From Dv_Topic Where Mode="&TopicMode&" and BoardID="&Dvbbs.BoardID&" And IsTop=0  order By LastPostTime Desc"
    End If
    Rs.Open Sql,Conn,1,1
    If Page >1 Then
        Rs.Move (page-1) * Clng(Dvbbs.Board_Setting(26))
    End If
    If Not Rs.EoF Then
        SQL=Rs.GetRows(Dvbbs.Board_Setting(26))
        Set topidlist=Dvbbs.ArrayToxml(sql,rs,"row","topic")
    Else
        Set topidlist=Nothing
    End If
End If
SQL=Empty
Set Rs=Nothing
%>

上边源码摘自Dvbbs 7.1 Sp1版index.asp的LoadTopiclist函数，用于实现贴子主题的查询并分页显示。上边源码针对SQL Server和ACCESS做了不同的处理，从中我们可以看出一同一不同：

查询原理相同：都是使用RecordSet对象的GetRows方法从记录集中取出n条记录，再用ArrayToxml函数将这n条记录转为xml格式，之后交给其他函数去显示。

SQL语句优化上不同：在SQL Server上使用的是GetRows(-1)，而ACCESS使用的是GetRows(Dvbbs.Board_Setting(26))，可以看出，前者使用存储过程并做了一些优化，只返回所需的记录集，而后者基本未作优化。

附：dv_list存储过程

复制内容到剪贴板程序代码 程序代码
Create PROCEDURE [dv_list]

@boardid int=1,
@pagenow int=1,  --当前页数            
@pagesize int=1, --定义每页面帖子数目
@tl int=0,  --按时间段查询
@topicmode int=0, --专题
@totalrec int output


AS
set nocount on
declare @int_topnum int
declare @int_timenum int
declare @var_times varchar(5000)

if @pagenow>1
if @topicmode>0
begin
select @int_timenum=(@pagenow-1)*@pagesize
set rowcount @int_timenum
select @var_times=lastposttime from Dv_Topic where boardID=@boardID and istop = 0 and mode=@topicmode orDER BY lastposttime desc

set rowcount @pagesize
select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from dv_topic where boardID=@boardID and istop = 0 and mode=@topicmode and lastposttime < @var_times orDER BY lastposttime desc

set nocount off
return
end
else
begin
select @int_timenum=(@pagenow-1)*@pagesize
set rowcount @int_timenum
select @var_times=lastposttime from Dv_Topic where boardID=@boardID and istop = 0 orDER BY lastposttime desc

set rowcount @pagesize
select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from dv_topic where boardID=@boardID and istop = 0 and lastposttime < @var_times orDER BY lastposttime desc

set nocount off
return
end

else
if @topicmode>0
begin
set rowcount @pagesize
select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from Dv_topic where boardID=@boardid and istop = 0 and mode=@topicmode orDER BY lastposttime desc
end
else
begin
set rowcount @pagesize
select TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode from Dv_topic where boardID=@boardid and istop = 0 orDER BY lastposttime desc
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


上一篇: 基于ASP的站内多值搜索
下一篇: SQL Server 存储过程的经典分页
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
最新日志:
评论: 0 | 引用: 0 | 查看次数: 4957
发表评论
请登录后再发表评论！