转：ASE中数据结果集分页效能的三种实现方法

转：ASE中数据结果集分页功能的三种实现方法 .

转：http://blog.csdn.net/andkylee/article/details/5638033

?

本篇介绍三种方法。

?

第一种：利用游标

程序开发人员比较喜欢使用游标，因为游标的“循环”遍历方式类似编程语言中的for，while，loop语句的实现方法，写起来比较容易。使用游标一般步骤是：为指定的SQL语句定义一个游标，打开并移动游标，当移动到指定行号的记录行之后，再按照需要提取的行数来取数据。从表面上看解决了提取指定范围数据的问题；但是在实际应用 上，有可能会出现严重的性能问题。建立游标需要耗用一定的系统资源之外；当表内的数据量有上千万甚至到亿级别并且需要取大量的数据结果时，用游标每移动一次就取这行数据，然后再移动游标，这个过程将是缓慢的。在使用游标的过程中，系统会给相应的表加上共享锁，导致锁竞争而严重影响数据库的性能。

?

在此不再介绍游标的实现方式，此法比较简单。

?

第二种：利用临时表和标志列

?

在Sybase ASE12.5.3及以后的版本中，我们可以用top关键字来限定只返回结果集的前N行数据。在ASE12.5.3之前的版本中只能用set rowcount N 的方法来“曲线救国”了。

对于取结果集的第N行至第N+M行数据的要求，我们考虑利用top来实现的话，比较容易想到的是：执行两次top，再加l两次倒序排序。

步骤如下：

　　　　　　　(1)　select top N+M * from table_name where_clause order by ID ??? 把此结果集派生为表:table_name1

????????????????? (2)?? select top M * from table_name1 order by ID? DESC ? 把此结果集派生为表：table_name2

????????????????? (3)?? select * from table_name2 order by ID? DESC

上面的3条语句好像能够实现返回第N行至第N+M行数据的要求。但是，在Sybase ASE中仅仅利用派生表而不利用临时表是不能实现这个要求的。

仅仅是ASE中的“派生出派生表(derived table)的SQL语句中不能含有order by 子句”这个限制就足以使上面的方法行不通。还有一个限制是，上面的3个步骤中都利用ID列进行排序。如果表中没有可用的排序列时，那么上述方法也不能用了。不过幸运的是，一般要求对其结果集进行分页的表都是有可以用作排序的列的（数字型或者日期型）。

?

继续寻找一个能用的方法，下面着重介绍目前通用的ASE的分页思路。此思路的关键是产生identity自增列和临时表。

在ASE中大家要是找到了不用临时表就可以实现分页的方法请麻烦告诉我一声。 我尝试了很多次，都不是很理想。

?

概括起来主要语句有两条：

?????????? (1)?? select syb=identity(10),*? into #temp_table from table_name where_clause ? order_by_clause

?????????? (2)?? select? * from #temp_table where_clause and syb >= N ? and syb <= N+M

?

用一个例子演示一下：

(1) 建立测试表:testA

?

create table testA(id int not null,name varchar(30) null) go

?

?

(2) 插入测试数据

?

insert into testA select 1,'liuzhenfu' go insert into testA select 2,'andkylee' go

?

?

(3) 循环插入大量的重复数据，

?

insert into testA select id+(select max(id) from testA),name from testA go 15

?

向表testA循环插入已有的数据，15次之后，表testA内的数据达到2^16 = 65536 行。

?

(4) 利用临时表 + 自增标志列来提取第100行至第200行的数据。

语句如下：

select syb=identity(10) ,* into #tempA from testA

select * from #tempA where syb>=100 and syb<=200

drop table #tempA

返回的结果为：

?

1> select syb=identity(10),* into #tempA from testA 2> select * from #tempA where syb>=100 and syb<=200 3> go (65536 rows affected) syb id name ------------- ----------- --------------------------- 100 100 andkylee 101 101 liuzhenfu 102 102 andkylee 103 103 liuzhenfu 104 104 andkylee 105 105 liuzhenfu 106 106 andkylee 107 107 liuzhenfu 108 108 andkylee 109 109 liuzhenfu 110 110 andkylee 111 111 liuzhenfu 112 112 andkylee 113 113 liuzhenfu 114 114 andkylee 115 115 liuzhenfu 116 116 andkylee 117 117 liuzhenfu 118 118 andkylee 119 119 liuzhenfu 120 120 andkylee 121 121 liuzhenfu 122 122 andkylee 123 123 liuzhenfu 124 124 andkylee 125 125 liuzhenfu 126 126 andkylee 127 127 liuzhenfu 128 128 andkylee 129 129 liuzhenfu 130 130 andkylee 131 131 liuzhenfu 132 132 andkylee 133 133 liuzhenfu 134 134 andkylee 135 135 liuzhenfu 136 136 andkylee 137 137 liuzhenfu 138 138 andkylee 139 139 liuzhenfu 140 140 andkylee 141 141 liuzhenfu 142 142 andkylee 143 143 liuzhenfu 144 144 andkylee 145 145 liuzhenfu 146 146 andkylee 147 147 liuzhenfu 148 148 andkylee 149 149 liuzhenfu 150 150 andkylee 151 151 liuzhenfu 152 152 andkylee 153 153 liuzhenfu 154 154 andkylee 155 155 liuzhenfu 156 156 andkylee 157 157 liuzhenfu 158 158 andkylee 159 159 liuzhenfu 160 160 andkylee 161 161 liuzhenfu 162 162 andkylee 163 163 liuzhenfu 164 164 andkylee 165 165 liuzhenfu 166 166 andkylee 167 167 liuzhenfu 168 168 andkylee 169 169 liuzhenfu 170 170 andkylee 171 171 liuzhenfu 172 172 andkylee 173 173 liuzhenfu 174 174 andkylee 175 175 liuzhenfu 176 176 andkylee 177 177 liuzhenfu 178 178 andkylee 179 179 liuzhenfu 180 180 andkylee 181 181 liuzhenfu 182 182 andkylee 183 183 liuzhenfu 184 184 andkylee 185 185 liuzhenfu 186 186 andkylee 187 187 liuzhenfu 188 188 andkylee 189 189 liuzhenfu 190 190 andkylee 191 191 liuzhenfu 192 192 andkylee 193 193 liuzhenfu 194 194 andkylee 195 195 liuzhenfu 196 196 andkylee 197 197 liuzhenfu 198 198 andkylee 199 199 liuzhenfu 200 200 andkylee (101 rows affected)

?

?

需要将select * from #tempA中的星号*替换为需要返回的列名。

?

继续。。。。

?

当要求返回满足name='andkylee'的所有行中的第100行至第200行的数据时， 利用

select syb=identity(10),* into #tempA from testA where name='andkylee'

select * from #tempA where syb>=100 and syb<=200

?

drop table #tempA

?

?

?

第三种：利用rowcount

此种方法有点不足：必须利用可用作排序的列 对结果集进行排序。

还是上面的测试表testA，如果从第9000行开始选择10行数据，那么语句如下：

declare @id1 int
set rowcount 9000
select @id1 = id from testA order by id
set rowcount 10
select *from testA where id >= @id1 order by id
set rowcount 0
go

?

此种方法中核心语句是select @id1=id from testA order by id ， 在对表testA执行查询的过程中，每读取一行都会把id列的值赋给@id1这个变量，一直持续到最后一行，@id1这个变量反复被下一行的id值刷新，结果只得到最后一样的id值。如果在此select语句之前加上rowcount的限定，那么就可用使得@id1这个变量获得第rowcount行的id值，那么我们也就获得了返回范围结果集的起点了。

后面的 set rowcount 10

???????? select * from testA where id >= @id1 order by id

这两句实际上可以用一句select top 10 * from testA where id >= @id1 order by id? 来替代。?

?

这样，两种不同的实现形式为:

declare @id1 int
set rowcount 9000
select @id1 = id from testA? order by id
set rowcount 0
select top 10? *from testA where? id >= @id1 order by id
go

?

分别看看执行结果吧。

?

1> declare @id1 int 2> set rowcount 9000 3> select @id1 = id from testA order by id 4> set rowcount 10 5> select *from testA where id >= @id1 order by id 6> set rowcount 0 7> go (9000 rows affected) id name ----------- ------------------------------ 9000 andkylee 9001 liuzhenfu 9002 andkylee 9003 liuzhenfu 9004 andkylee 9005 liuzhenfu 9006 andkylee 9007 liuzhenfu 9008 andkylee 9009 liuzhenfu (10 rows affected) 1>

?

第二种方式的结果：

?

1> declare @id1 int 2> set rowcount 9000 3> select @id1 = id from testA order by id 4> set rowcount 0 5> select top 10 *from testA where id >= @id1 order by id 6> go (9000 rows affected) id name ----------- ------------------------------ 9000 andkylee 9001 liuzhenfu 9002 andkylee 9003 liuzhenfu 9004 andkylee 9005 liuzhenfu 9006 andkylee 9007 liuzhenfu 9008 andkylee 9009 liuzhenfu (10 rows affected) 1>

?

当然，两种结果一模一样。

?

?

最后我们测试表testA中的ID列顺序值打乱， 来看看以上语句的执行情况。执行：

?

update testA set id = id + cast( rand() * 65536 as int )

?

?

ID列值打乱之后，前100行的数据为：

?

1> select top 100 * from testA 2> go id name ----------- ------------------------------ 51366 liuzhenfu 33573 andkylee 19447 liuzhenfu 19408 andkylee 57839 liuzhenfu 18817 andkylee ...................... 19075 liuzhenfu 17081 andkylee 26444 liuzhenfu 6620 andkylee 52344 liuzhenfu 49348 andkylee (100 rows affected)

?

?

?

我们要求返回满足name='andkylee'的从第9000行开始的10行数据。

declare @id1 int
set rowcount 9000
select @id1 = id from testA where name='andkylee' order by id
set rowcount 10
select *from testA where name='andkylee' and id >= @id1 order by id
set rowcount 0
go

?

结果为：

?

1> declare @id1 int 2> set rowcount 9000 3> select @id1 = id from testA where name='andkylee' order by id 4> set rowcount 10 5> select *from testA where name='andkylee' and id >= @id1 order by id 6> set rowcount 0 7> go (9000 rows affected) id name ----------- ------------------------------ 48639 andkylee 48639 andkylee 48641 andkylee 48641 andkylee 48642 andkylee 48643 andkylee 48644 andkylee 48644 andkylee 48650 andkylee 48650 andkylee (10 rows affected)

?

?

?

如果不对ID列进行排序， 有下面的sql语句：

declare @id1 int
set rowcount 9000
select @id1 = id from testA where name='andkylee'
set rowcount 10
select *from testA where name='andkylee' and id >= @id1?
set rowcount 0
go

?

相应的结果集为：

?

1> declare @id1 int 2> set rowcount 9000 3> select @id1 = id from testA where name='andkylee' 4> set rowcount 10 5> select *from testA where name='andkylee' and id >= @id1 6> set rowcount 0 7> go (9000 rows affected) id name ----------- ------------------------------ 74076 andkylee 74514 andkylee 74053 andkylee 74385 andkylee 74339 andkylee 74792 andkylee 74794 andkylee 74984 andkylee 75052 andkylee 74138 andkylee (10 rows affected) 1>

?

?

可以发现这个两句的结果是不同的。

?

我想既然都要求返回指定范围的结果集， 肯定是有排序的依据了， 否则怎么知道该返回哪个范围呢？

?

还有，我给出的第三种方法，在进行表扫描的时候，即使不指定排序，也是能够得到正确结果的。因为表扫描时很可能会按照表内数据在物理页面上的物理位置来返回结果。