Question

高效分页存储过程，对比一下?谢谢

下面是两个分页存储过程，id为自增标识也是主键
过程一：
select top N条记录 * from 文章表 where id not in(select top M条记录 id from 文章表 order by id desc ) order by id desc

过程二：
select top N条记录 * from 文章表 where id <(select min(id) from (select top M条记录 id from 文章表 order by id desc ) as tblTmp) order by id desc

过程一是用not in界定一个取值范围，过程二是限定一个取值条件，在我看来这两个过程没什么优劣之分，为什么太多太多的人都说not in效率最低呢？过程二在网上的例子很多，
是不是很多人都在用过程二分页呀，难道过程二效率就一定比过程一高吗？
请高手达人帮解释一下，这两个过程在执行原理上有什么不同呀，非常感谢啊！
祝愿身体棒棒、吃饭香香、爱情甜甜、事业顺顺，呵呵……

Answer 1

我在给你一个回答！
我一般都不会用你的那2种方法！
总之原因很多啦！
我是一典型的数据中心论者！
我用的就是这种方法！
本人感觉非常好！
推荐你也试试看！

在数据库中写语句！
create database mydb
go
use mydb
go
create table news
(id varchar(50) primary key,
name varchar(50)
)
go
insert into news values('020','sss')
insert into news values('021','ttt')
insert into news values('022','uuu')
insert into news values('001','aaa')
insert into news values('002','bbb')
insert into news values('003','ccc')
insert into news values('004','aaa')
insert into news values('005','ddd')
insert into news values('006','eee')
insert into news values('007','fff')
insert into news values('008','ggg')
insert into news values('009','hhh')
insert into news values('010','iii')
insert into news values('011','jjj')
insert into news values('012','kkk')
insert into news values('013','lll')
insert into news values('014','mmm')
insert into news values('015','nnn')
insert into news values('016','ooo')
insert into news values('017','ppp')
insert into news values('018','qqq')
insert into news values('019','rrr')
go
select * from news
go

create proc proc_cursor --定义存储过程
@pagesize int, --每页有多少条数据
@pageindex int, --第几页
@pagetotal int output --总页数
as
begin
declare @total int,@start int,@end int,@id varchar(10),@name varchar(10),@i int
--定义几个变量，作用后面解释

declare mycur scroll cursor
for
select * from news order by ID
--定义一个滚动游标

open mycur
--打开游标

set @total = @@cursor_rows
--得到总的记录数

if @total> 0
begin
if @total % @pagesize = 0
set @pagetotal = @total / @pagesize
else
set @pagetotal = @total / @pagesize + 1
--得到总页数

if @pageindex < 1 set @pageindex = 1
if @pageindex > @pagetotal
set @pageindex = @pagetotal
--检查输入页数，确保它在1到总页数之间

set @start = (@pageindex-1)*@pagesize+1
--游标第一次移动到的位置，比如我们需要第11到15条记录，那么这个参数的值是11
set @end= @pageindex*@pagesize
--游标第二次移动到的位置，如上，这个值应该是15
if @end > @total
set @end = @total
--确保第二次移动不超过记录的总条数

set @i=@start
while(@i<=@end)
begin
fetch absolute @i from mycur into @id,@name
print @id+' '+@name
set @i=@i+1
end
end
else
begin
set @pagetotal = 0
select top 0 * from news
end
close mycur
--关闭游标
deallocate mycur
--释放游标
end
go

declare @pagetotal int
exec proc_cursor 5,7, @pagetotal output
--执行存储过程
print ' '
print '共'+convert(varchar(10),@pagetotal)+'页'
go

drop proc proc_cursor
--删除存储过程
use master
go
drop database mydb
--删除数据库
go
然后你在直接调用就OK啦么！

Answer 2

SQL2005用row_number分页,对於SQL2000要有效的利用聚集索引或复合索引

1楼的观点是不完全正确的,
not in 有之前的版本SQL2000或7.0时大多数会用不到索引，需要指定索引.
not in时会解析为 or ...or

在SQL2005之后会用到索引，索引的使用与索引密度，数据量大小都有关。

楼主的情况，用大小符号"</>"时，是聚集索引时，一定会优於not in,非聚集索引时，要根据情况而定。首先条件列为非聚集索引，表有没有其它聚集索引或复合索引，最有效的判断方法。选中SQL语句查看执行计划(CTRL+L)看成本

Answer 3

1、个人认为在没有索引的情况下，两种效果是一样的。
2、但是如果有索引，就不一样了：
【not in】会导致数据库扫描数据库的全部数据，因为只有扫表完全部数据才能知道到底是不是【not in】，用不到索引
而忧【<】则会利用索引快速定义到需要数据，因此性能也就较比【not in】好。

---
以上，希望对你有所帮助。