分页存储过程？

xishuiyue 2010-09-30 04:59:18
写一个简单的存储过程返回总记录数和查询的结果，然后把总记录数给AspNetPager。结果填充ds 设置AspNetPager的数据源为ds,这样分页速度挺快的，为什么要用另外一种方法分页呢？就是把整个分页过程写在存储过程里面呢？
...全文
55 4 点赞 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
xishuiyue 2010-10-04
1楼说的有道理，但是在用AspNetPager的时候会给AspNetPager设定每页显示多少条记录，比如说8条记录，那么在填充ds的时候，只填充8条记录。并没有填充所有的记录。再翻页的时候又重新填充8条不一样的记录。我打断点试过，确实是这样子，所以不存在说一次把所有的记录填充到DS里面。执行速度也很快，就是有点不明白为什么还要像二楼那样写那么多语句。
  • 打赏
  • 举报
 回复 赞
dawugui 2010-09-30
http://topic.csdn.net/u/20100203/17/8F916471-597D-481A-B170-83BCEFE3B199.html



应一个朋友的要求，贴上收藏的SQL常用分页的办法～～ 



表中主键必须为标识列，[ID] int IDENTITY (1,1) 



1.分页方案一：(利用Not In和SELECT TOP分页) 



语句形式：  

SELECT TOP 页记录数量 * 

FROM 表名 

WHERE (ID NOT IN 

  (SELECT TOP (每页行数*(页数-1)) ID 

  FROM 表名 

  ORDER BY ID)) 

  ORDER BY ID 

//自己还可以加上一些查询条件 





例: 

select top 2 * 

from Sys_Material_Type 

where (MT_ID not in 

    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID)) 

order by MT_ID 







2.分页方案二：(利用ID大于多少和SELECT TOP分页） 



语句形式： 

SELECT TOP 每页记录数量 * 

FROM 表名 

WHERE (ID > 

          (SELECT MAX(id) 

    FROM (SELECT TOP 每页行数*页数 id  FROM 表 

          ORDER BY id) AS T) 

      ) 

ORDER BY ID 



例: 

SELECT TOP 2 * 

FROM Sys_Material_Type 

WHERE (MT_ID > 

          (SELECT MAX(MT_ID) 

          FROM (SELECT TOP (2*(3-1)) MT_ID 

                FROM Sys_Material_Type 

                ORDER BY MT_ID) AS T)) 

ORDER BY MT_ID 







3.分页方案三：(利用SQL的游标存储过程分页) 

create  procedure SqlPager 

@sqlstr nvarchar(4000), --查询字符串 

@currentpage int, --第N页 

@pagesize int --每页行数 

as 

set nocount on 

declare @P1 int, --P1是游标的id 

@rowcount int 

exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 

select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 

set @currentpage=(@currentpage-1)*@pagesize+1 

exec sp_cursorfetch @P1,16,@currentpage,@pagesize 

exec sp_cursorclose @P1 

set nocount off 







4.总结: 

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 

建议优化的时候，加上主键和索引，查询效率会提高。 



通过SQL 查询分析器，显示比较：我的结论是: 

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 

分页方案一：(利用Not In和SELECT TOP分页)  效率次之，需要拼接SQL语句 

分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用
  • 打赏
  • 举报
 回复 赞
dawugui 2010-09-30
[Quote=引用楼主 xishuiyue 的回复:]
写一个简单的存储过程返回总记录数和查询的结果，然后把总记录数给AspNetPager。结果填充ds 设置AspNetPager的数据源为ds,这样分页速度挺快的，为什么要用另外一种方法分页呢？就是把整个分页过程写在存储过程里面呢？
[/Quote
]--完善一点的,用存储过程实现的通用过程.



/*--用存储过程实现的分页程序



	显示指定查询结果的第X页

	此存储过程针对无主键的情况,使用临时表的方法

	如果有主键,不推荐此方法



--邹建 2003.09--*/



/*--调用示例

	exec p_show 'select * from xzkh_new..地区资料'



	exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号'

--*/



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(1000),	--查询语句,如果查询表,用:select * from 表

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

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

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

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

as

declare @FdName nvarchar(50)	--标识列名

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

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

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

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

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

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



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

		into #tb from('+@QueryStr+') a '+@FdOrder+'

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

	+@Id1+' and '+@Id2

	)

GO











/*--用存储过程实现的分页程序



	显示指定表、视图、查询结果的第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
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-09-30
我请求一页你把说有数据给传过来了，考虑一下网络资源能承不承受
  • 打赏
  • 举报
 回复 赞
相关推荐
基于Sql Server通用分页存储过程的解决方法 sql server2005以上就有了row_number 也是一大进步，详情如下参考 代码如下:Sql Server2005通用分页存储过程 CREATE PROCEDURE [dbo].[Common_GetPagedList](@TableName nvarchar(100), –表名@ColumnNames ...
sql server 通用分页存储过程 1.查询返回的表、列名以及排序列没有写死，可以根据需要将这些放到存储过程的输入参数中； 2.适用于所有需要分页的单表或多表联合查询。
MySql 分页存储过程以及代码调用 MySql 分页 存储过程 MySql 分页 存储过程 MySql 分页 存储过程
分页存储过程(用存储过程实现数据库的分页代码) 用存储过程实现数据库的分页代码,加快页面执行速度。具体的大家可以测试下。
一个分页存储过程代码 代码如下:———————————— –用途：分页存储过程(对有主键的表效率极高) –说明： ———————————— ALTER PROCEDURE [UP_GetRecordByPage] @tblName varchar(255), — 表名 @fldName varchar(255)...
分页存储过程 实用分页存储过程oracle版本和SqlServer2005以上版本。
针对SQL 2000 的分页存储过程代码分享 代码如下:—————————————————- –针对SQL 2000 的分页存储过程 –Time:2008-9-25 —————————————————- ALTER PROCEDURE [dbo].[uoSp_RecordPager] –要分页显示的表名，可多表连查...
MSSQL分页存储过程完整示例(支持多表分页存储) 主要介绍了MSSQL分页存储过程的实现与使用方法,并支持多表分页存储的功能,具有一定参考借鉴价值,需要的朋友可以参考下
万能分页存储过程 万能的分页存储过程 可实现各种情况下的分页功能 简单好用
高性能mysql分页存储过程 非常好用的mysql存储过程，支持大并发，很稳定，绝对原创，你值得拥有！
数据库分页存储过程代码 代码如下:/* 数据库分页存储过程，支持倒序和升序 参数说明： @tablename：为搜索表名 @tablefield：为表的字段，约定为表的主键, @where：为搜索表名，要显示所有记录请设为”1=1″ @orderby：为搜索结果排序...
高效分页存储过程 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程
sql2000可用分页存储过程 使用游标实现的sql2000可用分页存储过程，不要增加排序字段
oracle分页存储过程 oracle存储过程实例 代码如下:import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;...
分页存储过程代码 代码如下:/* *@curentpage 当前页 *@pagesize 每页记录数 *@TableName 表名 *@key 主键（自动排序） *@where 查询条件 1）空为 null 2）有查询条件不要带where *@order ‘0’表示　desc ‘1’是asc *@pageCount 总...
SQL中简单的分页存储过程 SQL中简单的分页存储过程的操作代码 实现思路：运用子查询来排除你所查询数据中所不需要的。先查到开始到你所需要查询内容的所有数据，然后再利用子查询排除掉前面你所不需要的数据，最后就只剩下你所需要的内容了。...
邹建的分页存储过程改了一下 邹建的分页存储过程–改了一下 因为邹建的分页存储过程不能返回记录总数，所以每次分页还又要统计一次，所以自己在里面加了个返回记录数，这下就好用了。 @iPageCount int OUTPUT –定义输出变量，放在@QueryStr...
千万级分页存储过程实现对表的数据分页速度非常快 经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒； 再生产环境中非常实用，帮助你解决线上数据库卡顿问题。
学习师德规范心得体会.doc 学习师德规范心得体会.doc
2022年会主题策划.docx 2022年会主题策划.docx
发帖
疑难问题
加入

2.1w+

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-30 04:59
社区公告
暂无公告