老杂毛博客
  青青子衿,悠悠我心。但为君故,沉吟至今。
博客首页 | 人生历程 | 编程开发 | 机关门  
  文章分类
 
博客首页 > 软件网络编程 > 文章正文

速度快高效率分页存储过程详解

2010/4/29 12:33:26
 

       记得以前写过一篇相似的文章:“分页存储过程详解”,可能优化的不好吧,在百度上并没有得到好的排名,所以关注度也不是很高,不但关注程度不高,就连效率也不是特别高,虽然以前就知道用not in的方法效率不高,但一直没有找到过特别大量的数据做过测试,昨天遇到一个好机会,让我好一番测试,最后发现之前的分页存储过程效率确实不行,尤其是十万条数据以上,基本就崩溃了。

       后来就把代码改了改,速度一下子快了几十倍,下面就把我改的代码和大家分享一下,在分享这段代码之前请你一定要把前面的那篇分页存储过程详解看明白,因为我是在那基础上改的代码,这篇文章并没有详细的去讲解每一条代码的意思。

分页存储过程原理分析:

      我们要把大量的数据分页显示,以前在asp里或其它程序里的做法是把数据全部取出来,然后根据不同的页码用程序去计算应该显示哪部分,如果数据量过大的话,可以想像每次都要调出来很多没用的数据,是不是会很慢呢,而分页存储过程解决了这一问题,每次只调出有用的数据,其余部分的数据并不会传输到程序里,这样就给数据的传输大大增加了效率。

      假如我们有一百条数据,现在要分二十页显示,一页显示五条。
      第一页:1至5条,第二页:6至10条,第三页:10至15条。

分页存储过程思路分析:

      在上一篇分页存储过程详解的文章里我们介绍的思路是这样的,取第一页数据的时候,top 5就可以了,取第二页数据的时候top 10,然后 id not in 前五条,取第三页数据的时候就是 top 15,然后not in 前十条数据,那么我们取第二十页数据的时候,就是top 100,然后not in前95条数据。

      可以看到上面这种方法,随着数据量的增大 not in计算的数据也要增大,而且有的时候有条件的话还需要一次性把所有的数据取出来,然后再排序取前面几条,然后再not in,而下面介绍这种方式完全不需要把全部数据取出来。

高效分页存储过程思路分析:
  
      如何不通过not in方法取出自己想要的数据呢,很简单,我们只需要用top就可以了,说一下简单的思路,比如取第6条到第十条数据的时候我们可以先取前十条数据,按时间排序(top 10),然后我们再从这十条数据里按时间倒序排序取前五条数据,这样取出来的数据就是从10到6条了,然后我们再正序排一下序就是从6到10条了,怎么样,简单吧,下面我们来写一下这条sql语句。

高效分页存储过程Sql语句讲解:

     我们还是举上面的例子,取从第6到第10条数据:

     第一步,我们要取从第一到第十条数据:select top 10 * from table order by  id

     第二步,我们在这十条数据里反取五条:

     select top 5 * from table where id in (select top 10 id from table order by  id ) order by id desc

     Ok,运行下以上这条句话,我们已经得到第6到第10条了,只不过顺序是反的,那么我们再排一下序给正过来就可以了。

    第三步,正序排序数据:

    select top 5 * from table where id in
    (select top 5 id from table where id in (select top 10 id from table order by  id ) order by id desc)
    order by id

    好了,到这里,我们的第六条到第十条,也就是第二页的内容就算是取出来了。

    下面我们要想办法把这个代码构造成一个存储过程,通用于任何表,任何条件,任何条数的数据,这样的话,我们就有几个地方需要改成变量了,首先每页的条数需要改成变量,还有就是表名和条件都要改成变量,最后我们还要把排序字段也要改成变量。

构造存储过程详细代码:

     创建存储过程之前先定义变量
CREATE  procedure SplitPage(
 @ColList varchar(4000)='', --显示字段
 @Table VARCHAR(4000)='',--查询的表,from后面的语句
 @Where varchar(4000)='',--查询条件,where后面的语句
 @Sort VARCHAR(255)='',--排序条件,不带表名的字段名且不带asc,desc,
 @AscDesc VARCHAR(255)='',--排序asc,desc,
 @PageSize int=0,--每页记录数
 @PageIndex int=0,--当前页码
 @ID VARCHAR(255)='',--ID字段,例如"memberid"
)
 
--每个变量后面都加了详细的注释,这个不用我解释了吧,如果你还不明白的话,看看后面我们怎么用就行了。

DECLARE @Sql nVARCHAR(4000)

if(@Where='')
begin
 set @Where='1=1'
end
--如果条件为空的话则给一个1=1永远为真的条件,你也可以在下面的语句中把where删除掉,从页面上传进来

     set @Sql='select top '+CAST(@PageSize AS VARCHAR(20))+' '+@ColList+' from '+@Table+' where '+@ID+' in
 (
 select top '+CAST(@PageSize AS VARCHAR(20))+' '+@ID+' from '+@Table+' where '+@ID+' in
 (select top '+CAST((@PageSize*@PageIndex) AS VARCHAR(20))+' '+@ID+' from '+@Table+' where '+@Where+' order by '+@Sort+' '+@AscDesc+')
  order by '+@ID+' asc
 ) order by '+@ID+' '+@AscDesc

EXEC(@Sql)--执行sql语句


总结:

        OK,到这里我们就算是把这个高效的分页存储过程讲解完了,但是还有一个小小的不足之处,排序是自已写好的,没有从页面上传进来,你可以再定义一个变量存储包含desc或asc的变量然后完成按要求排序。

        这个存储过程要比上次写的简单多了吧,如果你还有更精进的建议或意见请在下面给我留言,谢谢

        念再我辛苦花一个多小时整理的份儿上,如果你感觉我说的有用,请留个言支持一下,文章原创,转载请注明作者和出处(老杂毛)

阅读: (3857)  评论(36)  
 
网友评论:
 
 1.匿名网友2010/5/3 18:09:47 
谢谢分享,速度果然比之前的快,非常感谢
 2.匿名网友2010/5/3 19:59:24 
用游标速度快吗?
 3.古董2010/5/4 12:36:42 
我不懂电脑。只是利用这来与你联系,是你的签名,让我产生了与你进行的想法。前两天我让朋友做了一个扇子,上面的题字就是,天道酬勤。静以致远
 4.匿名网友2010/5/5 17:03:57 
讲的非常棒,支持了
 5.匿名网友2010/5/11 17:55:37 
正愁找不到呢,谢谢楼主了
 6.匿名网友2010/5/18 18:26:06 
今天你的文章 吸引了我 让我加班留下来看你的文章 55555

好久没这感觉了 这个分页 我会好好研究研究
 7.匿名网友2010/5/18 18:26:19 
加我啊 喜洋洋
 8.奋奋2010/5/31 23:43:21 
我想问你一个问题,你的这些文章是不是写在数据库里,然后通过ASP读取出来的?还是像平时写网页那样一个个网页写出来的?(不要笑我问这么低级的问题喔!)
 9.匿名网友2010/6/8 11:43:18 
呵呵,虽然还是不太懂,谢谢了
 10.匿名网友2010/6/22 9:47:53 
对我很有启发,我也是做C#的,希望多多联系,谢谢
 11.匿名网友2010/6/26 0:20:40 
第一、像你这种分页可能要实现多字段排序比较麻烦
第二、如果数据量大的话我觉得进行一次倒序再取数据不一定是最佳处理方法
 12.匿名网友2010/6/30 11:04:18 
可以加你QQ吗
 13.61.49.121.42010/12/13 9:46:59 
世间万物有利有弊,心境就好。
 14.113.57.146.1312010/12/17 22:04:54 
同样不是都要查出一个子查询:select top size*index id from table,index越大,子查询的结果集越大,和以前的方法没多少改进啊?没看明白。初学,别见笑。
 15.113.57.146.1492010/12/18 15:23:49 
在下面网址看到一个这样的分页,不知这个效率如何,我没看明白什么意思。http://www.mikel.cn/2010/08/20/%E8%BD%AC%E8%BD%BDmssql%E5%88%86%E9%A1%B5%EF%BC%9A%E4%B8%80%E4%B8%AA%E9%AB%98%E6%95%88%E7%9A%84%E4%BA%8C%E5%88%86%E6%B3%95top-maxtop-min%E5%88%86%E9%A1%B5%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B/
我自己一直用的是
http://www.newxing.com/Tech/Database/MSSQL/198.html这里讲的方法。
 16.124.126.225.1102011/1/11 11:47:14 
讲的不错,谢谢分享
 17.213.229.142.1472011/2/7 22:17:11 
不错  谢谢搂主
 18.119.255.48.1502011/4/14 9:40:01 
本人菜鸟,在asp.net中怎么调用这个存储过程?谢谢,知道的发我邮箱~~谢谢了
 19.118.122.85.1882011/6/29 6:24:32 
程序不错,不过每页20个记录,超过5000页,速度不就理想了 (:
 20.124.200.18.1062011/10/19 14:19:47 
我需要处理千万级数据,用in的话,再怎么都不会很快
 21.183.39.115.912011/11/30 15:26:16 
很基础的东西
 22.2016/6/13 7:02:51 
 23.2016/6/16 20:48:24 
 24.2016/6/21 11:59:08 
 25.2016/6/25 23:55:21 
 26.2016/11/10 3:28:54 
 27.2016/12/7 17:38:50 
 28.2017/1/3 16:02:14 
 29.2017/4/2 3:31:41 
 30.2017/12/4 11:45:16 
 31.2017/12/19 16:47:28 
 32.2018/1/3 18:02:26 
 33.2018/1/18 21:05:01 
 34.2018/2/2 19:03:00 
 
相关文章:
 微信支付asp.net V3.3.7 sys
 ERRORLOG 文件太大的处理办法
 Attempted to read or w
 Ctrl键自动按下,Ctrl键和Alt总感觉
 解决 Your project contai
 adb install 多设备id号一样,如
 如何去掉优酷,土豆等网站的广告?
 webview的loadData方法加载乱码
 让iis7支持apk文件下载
 sql利用游标循环,遍历表循环结果集
 
 
     
联系作者: QQ: 657603425 Email: 657603425@qq.com
Copyright © 2007-2014 www.laozamao.com All Rights Reserved
京ICP备09002242号 北京海淀分局备案号:1101084842