标题: 动网分页
作者: dnawo
日期: 2007-09-06 09:56 AM
分类: 程序修改
内容: 在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
+ 查看当前日志评论 (0)
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.047 ms
标题: 动网分页
你没有权限发表评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.047 ms
暂无评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.047 ms