馆藏200W本图书 检索涉及到8张表 用什么样的存储过程分页

C5662601 2010-03-03 01:20:06
RT：
数据库sql2005 我目前使用的存储过程如下 感觉速度很慢 


ALTER procedure [dbo].[page_pages]

(@pagesize int,

@pageindex int,

@sort nvarchar(200),

@where nvarchar(max))

as

set nocount on

begin

declare @strSQL nvarchar(4000)

declare @PageLowerBound int

declare @PageUpperBound int

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set @strSQL = '

with aa as(

select top ' + str(@PageUpperBound) + ' orderno = ROW_NUMBER()  OVER (' + @sort + '), * from

(

' + @where + '

)aaa

)

select * from aa where orderno between ' + str(@PageLowerBound + 1) + ' and ' + str(@PageUpperBound + 1)

end

exec(@strSQL)

set @strSQL = 'select count(*) from (' + @where + ') mt'

exec(@strSQL)

set nocount off

...全文
108 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
seai 2010-03-06
  • 打赏
  • 举报
 回复
引用 2 楼 c5662601 的回复:
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
这100怎么还是写死的

我还需要返回总记录条数用来显示


那就自己换成变量吧。
ai_li7758521 2010-03-06
  • 打赏
  • 举报
 回复
没多少可改的啦.等高手
--小F-- 2010-03-06
  • 打赏
  • 举报
 回复
引用楼主 c5662601 的回复:
RT：
数据库sql2005 我目前使用的存储过程如下 感觉速度很慢
SQL codeALTERprocedure[dbo].[page_pages]
(@pagesizeint,@pageindexint,@sortnvarchar(200),@wherenvarchar(max))asset nocountonbegindeclare@strSQLnvarchar(4000)declare@PageLowerBoundintdeclare@PageUpperBoundintset@PageLowerBound=(@pageindex-1)*@pagesizeset@PageUpperBound=@PageLowerBound+@pagesizeset@strSQL='
with aa as(
select top'+str(@PageUpperBound)+' orderno = ROW_NUMBER() OVER ('+@sort+'), * from
('+@where+'
)aaa
)
select * from aa where orderno between'+str(@PageLowerBound+1)+' and'+str(@PageUpperBound+1)endexec(@strSQL)set@strSQL='select count(*) from ('+@where+') mt'exec(@strSQL)set nocountoff

少用变量
ChinaJiaBing 2010-03-06
  • 打赏
  • 举报
 回复
ALTER procedure [dbo].[page_pages]
(@pagesize int,
@pageindex int,
@sort nvarchar(200),
@where nvarchar(max))
as
set nocount on
begin
declare @strSQL nvarchar(4000)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set @strSQL = '
with aa as(
select top ' + str(@PageUpperBound) + ' orderno = ROW_NUMBER() OVER (' + @sort + '), * from
(
' + @where + '
)aaa
)
select * from aa where orderno between ' + str(@PageLowerBound + 1) + ' and ' + str(@PageUpperBound + 1)
end
exec(@strSQL)
set @strSQL = 'select count(*) from (' + @where + ') mt'
exec(@strSQL)
set nocount off


row_number()已经比较快了，为什么要用动态...
黄_瓜 2010-03-06
  • 打赏
  • 举报
 回复
引用楼主 c5662601 的回复:
RT：
数据库sql2005 我目前使用的存储过程如下 感觉速度很慢
SQL codeALTERprocedure[dbo].[page_pages]
(@pagesizeint,@pageindexint,@sortnvarchar(200),@wherenvarchar(max))asset nocountonbegindeclare@strSQLnvarchar(4000)declare@PageLowerBoundintdeclare@PageUpperBoundintset@PageLowerBound=(@pageindex-1)*@pagesizeset@PageUpperBound=@PageLowerBound+@pagesizeset@strSQL='
with aa as(
select top'+str(@PageUpperBound)+' orderno = ROW_NUMBER() OVER ('+@sort+'), * from
('+@where+'
)aaa
)
select * from aa where orderno between'+str(@PageLowerBound+1)+' and'+str(@PageUpperBound+1)endexec(@strSQL)set@strSQL='select count(*) from ('+@where+') mt'exec(@strSQL)set nocountoff

05就用row_number（）了，语句本身已经没有什么好优化的了
可以考虑建立合适的索引，提升硬件设备
东那个升 2010-03-06
  • 打赏
  • 举报
 回复
@where中表的索引设计好，估计那是影响速度的地方。
C5662601 2010-03-03
  • 打赏
  • 举报
 回复
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
这100怎么还是写死的

我还需要返回总记录条数用来显示
ws_hgo 2010-03-03
  • 打赏
  • 举报
 回复 
/*--用存储过程实现的分页程序

 显示指定表、视图、查询结果的第X页

 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法

 如果视图或查询结果中有主键,不推荐此方法



--邹建 2003.09(引用请保留此信息)--*/



/*--调用示例

 exec p_show '地区资料'



 exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'

--*/



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') 

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_show]

GO



CREATE Proc p_show

@QueryStr nvarchar(4000), --表名、视图名、查询语句

@PageSize int=10,   --每页的大小(行数)

@PageCurrent int=1,   --要显示的页

@FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段

@FdOrder nvarchar (1000)='' --排序字段列表

as

declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名

 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号

 ,@Obj_ID int    --对象ID

--表中有复合主键的处理

declare @strfd nvarchar(2000) --复合主键列表

 ,@strjoin nvarchar(4000) --连接字段

 ,@strwhere nvarchar(2000) --查询条件





select @Obj_ID=object_id(@QueryStr)

 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end

 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end



--如果显示第一页，可以直接用top来完成

if @PageCurrent=1 

begin

 select @Id1=cast(@PageSize as varchar(20))

 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)

 return

end



--如果是表,则检查表中是否有标识更或主键

if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1

begin

 select @Id1=cast(@PageSize as varchar(20))

  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))



 select @FdName=name from syscolumns where id=@Obj_ID and status=0x80

 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键

 begin

  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')

   goto lbusetemp  --如果表中无主键,则用临时表处理



  select @FdName=name from syscolumns where id=@Obj_ID and colid in(

   select colid from sysindexkeys where @Obj_ID=id and indid in(

    select indid from sysindexes where @Obj_ID=id and name in(

     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

   )))

  if @@rowcount>1  --检查表中的主键是否为复合主键

  begin

   select @strfd='',@strjoin='',@strwhere=''

   select @strfd=@strfd+',['+name+']'

    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'

    ,@strwhere=@strwhere+' and b.['+name+'] is null'

    from syscolumns where id=@Obj_ID and colid in(

    select colid from sysindexkeys where @Obj_ID=id and indid in(

     select indid from sysindexes where @Obj_ID=id and name in(

      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

    )))

   select @strfd=substring(@strfd,2,2000)

    ,@strjoin=substring(@strjoin,5,4000)

    ,@strwhere=substring(@strwhere,5,4000)

   goto lbusepk

  end

 end

end

else

 goto lbusetemp



/*--使用标识列或主键为单一字段的处理方法--*/

lbuseidentity: 

 exec('select top '+@Id1+@FdShow+' from '+@QueryStr

  +' where '+@FdName+' not in(select top '

  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder

  +')'+@FdOrder

  )

 return



/*--表中有复合主键的处理方法--*/

lbusepk:  

 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from

  (select top 100 percent * from '+@QueryStr+@FdOrder+') a

  left join (select top '+@Id2+' '+@strfd+' 

  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'

  where '+@strwhere+') a'

  )

 return



/*--用临时表处理的方法--*/

lbusetemp:  

select @FdName='[ID_'+cast(newid() as varchar(40))+']'

 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))

 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))



exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'

  into #tb from'+@QueryStr+@FdOrder+'

 select '+@FdShow+' from #tb where '+@FdName+' between '

 +@Id1+' and '+@Id2

 )

GO
0行代码写服务部署版
虽说是原型但是数据全部需要实时，这时候就需要大量编写数据接口，同样编写接口这件事难度倒是不大，但是量大，编写过程手写很容易出错……案例三，以前管理的团队主要做移动端开发，里面的项目会涉及到推送，...
数据库
1 基本简介 　数据库（Database）是按照数据结构来组织、...数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。 2 主要特点 2.1 数据整体性
基于ThinkPhp框架的高校图书馆藏书借阅系统
我们所要做的是一个高校图书馆藏书借阅系统，用于读者对图书的检索、浏览、借阅。因此，需要的功能包括对图书的检索查询、借阅、预约、续借、安全性保障。 而在拓展功能板块，我们的考虑为以图书为中心建立起
基于知识图谱的图书智能推荐系统+论文+ppt
推荐系统旨在为用户推荐智能化的在线商品或信息，其广泛应用于众多...近几年随着数字图书馆的高速发展，其数字资源馆藏已实现了海量增长，读者对资源的需求越来越智能化，这些因素推动着图书馆推荐系统应用的普及和深化
MySQL数据库访问性能优化
MYSQL应该是最流行的WEB后端数据库。大量应用于PHP，Ruby，Python，Java 等Web语言开发项目中，无论NOSQL发展多么快，都不...说起MySQL的查询优化，相信大家会想到：不能使用SELECT *、不使用NULL字段、合理创建索引、
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章