电梯直达

1楼 [收藏(0)] [报告]

发表于 2010-11-12 16:21 |只看该作者 |倒序浏览

有人写过sybase iq 分页的过程吗，IQ 数据量比较大，用什么处理比较好？

2楼 [报告]

发表于 2010-11-12 16:37 |只看该作者

IQ中分页是个比较纠结的事情

3楼 [报告]

发表于 2010-11-12 17:01 |只看该作者

本帖最后由 wangdonsy 于 2010-11-12 17:05 编辑

不借助临时表或新建表，能不能在原表操作？
IQ 里面 ALTER TABLE 不能对临时表操作，新建表数据大，效率有问题
本论坛的一位兄弟写的

Please see the below sp I wrote:
CREATE PROCEDURE GetDataByPage(
@SqlStr varchar(8000),
@PageSize int,
@CurrentPage int)
AS
DECLARE @FirstRec int, @LastRec int,@dt varchar(10)
BEGIN
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
SELECT @dt= substring(convert(varchar,rand()),3,10)
SELECT @SqlStr = stuff(@SqlStr,charindex(' FROM ',upper(@SqlStr)),6,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD RID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
EXECUTE (@SqlStr)
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging'+@dt+' WHERE RID >; '+convert(varchar,@FirstRec)+' and RID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)
END
some comments:
1.@SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table, you'd better change the 'tempdb..Paging' to #Paging, the performance will be better

复制代码

sybase IQ 分页 [复制链接]