【测试】两种数据库,四种分页算法的效率比较

分页算法本身没有什么快慢之分,对反应速度起到决定作用的是——能否有效地利用索引!

算法

评价

缺点

适用的数据库

max

效率最高的

只能有一个排序字段

Excel、Access、SQL Server 2000(2005)

颠倒Top

适用范围最广的

最后一页多数据

Excel、Access、SQL Server 2000(2005)

表变量

最大失所望的

太多了

SQL Server 2000(2005)

Row_Number

只适合SQL Server2005

只能用在一种数据库

SQL Server 2005

一、max

     缺点就是只能有一个排序字段,而且排序字段的值不能有重复值,或者说可以有重复值,但是不能在一页的最上面、最后重复。优点就是很容易设置索引,按那个字段排序就把那个字段设置上索引就可以了,设置索引后,分页速度会大大提高,记录越多越明显,因为利用了索引。索引的作用是什么?简单地说就是,先站排,再找个头最高的。假设说要找到一个班级里面个头最高的,那要怎么办呢,先让同学按照大小个站排,然后谁是最高的就一目了然了。设置好了索引就相当于已经排好了队,剩下的就快多了。

二、颠倒Top 

     这个是颠颠倒倒的一种优化,必须有一个主键,且不能是复合主键。由于大部分情况都比较好设置索引,也可以利用索引,所以分页效果也是可以的。

三、表变量

     由于一直在使用颠倒top法和定位法,所以一直对表变量不感冒,由于是吴旗娃的分页控件推荐的一种算法,这次升级的时候还是去研究了一下。仔细看了一下,缺点还真是多。由于表变量的思路是,要把主键和自增字段放在表变量里面,然后利用自增字段来分页。就像上面的例子,要先站排,然后报数,然后根据报数来提取数据。这样就带来了很多的缺点:

     1、表必须有一个主键,且不能是复合主键。复合主键的话,就不好提取数据了。      2、主键的类型会影响分页算法的编写。int的和datetime的,在定义表变量的时候字段类型是不一样的。      3、如果只按照主键排序的话,那么主键的索引就完全被浪费掉了。原先已经排好队了,却又让她们重新排一遍。      4、越往后翻页,需要往表变量里存放的数据也就越多,放的多倒无所谓,其他的算法也会放一些数据到“临时表”里面,但是问题在于,自增字段没有索引,加的数据越多速度也就自然越慢。      5、只能在SQL Server 2000和SQL Server2005里面使用,而且在2005里面速度明显没有Row_Number快,吴旗娃的那个网站推荐的分页算法,都分成了两个版本,SQL2000的和SQL2005的,对于后者推荐使用Row_Number。

     说了这么多的缺点,不会一点优点都没有吧,有点恐怕就是编写起来比较容易吧,便于理解。颠倒Top就不好理解。所以说这个是最大失所望的分页算法。

四、Row_Number

     这个我只会用,内部原理也不太理解,大多数情况效率也还行,但是有一次发现他不能利用索引,郁闷。

     光说不练,恐怕大家也不太相信,尤其说了表变量那么的坏话,那么下面就用测试来证明一下,看看各个分页算法的表现。要说测试也真麻烦,数据库至少就有三个,分页算法有四个,数据上呢又有单字段排序、多字段排序,单表分页、多变关联分页,少量数据和海量数据,能否利索引。

3*4*2*2*2*2 = 192 。这么多是不是有点晕,先简化一下吧,这里先用SQL Server2000 测试一下单表海量数据的情况。

三种分页算法、单表、海量数据(二百万吧,少了对比不出来效果)。

先介绍一下测试环境

数据库: SQL Server2000

IDE:VS2008。

CPU:AMD3000+

内存:DDR2 1G

硬盘:串口160G

三种分页算法:Max、表变量、颠倒top。

     我们使用Northwind数据库,Products表进行测试,自带的数据不够,我们来复制点数据吧,用insert into select 的方式,添加2523136条记录,一页显示15条记录,一共有168210页 。那么我们就记录一下第一次加载、前几页、前100页、第1000页、第10000页、最后几页和最后一页的用时和内存占用情况。

这是单字段排序的测试结果,按照  ProductID  排序。 单位:毫秒。

分页算法

第一次

前几页

前100页

1000页

10000页

100000页

最后几页

最后一页

MaxMin

953/40M

400/40M

0-15/50M

0-15/50M

78-93/68M

3.62s/210M 765/210M

2.80s/310M 1.28s/310M

5.62s/310M 1.28s/310M

颠倒Top

875/40M

400/40M

0-15/50M

15-31/50M

281/69M 93-125/69M

2.96s/210M 1.3s/210M

3.78s/310M 1.7s/310M

15(特殊处理)

表变量

968

468

0-15/50M

11.18s/55M 93-109/59M

24.265s/76M 859/77M

超时了 45.171s/230M 11.78s/240M 8.281s

这个就不测试了

这个就不测试了

     记录说明:

     1、如果一个格里面有两个时间,那么前面的表示大范围跳页(比如从100页跳到1000页)需要的时间,后面是显示下一页需要的时间。

     2、前面的是执行时间,后面的是内存增量。

     3、第一次运行的时候SQL Server需要加载一些数据到内存里面,所以时间比较长。

     4、第一页的时候需要使用Count(*)来统计总记录数,所以时间也有点长。而在访问其他页的时候就不用统计总记录数了,所以时间会很快。

     5、CPU的占用率就不记录了,基本上都占满了,看来AMD3000+有点弱了。

第一页的SQL语句:

select top 15 * from Products order by ProductID

MaxMin的SQL语句: select top 15 * from Products where productid>= (SELECT max(productid ) from (select top 526 productid from Products order by productid ) as t ) order by productid

颠倒Top的SQL语句: select * from Products where productid in ( select top 15 productid from ( select top 420 productid from Products order by productid ) as t order by t.productid desc )order by productid

对最后一页得分页算法作了特殊处理,目的是去掉bug,并不是为了提高速度。

颠倒Top的显示最后一页的SQL语句 select * from ( select top 1 * from Products order by productid desc ) as t order by t.productid

表变量的SQL语句: declare @tt table(id int identity(1,1),nid int) insert into @tt(nid) select top 2130 ProductID from Products order by ProductID select * from Products t1, @tt t2 where t1.ProductID =t2.nid and t2.id between 2116 and 2130

还真的是不行,从第一万页跳转到第十万页的时候,很不幸,等待了45.171秒之后超时了,由于数据访问函数库在遇到异常的时候并不抛出异常,所以能够得到运行的时间,通过查看错误日志,得知“超时时间已到。在操作完成之前超时时间已过或服务器未响应。” 再次刷新才得到了数据。

在测试的时候表变量的分页时间很不稳定,一会快一会慢的,大范围跳页的时候还总是超时。而颠倒Top就比较稳定。

这是多字段排序的测试结果,按照   UnitPrice,ProductID desc  排序。设置了索引:UnitPrice,ProductID desc 单位:毫秒。

分页算法

第一次

前几页

100页

1000页

10000页

100000页

最后几页

最后一页

颠倒Top

375/55M

15-31/57M

15-31/60M

62-78/63M

687/63M 486/64M

5.18s/65M 4.3s/65M

8.15s/66M 7-8s/66M

15(特殊处理)

表变量

968

15-31/58M

1.31/59M 46/60M

12.17s/60M 93-125/60M

真实搞不懂,怎么超时了 27.39s/76M 8-9s/77M 900/77M

超时了 45.171s/100M 10.2s/100M 9.3s

27.2s/100M 9.3s

这个就不测试了

这是SQL Server 2005 的测试结果,多字段排序,按照   UnitPrice,ProductID desc  排序。设置了索引:UnitPrice,ProductID desc 单位:毫秒。

分页算法

第一次

前几页

100页

1000页

10000页

100000页

最后几页

最后一页

颠倒Top

375/55M

15-46/57M

15-46/100M

78-93/110M

531/117M 466/117M

5-8s/118M

7-10s/120M

15(特殊处理)

表变量

968

31-46/100M

2.62/100M 0.4s 或1.6s/100M

17.34s/110M 93-156/110M

25.57s/76M 1.25s/77M

超时了 22.82s/130M 15.2s/130M 9.3s

27.2s/100M 9.3s

这个就不测试了

Row_Number

500

15/93M

546 15-31

4.82s/200M 93-106/210M

3.15/265M 3.96/263M

比较慢,总超时,不想测了。

这里增加了Row_Number算法的测试,

Row_Number的SQL语句:

with t_pager as (select myIndex = ROW_NUMBER() OVER (ORDER BY UnitPrice,ProductID desc ),* from Products ) select * from t_pager where myIndex between 150031 and 150045

我都怀疑这个测试结果,如果测试结果没有问题的话,那么就说明“颠倒top”的效率是最高的,而且适用范围也很广。

但是我还是比较怀疑这个结果,难道 Row_Number会这么慢?是不是拼接出来的SQL语句有什么问题?或者那个环节出了问题?

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

 测试用的代码

先建立一个基类,定义一个GridView、一个QuickPager分页控件和一个标签,然后override OnInit()函数,再添加一个事件就ok了。因为是对同一个表进行分页,所以分页控件的属性设置都是一样的,只是分页算法的属性不同,那么我们就可以把相同的设置放在基类里面,不同的放在具体的页面里。

QuickPager 分页控件在基类里面的使用方法

public class BaseList : System.Web.UI.Page
    {
        protected JYK.Controls.QuickPager myPager = null;
        protected System.Web.UI.WebControls.GridView GV = null;
        protected Label lbl = null;

        protected DateTime dt1;

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);

            //设置分页控件的属性

            myPager.ControlGrid = GV;

            myPager.TableName = "Products";
            myPager.TableShowColumns = "*";
            myPager.TableIDColumns = "ProductID";
            //myPager.TableOrderColumns = "ProductID";ProductName
            myPager.TableOrderColumns = "UnitPrice,ProductID desc";

            myPager.PageSize = 15;

            //添加事件
            myPager.GridBinded += new JYK.Controls.QuickPager.EventPageChange(myPager_GridBinded);

            dt1 = DateTime.Now;
        }

        void myPager_GridBinded(object sender, JYK.Controls.Page.PageArgs e)
        {
            TimeSpan ts = DateTime.Now - dt1;
            lbl.Text = "秒:" + ts.Seconds + ",毫秒:" + ts.Milliseconds;
            lbl.Text += "<BR>" + myPager.GetPagerSQL;
        }

        void myPager_PageChanged(object sender, JYK.Controls.Page.PageArgs e)
        {
           
        }
    }

然后建立一个aspx页面,拖拽进来三个控件就可以了

<asp:GridView ID="GV" runat="server" Width="100%">
        </asp:GridView>
        <JYK:QuickPager ID="myPager" runat="server"  />
    <asp:Label ID="lbl" runat="server" Text=" "></asp:Label>
public partial class lst_Max : BaseList
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack )
                myPager.SetSQLKind = JYK.Controls.Page.myPageSQLKind.Row_Number;
        }
    }

     我们还可以顺便对比一下SQL 2000 和 SQL 2005,好像在这种情况下05并没有太多的优势。Row_Number也没有想象中的那么好,也许是我写的不对,也许四、五个排序字段的时候才能体现出来优势?不过不管怎么说,更换分页算法对于QuickPager来说是很容易的事情,如果发现速度慢了,那么就换成其他的分页算法试一试,再加个索引试一试,呵呵。

QuickPager 分页控件 v2.0.0.8的下载地址:

http://www.cnblogs.com/jyk/archive/2008/07/29/1255891.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java Web

Java Web -【分页功能】详解

分页简介 分页功能在网页中是非常常见的一个功能,其作用也就是将数据分割成多个页面来进行显示。 使用场景: 当取到的数据量达到一定的时候,就需要使用分页来进...

59530
来自专栏岑玉海

Hive Tuning(一) 连接策略

群里共享了一本hive调优的书记,名叫《Hive Tunning》,就忍不住开始看了,也顺便记录一下自己学到的东西,备忘! 首先,这是hive的数据摘要,别...

41460
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十二天 Web商城实战二【悟空教程】

`cname` varchar(20) DEFAULT NULL, #分类名称

25840
来自专栏扎心了老铁

spark-streaming集成Kafka处理实时数据

在这篇文章里,我们模拟了一个场景,实时分析订单数据,统计实时收益。 场景模拟 我试图覆盖工程上最为常用的一个场景: 1)首先,向Kafka里实时的写入订单数据,...

83750
来自专栏程序员的SOD蜜

如何用ORM支持SQL语句的CASE WHEN?

OQL如何支持CASE WHEN? 今天,一个朋友问我,OQL可否支持CASE WHEN语句?他给的示例SQL如下: select HName,case whe...

26380
来自专栏我是攻城师

ElasticSearch入门之彼行我释(四)

35750
来自专栏哲学驱动设计

Rafy 领域实体框架设计 - 重构 ORM 中的 Sql 生成

前言 Rafy 领域实体框架作为一个使用领域驱动设计作为指导思想的开发框架,必然要处理领域实体到数据库表之间的映射,即包含了 ORM 的功能。由于在 09 年最...

35470
来自专栏烙馅饼喽的技术分享

本人有生以来的第一篇博客,嘿嘿,就发这个吧, 怎样在虚拟主机上使用Castle框架的ActiveRecord

        我在某个私人项目中使用了Castle 的 ActiveRecord.用起来那是真叫个爽,整个项目里楞是一句SQL语句都没有,嘿嘿。超级喜欢上了这...

27850
来自专栏WindCoder

网易MySQL微专业学习笔记(四)-实践课数据库对象

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

8610
来自专栏小狼的世界

locale的详细解释

* Thu Sep 27 2001 Bernhard Rosenkraenzer 2.5-0.f.2

9640

扫码关注云+社区

领取腾讯云代金券