create procedure test_p @ipage int, @num int as
begin
declare @maxpages int, @rcount int
if @ipage>=100
select @maxpages=ceiling(count(*)/@num) from test
else
select @maxpages=100000
if @ipage<=@maxpages/2
begin
select @rcount=@ipage*@num
set rowcount @rcount
select id=identity(12),name,descs,ddd into #temptable1 from test order by id
select * from #temptable1 where id>=(@ipage-1)*@num and id<= @ipage*@num
end else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
select id=identity(12),name,descs,ddd into #temptable2 from test order by id desc
select id,name, ddd,descs from #temptable2 where id>=(@maxpages-@ipage)*@num and id<= (@maxpages-@ipage+1)*@num order by id desc
end
end
create procedure splitpage @qry varchar(16384),@ipage int, @num int as
begin
declare @maxpages int
declare @rcount int
declare @execsql varchar(16384)
if @ipage>=100
select @maxpages=ceiling(count(*)/@num) from test
else
select @maxpages=100000
if @ipage<=@maxpages/2
begin
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
end else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' order by sybid desc'
set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@maxpages-@ipage)*@num) || ' and sybid <= ' || convert(varchar,(@maxpages-@ipage+1)*@num)
execute (@execsql)
end
end
>select id=identity(12),name,descs,ddd into #temptable1 from test order by id
>select * from #temptable1 where id>=(@ipage-1)*@num and id<= @ipage*@num
我在项目中也是使用这个模式的,问题是写临时表时是否会发生物理写操作?如果发生,还有没有不需要写临时表的方案?
vc00, sybase中的临时表当用select into #table的形式时,是不记录日志的。速度非常快。
这点和oracle不同。算是sybase的一个feature.
ncowboy ,老程序在处理10万页以上结果集会出现问题。用select @maxpages=ceiling(count(*)/@num) from test此处使用是不对的。
由于即席查询时获取maxpages效率不高,对此作以下修改:可以由用户指定最大查询页数,缺省定为5000
create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as
begin
declare @rcount int
declare @execsql varchar(16384)
if @ipage > @maxpages
begin
select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' || convert (varchar,@maxpages) ||']'
return
end
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
end
由于无法精确且高效取出即席查询结果集的总页数,原程序中考虑后几页查询的优化算法就无法使用了。
如果可以先算出精确的总页数,然后作为参数传给splitpage,那么也可以把查询后几页的算法再补上。
谁有更好的办法也可以提出来大家一起把这个分页存储过程优化到底。
版本更新:1.0.2
注意程序最后要加上set rowcount 0
create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as
begin
declare @rcount int
declare @execsql varchar(16384)
if @ipage > @maxpages
begin
select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' || convert (varchar,@maxpages) ||']'
return
end
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
set rowcount 0
end
Reference:
http://www.08.la/news2010/newshtml/JiShuWenZhang/SYBASE/9648.html