SQL Server 存储过程的分页方案比拼 - 达人(阿Ken)

2005/06/12 | SQL Server 存储过程的分页方案比拼
类别(SQL) | 评论(0) | 阅读(106) | 发表于 14:05: CSDN - 文档中心 - 数据库开发阅读：17580 评论： 5 参与评论

标题 SQL Server 存储过程的分页方案比拼选择自 lihonggen0 的 Blog
关键字 SQL Server 存储过程的分页方案比拼
出处

SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

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

分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID

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

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID

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

分页方案三：(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用

在实际情况中，要具体分析。

更多的讨论见：
community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515

作者Blog：http://blog.csdn.net/lihonggen0/
相关文章
在VB6和VB.NET中进行图象捕获
SQL Server 2005安装及界面截图，新增功能．
在VB6和VB.net中设置桌面墙纸
SQL Server 存储过程的分页方案比拼
SQL Server中发送邮件的新方式

对该文的评论
hujunx ( 2004-10-04)
方案二是不實用的 ,
但需要改造，就可以任意增加条件进行查询分页
CSDN 网友 ( 2004-09-26)
啊.游标的没看懂.
CSDN 网友 ( 2004-09-20)
而且，select top 的方法如果加了条件的话，肯定要在内层和外层都加条件在大容量检索中会非常影响效率
CSDN 网友 ( 2004-09-20)
方案二是不實用的，现实应用中，哪里有ID与你所期望的排序相一致的情况，通常是根据时间，或者自定义的条件排序，那么MAX（ID）根本沒有任何意義，因为无法反映顺序
CSDN 网友 ( 2004-09-16)
我不认为游标分页效果很差，
因为上面两种都没有取得所查记录集的个数即
select count(id) from table,
一般情况下分页查询都需要查出查询sql返回记录集的个数
select count(id) from table,在１００万条记录中速度相当慢，
那么两个查询加起来的效率比游标存储过程分页要慢很多，
记录越大相差越明显，我测试过100万条相差几钞钟，
400万条则相差10-20秒，

【评论】【关闭】【报告bug】; 0

http://jybbh.5d.cn/

评论Comments