用Scroll Cursor写了一个分页的存储过程【还没封装】，大家来评价评价

[飞行石]

从各个角度来说吧，性能，目前问题等等。
代码如下：

/*************************************************************************
我重新写了一个分页的代码，使用Scroll Cursor，有一个缺点就是在
Fetch Absolute得时候资源占用率占掉总查询资源占用率的17%左右，如果能合并到
下面的那个循环里面就好了。因为While ... End 中间的查询所消耗的资源占用率
也是总资源占用率的17%。这样就能再提高性能了。游标比较占用资源，但是我觉得
对海量数据库在硬盘上搜索的话还是值得的。可以找一个10,000,000行的数据来测
试一下。
*************************************************************************/

Declare @PageIndex int --页索引【第几页】如果要创建存储过程
Declare @PageSize int --页行数【有几行】

Declare @Rowcount int --要查询的表/视图的行数
--只有这一行进行了全表查询，所以直接要求在相应的表
--建立一个相应的字段来存储这个信息，
--这里我还是简单的计算了一下
Select @rowcount = count(newsid) from wqnews

--下面两个变量可以封装在存储过程中，做为输入参数
Select @PageIndex = 5 --测试使用的页索引，指向当前第五页
Select @PageSize = 12 --测试使用的页大小，设定当前每页显示12条

Declare @PageLowerBound int
Declare @PageUpperBound int
Set @PageLowerBound=(@PageIndex-1)*@PageSize
Set @PageUpperBound=@PageLowerBound+@PageSize

--下面6个变量是一一对应要查询的表/视图中的字段
--请自行定义或者通过编程构造出来。
Declare @newsid int
Declare @heading nvarchar(100)
Declare @Author nvarchar(50)
Declare @AddTime smalldatetime
Declare @source nvarchar(100)
Declare @hits int

--下面的Table变量对应要查询的表/视图中的字段
--请自行设计结构或者通过编程构造出来。
Declare @wqnews Table(
newsid int Primary Key,
heading nvarchar(100),
author nvarchar(50),
addtime smalldatetime,
source nvarchar(100),
hits int)

Declare @i int
Select @i = 0

Declare myCursor SCROLL cursor for
Select * from wqnews
open myCursor
--游到指定行
fetch absolute @PageLowerBound from myCursor
--这里取出第一行放入指定的变量，对应要查询的表/视图中的字段
Into @newsid,@heading,@Author,@source,@addtime,@hits

--如果当前游标存在记录则进行下面的代码
If @@Fetch_Status = 0 Begin
Insert @wqnews (newsid,
heading,
author,
addtime,
source,
hits)
values (@newsid,
@heading,
@Author,
@addtime,
@source,
@hits)
while @i < @pageSize - 1 Begin
fetch next from myCursor
--这里取出第一行放入指定的变量，对应要查询的表/视图中的字段
Into @newsid,@heading,@Author,@source,@addtime,@hits
--当前行插入指定临时表
If @@Fetch_Status = 0 Begin
Insert @wqnews (newsid,
heading,
author,
addtime,
source,
hits)
values (@newsid,
@heading,
@Author,
@addtime,
@source,
@hits)
End
Select @i = @i +1
End
End
close myCursor
deallocate myCursor
Select * from @wqnews
/********************************代码结束********************************

[pengdali]

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num between 10 and 20

[pengdali]

declare @SQLStr varchar(8000)
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)

[飞行石]

楼上的能说说这个做什么用？放了一个临时表？
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num between 10 and 20

[pengdali]

对
create proc 分页浏览
@begin int,
@end int
as
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 你的表
select * from #temp where ID_Num between @begin and @end
go

--调用：
exec 分页浏览 50,100

[飞行石]

大力兄牛。

[leimin]

不一定,要具体问题具体分析?

[飞行石]

回复人： xpilot(飞行石) ( ) 信誉：104 2003-05-23 00:33:00 得分:0


大力的意思是用临时表取代游标，既然是做分页程序，性能肯定是首要考虑的问题，游标在各方面性能来说都不如临时表满意！

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
但是游标使用得当的话，也是一个好用的咚咚啊，何况我这个存储过程排除了全表扫描的过程，只有那个计算总行数的函数，如果在某一个字段存储了就根本不需要进行扫描。

我封装在.NET程序里面进行了压力测试，能顶得住大概150左右的冲击量。
数据是用程序添加的大概2,000,000行，添加了我一整天啊。

[飞行石]

回复人： pengdali(大力) ( ) 信誉：419 2003-05-22 23:52:00 得分:0


对
create proc 分页浏览
@begin int,
@end int
as
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 你的表
select * from #temp where ID_Num between @begin and @end
go

--调用：
exec 分页浏览 50,100



－－－－－－－－－－－－－－－－－－－－－－－－－－
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 你的表
大力兄，这个好像不太合适吧，我试了一下，原来就有一个列是自动编号的，嘿嘿，再加一个就出错了。

[yzssg]

你的实现方法确实是不错的，你说可以消除全表扫描的过程这也不错，但游标每次打开是所花费的代价实在太高了，况且你实现的还是Scroll Cursor，我感觉大力的做法还是好的，虽然我没测试过，但我想性能应当比你的要高。在数据库编程方面一般不到万不得已的时候是不会使用游标的，至少我是这样认为的，如果可以的话各位可以交个朋友，我的QQ：14234884，MSN：SSGZHL.HOTMAIL.COM

[pengdali]

select IDENTITY(int, 1,1) AS ID_Num,cast(你原来的id列 as int) 别名,其他列 into #temp from 表

[xpilot]

大力的意思是用临时表取代游标，既然是做分页程序，性能肯定是首要考虑的问题，游标在各方面性能来说都不如临时表满意！

[cbspy]

猛！

[cadinfo]

还是我来作个总结吧，大力兄牛，但是也倏忽了一点：

1.分页程序这样每次建立临时表，不觉得有点浪费吗？
尤其是对于VC开发的客户端程序，这种浪费是比较吓人的！

2.建立临时表之后就不管了，也太粗心了吧，我看还是需要做点工作
好了下面是我的一个分页过程，各位看看吧！

--------------------------------------------------------

create procedure sp_vlist @cpbmb varchar(6),@crbh varchar(2) with encryption as
set nocount on
declare @sqlcmd varchar(148)
if object_id('tempdb..##vlist') is not null
drop table ##vlist
set @sqlcmd='select identity(int, 1,1) as id, * into ##vlist from '+@cpbmb+' where crbh='''+@crbh+''' order by cpxh
create index ##vlist_id on ##vlist(id)'
exec(@sqlcmd)
set nocount off

---------------------------------------------------
^_^,这只是个建立全局临时表的过程，这样即可一劳永逸，在你的分页程序中使用between进行检索，怎么样，不赖吧！