专栏首页更流畅、简洁的软件开发方式分页控件之分页算法 —— for SQL Server 版。

分页控件之分页算法 —— for SQL Server 版。

上两篇随笔:

我的分页控件(未完,待续)——控件件介绍及思路

我自己写的一个分页控件(源码和演示代码)PostBack分页版 for vs2003、SQL Server

关于分页的误区

    误区1:分页的时候,只有使用存储过程,效率才高。

    误区2:忽略了索引的作用。

上两篇好像介绍的不太详细,这里详细说明一下分页控件里使用的分页算法,也就是SQL语句。

分页一般分为四种情况

1、单字段排序,排序字段没有重复值。

2、单字段排序,排序字段有重复值。

3、多字段排序,最后一个排序字段没有重复值。

4、多字段排序,最后一个排序字段有重复值。

其中第2、4 情况都可以再加一个排序字段(比如说主键),就可以转换成第三种情况。

所以分页针对1、3两种情况设置了两种分页算法。

1、单字段排序,排序字段没有重复值。

    公式:

declare @col int



select top {PageSize * (PageIndex-)+} @col = [排序字段] 

from [表名|视图名] 

[ where 查询条件 ] 

order by [排序字段] asc|desc



select top PageSize 需要显示的字段 

from [表名|视图名] 

where [排序字段] >= @col

[ and 查询条件 ]

order by [排序字段] asc|desc

    以NorthWind 数据库里的 Products 表为例,假设一页显示10条数据,CategoryID = 3 为查询条件,按照ProductID 倒序,如果想显示第二页的数据,那么SQL语句就是

declare @col int



select top  @col = ProductID from Products where CategoryID =  order by ProductID desc    



select top  * from Products where ProductID >= @col and CategoryID =  order by ProductID desc

   说明:

    第一行的定义,要根据字段类型来修改,看是比较麻烦,但是这个麻烦交给分页控件就可以了,使用者,只要设置分页控件的属性就可以了。

    第五行和第十一行,如果需要加查询条件的话就可以在这里添加。

    第三行是一个“定位”,这个可以算是SQL Server 所特有的吧,也是SQL Server 很宽容的地方。以Products 表的例子,执行完第一条select 语句之后, @col 里面记录的是 在CategoryID = 3 的记录里面,按照ProductID 倒序,排行在11位的记录的值。

    第一个select 语句定位以后,第二个select 语句就可以根据这个“位置”继续向下查找数据了。

    虽然例子里面使用了ProductID(主键)来排序,但是并不是说这个算法只能用主键来排序,哪个字段都可以,但是要符合第一种情况,就是“只有一个排序字段,且排序字段里的记录没有重复值”!

3、多字段排序,最后一个排序字段没有重复值。

    如果 Products 表想要用 UnitPrice 字段来排序怎么办呢?上面的算法是不适合的,我们需要使用另一种算法,这个和颠颠倒倒法有些类似,但是我做了一些优化。

    公式:

select [需要显示的字段] from [表名|视图名] where [主键字段] in 



    ( select top PageSize [主键字段] from 

        (select top {PageSize * PageIndex} [主键字段] , [排序字段] from     --有几个排序字段就写几个字段



            [表名|视图名] 

            [ where 查询条件 ] 

            order by 

            

                [排序字段1] asc|desc ,

                [排序字段2] desc|asc, 

                

                [主键字段] asc|desc     

        ) as aa     

        order by     

            [排序字段1] desc|asc,        --如果上面是倒序,那么这里就是正序,下同

                [排序字段2] asc|desc ,

                    

                    [主键字段] desc|asc 



    ) 



order by 

    [排序字段1] asc|desc,    --如果上面是倒序,那么这里就是正序,所谓颠颠倒倒嘛。

    [排序字段2] desc|asc,

    

    [主键字段] asc|desc

    以NorthWind 数据库里的 Products 表为例,假设一页显示10条数据,CategoryID = 3 为查询条件,按照UnitPrice 倒序,由于UnitPrice 字段可能有重复值,所以加上一个排序字段——ProductID ,即按照 UnitPrice  desc,ProductID  来排序。 如果想显示第二页的数据,那么SQL语句就是

select * from Products where ProductID in 

    ( select top  ProductID from 

        (select top  ProductID , UnitPrice from     

            Products 

            where CategoryID = 

            order by 

                UnitPrice desc ,

                ProductID      

        ) as aa     

        order by     

            UnitPrice asc,        --如果上面是倒序,那么这里就是正序,下同

                ProductID desc 

    ) 

order by 

    UnitPrice desc,    --如果上面是倒序,那么这里就是正序,所谓颠颠倒倒嘛。

    ProductID

    说明:

    1、这里查询条件加一次就可以了。

    2、是不是看 asc|desc 倒来倒去的有点晕,恩,这就对了,颠颠倒倒嘛。

    3、最主要的就是第三个select 语句,他要取从第一条数据到要显示的页的数据,可见越是后面的记录,top n 就会越大,所以这里提取的数据就要做一个精简,只写排序需要的字段(主键字段和排序字段)。

    4、第二个select 语句是去掉前面不需要的页里的数据,只保留要显示的页号里的数据。

    5、第一个select 语句,用主键字段 in () 的方式提取其他需要的字段。

    6、这种分页算法有一个小的bug,就是显示最后一页数据的时候,会多出来几条记录,不过这个bug已经在分页控件里面修正了,最后一页的分页算法,采用特殊的select语句。

    7、效率,设置好索引,效率是没有问题的,上一篇随笔已经测试过了。

    8、这种算法有一个“侵入性”,就是要求表必须有主键,而且不能是联合主键,引为要用 in 的方式查询数据。但是并没有要求主键自身必须能够排序。

测试效果

记录数:2523136条。

一页显示5条记录。

//分页算法1 单字段排序,且排序字段是聚集索引。    //1000 页以内 15毫秒    //10000页以内 30毫秒    //50000页以内 100多毫秒    //100000页以内 200多毫秒    //最后几页 第一次跳转到 4秒多    //最后几页 连续向前翻页 1秒156毫秒

   //页号大范围跳转的时候需要的时间比较长,但是也小于1秒,同时SQL Server 占用的内存有所增加 120M。最后几页时达到320M

=================================================================== 以下是多排序字段的分页情况,排序字段是 UnitPrice,ProductID      //分页算法2 无索引  首页 8秒187毫秒 。     //10 页以内 2秒812毫秒    //速度太慢下面的就不测试了

   //分页2 非聚集索引 UnitPrice  首页 468毫秒    //10 页以内 2秒671毫秒    //速度太慢下面的就不测试了

   //分页算法2 非聚集索引 UnitPrice,ProductID  首页 500毫秒    //10 页以内 2秒796毫秒    //100页以内 4秒796毫秒    //速度太慢下面的就不测试了

   //分页算法2 非聚集索引 UnitPrice,ProductID desc  首页 500毫秒    //10 页以内 0-15毫秒    //100页以内 15-46毫秒    //1000页以内 31-62毫秒    //10000页以内 100毫秒左右    //50000页以内 400-500毫秒    //100000页以内 900毫秒左右    //最后几页 第一次跳转到 4秒421毫秒    //最后几页 连续向前翻页 4秒375毫秒

   //页号大范围跳转的时候需要的时间比较长,但是也小于1秒,    //这回SQL Server 占用的内存增加幅度不大 120M左右

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【自然框架】之鼠标点功能现(一):单表的增删改查(即上次5月23日活动的一个主题)【Demo、源码下载】

    简单的需求,点点鼠标就可以了,那么复杂的需求呢?还是要写代码,哈哈。 不要被我误导了哦,关于什么时候写代码的问题,请看这里:http://www.cnblogs...

    用户1174620
  • 【自然框架】 之 资源角色——列表过滤方案(思路篇)

    名词解释 1、资源角色,我的理解就是资源过滤方案 + 角色。就是把资源过滤方案和角色结合在一起的东东。 2、资源:这里的资源指的是关系数据库里的记录。 3、资...

    用户1174620
  • js的动态加载、缓存、更新以及复用(四)

      本来想一气呵成,把加载的过程都写了,但是卡着呢,所以只好在分成两份了。   1、页面里使用<script>来加载 boot.js 。   2、然后在boot...

    用户1174620
  • Entity Framework ModelFirst尝试

    Model First我们称之为“模型优先”,这里的模型指的是“ADO.NET Entity Framework Data Model”,此时你的应用并没有设计...

    aehyok
  • 分布式监控系统Zabbix3.2添加自动发现磁盘IO并注册监控

       zabbix并没有给我们提供这么一个模板来完成在Linux中磁盘IO的监控,所以我们需要自己来创建一个,在此还是在Linux OS中添加。   由于一台服...

    欢醉
  • 上个小菜:大型网站的访问全流程

    访问一个大型网站,当你输入www.sina.com.cn网址后,几秒后,在网页中显示了具体内容,这一切经历了什么?其实台上一分钟,台下十年功,背后发生了很多事,...

    希望的田野
  • Python数据库操作 MySQL数据库与数据表操作#学习猿地

    create database if not exists tlxy default charset=utf8;

    学习猿地
  • Python数据库操作 MySQL数据库与数据表操作#学习猿地

    create database if not exists tlxy default charset=utf8;

    学习猿地
  • 资深专家深度剖析Kubernetes API Server第3章(共3章)

    在本系列的前两部分中我们介绍了API Server的总体流程,以及API对象如何存储到etcd中。在本文中我们将探讨如何扩展API资源。

    用户1108251
  • Web自动化之Headless Chrome编码实战

    IMWeb前端团队

扫码关注云+社区

领取腾讯云代金券