SQL Server 索引结构及其使用（三）

zhushihe111

　　实现小数据量和海量数据的通用分页显示存储过程 　　 　　建立一个 Web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机。 　　 　　更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。 　　 　　最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。 　　 　　后来，网上有人改造了此存储过程，下面的存储过程就是结合我们的办公自动化实例写的分页存储过程： 　　 　　CREATE procedure pagination1 　　(@pagesize int, --页面大小，如每页存储20条记录 　　@pageindex int --当前页码 　　) 　　as 　　 　　set nocount on 　　 　　begin 　　declare @indextable table(id int identity(1,1),nid int) --定义表变量 　　declare @PageLowerBound int --定义此页的底码 　　declare @PageUpperBound int --定义此页的顶码 　　set @PageLowerBound=(@pageindex-1)*@pagesize 　　set @PageUpperBound=@PageLowerBound @pagesize 　　set rowcount @PageUpperBound 　　insert into @indextable(nid) select gid from TGongwen 　　where fariqi >dateadd(day,-365,getdate()) order by fariqi desc 　　select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t 　　where O.gid=t.nid and t.id>@PageLowerBound 　　and t.id”或“200 　　于是就有了如下分页方案： 　　 　　select top 页大小 * 　　from table1 　　where id> 　　(select max (id) from 　　(select top ((页码-1)*页大小) id from table1 order by id) as T 　　) 　　order by id 　　 　　在选择即不重复值，又容易分辨大小的列时，我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表，在以GID（GID是主键，但并不是聚集索引。）为排序列、提取gid,fariqi,title字段，分别以第1、10、100、500、1000、1万、10万、25万、50万页为例，测试以上三种分页方案的执行速度：（单位：毫秒） 　　

登录/注册后可看大图

　　从上表中，我们可以看出，三种存储过程在执行100页以下的分页命令时，都是可以信任的，速度都很好。但第一种方案在执行分页1000页以上后，速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势，后劲仍然很足。 　　 　　在确定了第三种分页方案后，我们可以据此写一个存储过程。大家知道SQL SERVER的存储过程是事先编译好的SQL语句，它的执行效率要比通过WEB页面传来的SQL语句的执行效率要高。下面的存储过程不仅含有分页方案，还会根据页面传来的参数来确定是否进行数据总数统计。 　　 　　--获取指定页的数据： 　　 　　CREATE PROCEDURE pagination3 　　@tblName varchar(255), -- 表名 　　@strGetFields varchar(1000) = ''*'', -- 需要返回的列 　　@fldName varchar(255)='''', -- 排序的字段名 　　@PageSize int = 10, -- 页尺寸 　　@PageIndex int = 1, -- 页码 　　@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 　　@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 　　@strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where) 　　AS 　　 　　declare @strSQL varchar(5000) -- 主语句 　　declare @strTmp varchar(110) -- 临时变量 　　declare @strOrder varchar(400) -- 排序类型 　　 　　if @doCount != 0 　　begin 　　if @strWhere !='''' 　　set @strSQL = "select count(*) as Total from [" @tblName "] where " @strWhere 　　else 　　set @strSQL = "select count(*) as Total from [" @tblName "]" 　　end 　　--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况： 　　 　　else 　　begin 　　if @OrderType != 0 　　begin 　　set @strTmp = "(select max" 　　set @strOrder = " order by [" @fldName "] asc" 　　end 　　 　　if @PageIndex = 1 　　begin 　　if @strWhere != '''' 　　 　　set @strSQL = "select top " str(@PageSize) " " @strGetFields " 　　from [" @tblName "] where " @strWhere " " @strOrder 　　else 　　 　　set @strSQL = "select top " str(@PageSize) " " @strGetFields " 　　from [" @tblName "] " @strOrder 　　--如果是第一页就执行以上代码，这样会加快执行速度 　　 　 <