ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql 分页方法对比及整合方案.

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

原创 Linux操作系统 作者:gloot 时间:2009-01-06 17:42:13 0 删除 编辑

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的数据的时候,页面总是显示超时.

 

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



Link URL: http://blog.sina.com.cn/s/blog_5116ad5f0100ahpo.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/586303/viewspace-530057/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-06

  • 博文量
    19
  • 访问量
    10297