[转]Sql server 大数据量分页存储过程效率测试附代码

本文转自:http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html

在项目中,我们经常遇到或用到分页,那么在大数据量(百万级以上)下,哪种分页算法效率最优呢?我们不妨用事实说话。

测试环境

硬件:CPU 酷睿双核T5750  内存:2G

软件:Windows server 2003    +   Sql server 2005

OK,我们首先创建一数据库:data_Test,并在此数据库中创建一表:tb_TestTable

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码GO

[转]Sql server 大数据量分页存储过程效率测试附代码

然后我们在数据表中插入2000000条数据:

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码set identity_insert tb_TestTable off

[转]Sql server 大数据量分页存储过程效率测试附代码

我首先写了五个常用存储过程:

1,利用select top 和select not in进行分页,具体代码如下:

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码end

[转]Sql server 大数据量分页存储过程效率测试附代码

2,利用select top 和 select max(列键)

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码end

[转]Sql server 大数据量分页存储过程效率测试附代码

3,利用select top和中间变量--此方法因网上有人说效果最佳,所以贴出来一同测试

 
[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

4,利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引

 
[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

5,利用临时表及Row_number

 
[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

OK,至此,存储过程创建完毕,我们分别在每页10条数据的情况下在第2页,第1000页,第10000页,第100000页,第199999页进行测试,耗时单位:ms  每页测试5次取其平均值

存过 第2页耗时 第1000页耗时 第10000页耗时 第100000页耗时 第199999页耗时 效率排行
1用not in 0ms 16ms 47ms 475ms 953ms 3
2用select max 5ms 16ms 35ms 325ms 623ms 1
3中间变量 966ms 970ms 960ms 945ms 933ms 5
4row_number 0ms 0ms 34ms 365ms 710ms 2
4临时表 780ms 796ms 798ms 780ms 805ms 4

测试结果显示:select max >row_number>not in>临时表>中间变量

于是我对效率最高的select max方法用2分法进行了扩展,代码取自互联网,我修改了ASC排序时取不到值的BUG,测试结果:

2分法 156ms 156ms 180ms 470ms 156ms 1*

从测试结果来看,使用2分法确实可以提高效率并使效率更为稳定,我又增加了第159999页的测试,用时仅296ms,效果相当的不错!

下面是2分法使用select max的代码,已相当完善。

 
[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

[转]Sql server 大数据量分页存储过程效率测试附代码

执行示例:exec proc_paged_2part_selectMax 'tb_testTable','ID,userName,userPWD,userEmail',10,100000,'ID',0,null,'ID',0

这种测试只在单机进行,并且没有在实际开发WEB项目中分页测试,测试项也比较单一,所以不够全面系统,但从其效率相比上,我们可以在数据库分页算法上进行有效的控制。

上一篇:关于如何在电脑上安装adb来操作手机(Android)的方法及步骤


下一篇:前端面试题总结(二)CSS篇