一套新的SqlServer2005分页方案，很实用，很快！（总结了一下大家的讨论）,该怎么解决

2012-02-20

一套新的SqlServer2005分页方案，很实用，很快！（总结了一下大家的讨论）-----------------------以下为6月25

一套新的SqlServer2005分页方案，很实用，很快！（总结了一下大家的讨论）
-----------------------
以下为6月25日补充：
-----------------------
感谢大家的讨论，我总结一下集中讨论的焦点：

1、为什么要使用row方案：
在oracle里有row_number虚列，
mySql有limit关键字分页，
他们都有一个比较通用的分页方案，
使得hibernate等类似的程序可以拼接sql字符串提供通用的分页。
而sqlserver却没有这样的分页方案。
于是乎，本人稍稍改装row_number()over(order by )用法，获得了一个通用的分页方案。
如提供了sql如下：

SQL code

select * from Student where Age>18 order by Age

被row方案的分页程序处理后变成
（在select 后面添加 top 开始位置 0 __tc，在外层嵌套固定模式的查询sql）

SQL code

select *from (    select row_number()over(order by __tc__)__rn__,*    from (select top 开始位置+10 0 __tc__,* from Student where Age>18 order by Age)t)ttwhere __rn__>开始位置

这样就得到了拼接出通用的分页sql方案了。
并且经过本人测试发现，这套方案的运行速度不逊于任何一套其他方案。
其余各方面效率还有待考察，忘高人指点了。

2、row方案的排序：
row方案可以任意排序，
只要修改最内层的select排序即可，
应该来说是很简单易用的。
参考【追加说明1、】和【#80楼】。

3、row方案和普通row_number()方案的区别：
一般的row方案：

SQL code

select *from (select top 开始位置+10 row_number()over(order by Id)__rn__, * from Student)twhere __rn__>=开始的位置

使用了over(order by 表中的列)，照成了必须由用户提供这个列，
而不容易使用分页程序生成分页sql（如hibernate分页）。
而row方案使用的是一个常数列tempColumn，值永远是0。

SQL code

select *from (  select row_number()over(order by TempColmun) *  from (    select top 开始的位置 0 as TempColmun,*    from Student order by Id  )tt)twhere rowNumber >=开始的位置

这个列是静态的，只是为了使用row_number（）函数，
并不是真正的order by 依据，order by 实际看最内层。

我分析是因为row方案使用一个静态的列tempColumn，
这样可能被sql分析程序认为是无需排序的，省下了排序过程的开销。

4、数据测试：
现只在我一台机子上试过，
希望路过的各位随手帮忙测试一下。
这也是我迟迟不结贴的缘故。
举手之劳，复制sql运行即可：

SQL code

--插入测试数据200w条，可能会很久create table Student(  Id int PRIMARY KEY identity(1,1),  Name nvarchar(50),  Age int)insert Student(Name,Age)values('Name',18)while (select count(*) from Student)<2000000  insert Student select Name,Age from Student

运行测试代码：

SQL code

--开始测试查询declare @now datetime--max方案select 'max'方案select @now=getdate()--beginselect top 10 * from Studentwhere Id>(  select max(Id)  from (    select top 1999990 Id from Student order by Id)tt)--enddeclare @maxDiff intselect @maxDiff=datediff(ms,@now,getdate())--top方案select 'top'方案select @now=getdate()--beginselect top 10 * from Studentwhere Id not in(select top  1999990 Id from Student)--enddeclare @topDiff intselect @topDiff=datediff(ms,@now,getdate())--row方案select 'row'方案select @now=getdate()--beginselect *from (select row_number()over(order by tc)rn,*from (select top 2000000 0 tc,* from Student)t)ttwhere rn>1999990--enddeclare @rowDiff intselect @rowDiff=datediff(ms,@now,getdate())--row_number方案select 'row_number'方案select @now=getdate()--beginselect *from(select top 2000000 row_number()over(order by Id)rn,* from Student)twhere rn>1999990--enddeclare @row_numberDiff intselect @row_numberDiff=datediff(ms,@now,getdate())--记录结果select '第20万页'页码,@maxDiff max方案,@topDiff top方案,@rowDiff row方案,@row_numberDiff row_number方案

-----------------------
以下为原帖：
-----------------------

这套方案（下面简称row方案）是本人借鉴Oracle的row_number分页方法和sqlServerrow_number结合+上top分页方案合体版，经过本人初步测试。
效率非常快。（本人测试非常业余，还望高人帮忙测试。）
row方案的具体操作方法在这章帖子里：
一套原创的sqlserver通用分页方案忘高人测试效率先阿里嘎多了

比较了3种分页方式，分别是max方案，top方案，row方案

效率：
第1：row
第2：max
第3：top

缺点：
max：必须用户编写复杂Sql，不支持非唯一列排序
top：必须用户编写复杂Sql,不支持复合主键
row：不支持sqlServer2000

测试数据：
共320万条数据，每页显示10条数据，分别测试了2万页、15万页和32万页。

页码，top方案，max方案，row方案
2万，60ms，46ms，33ms
15万，453ms，343ms，310ms
32万，953ms，720ms，686ms

具体操作sql代码如下：

top方案：

SQL code

select top 10 * from Table1where Id not in(select top 开始的位置 Id from Table1)

max:

SQL code

select top 10 * from Table1where Id>(select max(Id)from (select top 开始位置 Id from Table1order by Id)tt)

row:

SQL code

select *from (    select row_number()over(order by tempColumn)tempRowNumber,*    from (select top 开始位置+10 tempColumn=0,* from Table1)t)ttwhere tempRowNumber>开始位置

[解决办法]
路过,关注中.............
[解决办法]
关注.............
[解决办法]
路过贴不沉
[解决办法]
关注中，一般情况下还是使用 row
[解决办法]
谢谢LZ分享！
非常感谢
[解决办法]
row_number() 是个好东西，很早就用过！
[解决办法]
学习了。
[解决办法]
帮顶了
[解决办法]
up 一下
[解决办法]
表连接如果没有索引，用row_number()效率相当低。
[解决办法]
2005还没用过
[解决办法]
不排序的分页讨论的有意义么？

select *
from (
select row_number()over(order by tempColumn)tempRowNumber,*
from (select top 开始位置+10 tempColumn=0,* from Table1)t
)tt
where tempRowNumber>开始位置

tempcolumn都等于0，那个order by就相当于没有，你见过哪家分页不排序的？
可能你要用的顺序恰好跟现在的顺序一致，不用排序，但这样有局限性的SQL讨论的有意义么？
[解决办法]
关注、
[解决办法]
row_number() 快点
[解决办法]
顶，学习了！
[解决办法]
学习了.
[解决办法]
顶一下，关注学习
[解决办法]
嗯。这个早都流传开了。
比较不错。顶一个。
[解决办法]
顶学习了！~
------解决方案--------------------

select top 10 * from Table1
where Id not in(select top 开始的位置 Id from Table1)

怎么用上 not in 了？？？？
[解决办法]
先顶再看
[解决办法]
正好最近打算重写以前的数据库操作类, 可以把楼主的分页和我以前写的做次比较
[解决办法]
帮顶。
[解决办法]
不错。。不错。。，顶顶先!
[解决办法]
最近正好要学习一下数据库
[解决办法]
UP 学习
[解决办法]

探讨
UP 学习

[解决办法]
顶下~不错
[解决办法]
呵呵给你顶顶
[解决办法]
up[u][/u]
[解决办法]
不错，支持
[解决办法]
mark~~~~~~~~~~
[解决办法]
感谢LZ
收下
[解决办法]
鄙视一下楼主跟那群盲目跟贴的人。
[解决办法]
收藏下
[解决办法]
还在用2000，将来看看!~
[解决办法]
看看，
[解决办法]
这个有用，收藏下
[解决办法]

探讨
不只是没去别，是更慢了。。。费解ing，=待高人解说。

[解决办法]
你這裡的id是主鍵嗎？如果是，多個order肯定會慢，要是大家都沒索引呢？

幫頂，希望有更多方面的比較。
[解决办法]
关注中~学习
[解决办法]
关注。。。
[解决办法]
顺路看看~！
[解决办法]
关注中，正在搞分页
[解决办法]
以前测试过,row_number有个缺陷，就是会把结果列全部由硬盘读取到内存中，如果结果列中有text文章之类的或者结果列很多，执行效率明显下降。
[解决办法]
干得好，
支持
[解决办法]
学习！
[解决办法]
弱弱的问下...SQL2000支持row_number么？！
[解决办法]
mark
[解决办法]

探讨
测试数据：
共320万条数据，每页显示10条数据，分别测试了2万页、15万页和32万页。

页码，top方案，max方案，row方案
2万，60ms，46ms，33ms
15万，453ms，343ms，310ms
32万，953ms，720ms，686ms

------解决方案--------------------

都是牛人啊
[解决办法]
我觉得 TOP MAX 速度还可以，很少用rownumber
[解决办法]
这个sql分页语句的确写的不错，比一般的SQL分页语句效率要高些，值得引用！

[解决办法]
MARK,Good good study,Day day up。
[解决办法]
关注........
[解决办法]
一直关注效率问题，但是没有实际的数据来源，也就无法实验测试了
[解决办法]
以前用top，现在在用rownumber()。。。因为上次换服务器升成sql server 2005了，新特性不用白不用
刚开始也没看懂楼主的写法，如果最里层写成

SQL code

select top 1500010 0 as tempColumn,* from table1 order by column1 desc
[解决办法]
这是你自己创建的么，我也用过这中类似的方法啊，其原理不就是索引么

[解决办法]
http://topic.csdn.net/u/20100608/13/49434117-00f0-4f97-b311-cf96d129d282.html

帮我测试我下 这个 怎么样`
[解决办法]
学习一下...
[解决办法]
看看。。。
[解决办法]
还以为是什么新东西

  ROW_NUMBER() ... OVER() 就是SQL Server 2005/2008 的首先翻页方式， LINQ to SQL、ADO.NET Entity Framework 中生成的翻页的ESQL（第一页的除外）全是使用 ROW_NUMBER() ... OVER()
[解决办法]
mark
[解决办法]
厉害哦，学习学习
[解决办法]
学习ing。。。。
[解决办法]
js的

热点排行