一套新的SqlServer2005分页方案，很实用，很快！
发布时间：2020-12-12 14:40:56 所属栏目：MsSql教程 来源：网络整理
导读：感谢大家的讨论，我总结一下集中讨论的焦点： 1、为什么要使用row方案： 在oracle里有row_number虚列， mySql有limit关键字分页， 他们都有一个比较通用的分页方案， 使得hibernate等类似的程序可以拼接sql字符串提供通用的分页。 而sqlserver却没有这样的分
|
感谢大家的讨论，我总结一下集中讨论的焦点：
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 )tt
where
__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)t
where
__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)t
where
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
()
--
begin
select
top
10
*
from
Student
where
Id
>
(
select
max
(Id)
from
(
select
top
1999990
Id
from
Student
order
by
Id)tt)
--
end
declare
@maxDiff
int
select
@maxDiff
=
datediff
(ms,
@now
,
getdate
())
--
top方案
select
'
top
'
方案
select
@now
=
getdate
()
--
begin
select
top
10
*
from
Student
where
Id
not
in
(
select
top
1999990
Id
from
Student)
--
end
declare
@topDiff
int
select
@topDiff
=
datediff
(ms,
getdate
())
--
row方案
select
'
row
'
方案
select
@now
=
getdate
()
--
begin
select
*
from
(
select
row_number()
over
(
order
by
tc)rn,
*
from
(
select
top
2000000
0
tc,
*
from
Student)t )tt
where
rn
>
1999990
--
end
declare
@rowDiff
int
select
@rowDiff
=
datediff
(ms,
getdate
())
--
row_number方案
select
'
row_number
'
方案
select
@now
=
getdate
()
--
begin
select
*
from
(
select
top
2000000
row_number()
over
(
order
by
Id)rn,
*
from
Student )t
where
rn
>
1999990
--
end
declare
@row_numberDiff
int
select
@row_numberDiff
=
datediff
(ms,
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
Table1
where
Id
not
in
(
select
top
开始的位置 Id
from
Table1)
max: SQL code
select
top
10
*
from
Table1
where
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 )tt
where
tempRowNumber
>
开始位置
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |