加载中…
个人资料
周伟东
周伟东
  • 博客等级:
  • 博客积分:0
  • 博客访问:56,957
  • 关注人气:6
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
正文 字体大小:

sql 分页方法对比及整合方案.

(2008-09-27 10:34:51)
标签:

sql

分页

查询

对比

min

max

row_number

it

1. Row_Number方法
select top 20 * from
(
 select *,Row_Number() over(order by id desc) as Num from testTbl
) c where Num >= (2 * 20 + 1)

2. min方法
select top 20 * from testTbl
where id <
(
 select min(id) as id from (select top ((99999-1) * 20) id from testTbl order by id

desc) as T
) order by id desc

3. max方法
select top 20 * from testTbl
where id >
(
 select max(id) as id from (select top (10 * 20) id from testTbl order by id asc) as

T
) order by id asc

 

测试格式(页数 秒)

143万数据--------每页10条--------------------------------------------------
Row_Number方法
2 3
10 2
100 2
500 2
1000 3
5000 3
10000 3
15000 3 开始变慢,比max,min慢 150000数据左右
20000 4
30000 3
40000 5
50000 6
70000 6
80000 9
100000 7
150000 7


max方法
2 1
10 1
100 4
500 4
1000 4
5000 3
10000 4
15000 4
20000 4
30000 4
40000 4
50000 4
70000 4
80000 4
100000 4
140000 4
200000 6

min方法
2 2
10 2
100 4
500 4
1000 4
5000 4
10000 4
15000 4
20000 4
30000 4
40000 4
50000 4
70000 4
80000 4
100000 4
140000 4

==================================================================

200万数据测试 ---------------------每页20条---------------------
Row_Number方法
2 3
10 2
100 2
500 3(2)
1000 4
5000 4
10000 6
20000 6 开始变慢,比max,min慢 400000数据左右
30000 8
50000 9
80000 10
90000 11
99999 11

min方法
2 4
10 7
100 7
500 7
1000 7
5000 7
10000 8
20000 7
30000 7
50000 7
80000 7
90000 7
99999 7

max方法
2 3
10 7
100 7
500 7
1000 7
5000 7
10000 7
20000 7
30000 7
50000 7
80000 7
90000 7
99999 7

表testTbl 字段id,FieldName SQL SERVER 2005

以上搜索没有对id设聚集索引

以上是对两种规模数据下测试出来的数据,可以看得出在某个数据点处,Row_Number比较,之后min,max 会比较快, 而且min,max之后会比较稳定,而Row_Number会随着页数据增加搜索时间比较越来越长.

 

从两种测试结可以看得出,这个RowNumber变慢的临界点是不一样的.

 

所以我们在设计搜索分页的时候, 不仅要根据数据范围的不同采取不同的搜索方法,也要根据数据总量的不同对搜索做出相应的修改.

 

比如在第一种情况(143万数据时)可以这样:

1-100000数据左右可以用Row_Number来搜索, 之后可用min来搜索.

也可对半来搜索,前一半用min, 后一半用max(只是这时候可能还要再嵌一层,排序需要)

 

或者是当 第一页时是直接select top ... order by 即可

         @PageIndex < @totalPage / 2 时

              @PageIndex * @PageSize < 100000 用 Row_Number

              @PageIndex * @PageSize > 100000 时用 min

         @PageIndex > @totalPage / 2 时

              1.(@totalPage-@PageIndex) * @PageSize > 100000 时用max

              2.(@totalPage-@PageIndex) * @PageSize < 100000 时用Row_Number

@totalPage 是总页数. 这时候可能还要再嵌一层,排序需要

 

当然在2000000数据的时候,100000按照上面就要改成400000左右. 这样大概就会比较合理.

 

前两天在做一个分页的存储过程, 后面数据(Row_Number)都是用min, 当@PageIndex比较大是, min方法中的子查询的select top (@PageIndex - 1) * @PageSize会很大. 查10几W的数据的时候,页面总是显示超时.

 

但是像上面那样写就不会了.

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有