Access分页方案

http://www.cnblogs.com/huanghai/archive/2011/03/19/1988627.html 开门见上，本文主要是回答一下三个问题： Access是否存在更有效率的分页方法？ 现有Access大数据量10万条数据分页的效率测试 Access的数据承载量到底有多大？ 相信很多ASP的站点还在使用Ac

http://www.cnblogs.com/huanghai/archive/2011/03/19/1988627.html

开门见上，本文主要是回答一下三个问题：

1. Access是否存在更有效率的分页方法？
2. 现有Access大数据量10万条数据分页的效率测试
3. Access的数据承载量到底有多大？

相信很多ASP的站点还在使用Access数据库，因为Access数据库无须开专门的数据库空间，调用，迁移也方便，节省费用。另外对网站搭建者的专业能力要求也相对低一些。但随着网站的运行，数据库体积越来越大，数据量也从最初的几百条到了现在的上万条，上十万条甚至更多。于是因数据应用级别的改变带来的各种各样的应用问题出现了。而其中大数据量的列表分页效率问题更是让很多人头疼。笔者随便通过“大数据量分页效率”，“Access 分页”等关键词分别百度和谷歌了一下，发现有此疑问的大有人在。很多网页上也给出了不同的解决办法。那么，这些方法到底能达到优化效率，提高速度的目的吗？

先让我们来看看以下的几个Access分页优化方案，当然如果你直接将数据库升级到SQL Server，那么有更好的诸如存储过程等方法。今天我们就讨论一下Access大数据量优化分页方法，以及Access到底能承受多少数据量。

方案一：利用ado本身的结果集的pagesize，AbsolutePage的属性来进行分页
程序示例：（仅供示意，完善的各种条件判断自行添加）

<ol>
<li><span>MaxPerPage=20    </span></li>
<li>
<span>page=cint(request(</span><span>"page"</span><span>))     </span>
</li>
<li>
<span>sql=</span><span>"select * from 表 where 条件 order by 排序条件"</span><span>   </span>
</li>
<li>
<span>set</span><span> rst=server.CreateObject(</span><span>"adodb.recordset"</span><span>)    </span>
</li>
<li>
<span>rst.</span><span>open</span><span> sql,conn,1,1    </span>
</li>
<li><span>rst.pagesize=MaxPerPage    </span></li>
<li><span>rst.AbsolutePage = Page   '将记录定位到对应页数的第一条    </span></li>
<li>
<span>for</span><span> i=1 </span><span>to</span><span> MaxPerPage      循环列表  </span>
</li>
<li><span>      rst.movenext  </span></li>
<li>
<span>     if rst.eof </span><span>then</span><span> exit </span><span>for</span><span>   </span>
</li>
<li>
<span>next</span><span>   </span>
</li>
</ol>

这个方法是最为常用的Access分页方法。

缺点：每次都要读入符合条件的所有记录，然后再定位于对应页的记录。当数据量大的时候，效率就十分的低下。
与此相似的方法是利用ado的move方法，每次将记录集游标移动 (page-1)*pagesize ，就实现了了记录的分页。经过测试，效率与方案一大致相同。

方案二：

1.设置一个自增长字段.并且该字段为INDEX.

2.由于是 Access ,所以,只能是前台分页.自增长字段目的,就是为了实现分页功能.

1> 记录用户前页的最后一个 自增值 ,例如 M .

3> 前台循环取 RS 的前 PAGESIZE 条, 写到一个 新的RS中,并返回.

这个方案通过自增值来分部截取不同分页的数据列表，文中考虑到数据库有增删操作，所以加入了一个系数的概念，这是一个不得已的做法。这个方案可以保证分页效率，但只能运用于增删不太频繁（自增值字段相邻记录的值相差不多的情况）的数据表。

方案三：not in 方法。

<ol><li><span><span>sql=</span><span>"select top 12 * from 表 where Id not in(select top page*pagesize Id from 表 order by id desc) order by Id desc"</span><span> </span></span></li></ol>

如果是第9页，每页20条即

<ol><li><span><span>select</span><span> </span><span>top</span><span> 20 * </span><span>from</span><span> 表 </span><span>where</span><span> Id </span><span>not</span><span> </span><span>in</span><span>(</span><span>select</span><span> </span><span>top</span><span> 9*20 Id </span><span>from</span><span> 表 </span><span>order</span><span> </span><span>by</span><span> id </span><span>desc</span><span>) </span><span>order</span><span> </span><span>by</span><span> Id </span><span>desc</span><span> </span></span></li></ol>

原理即：选择top 20 的记录，条件是id不在前面分页的记录ID里。通过这种方式过滤掉前面分页的记录，然后通过top高效率的方式获取当页的记录。

“top”确实高效，但是“not in”呢？

于是我直接用这种方法测试了一下，测试条件：10万条数据。点击查询.......... MY GOD，长时间无响应，最后Ctrl+Alt+Delete 结束任务。再试，结果同样如此。于是改变一下测试条件，变成1000条数据，OK，结果显示非常顺利。

结论：如果你是大数据量分页，还是不要用这种方法，会死人的。 

方案四：

<ol><li><span><span>"select * from (select top "</span><span>&pagesize&</span><span>" * from (select top "</span><span>&page*pagesize&</span><span>" * from 表"</span><span> </span><span>order</span><span> </span><span>by</span><span> id </span><span>desc</span><span>) </span><span>order</span><span> </span><span>by</span><span> id) </span><span>order</span><span> </span><span>by</span><span> id </span><span>desc</span><span>" </span></span></li></ol>

这个方法简单说来，就是选取当前页及小于当前分页的所有记录，再通过“Top”方式选取当前页的记录。

这个方法没有出现效率低的语句，虽然至少要select两次（示例select了三次是为了排序）。但是效率应该不错。且越靠前的分页应该越明显。

如果还想节省效率，可以只select两次。

假如记录ID为1-100，每页5条。现在显示第4页，排序为倒序。

执行顺序：

1) 选择前4页的数据，即100-81共20条数据

2) 从这20条数据中选择最小的5条，即81-85。

3) 将选择的5条按倒序排，即成为 85-81。

如果节省第三步也可，只不过显示变成

第一页：96，97，98，99，100

第二页：91，92，93，94，95

其实也不错。

光说没用，最终看测试结果。我在相同的数据条件，服务器配置下，分别对方案一中的两种方法和方案四进行了Access分页效率测试，测试数据如下

测试条件：

>10万条；pagesize=20；

分页总数>5000页；

顺便也进行了一下“select 部分字段”和“select 所有字段”的对比测试。

从上面的测试结果来看，方案三的优势还是比较明显的。而到5000页的效率基本上和前两种方法差不多，甚至仍然有一定得优势。

另外，很多人在写select语句时， 习惯 select * from 表，这不是一个好习惯。上面的Access分页测试结果表明，还是按需索要，按需供应的好，需要什么字段，就select什么字段。能够极大的节省服务器资源。

很多网友提到Access时都不免的轻视，“你还在用Access？”，“还不换SQL Server？”，“用Access你还想多快？”。其实在我的经验看来，即便是在10万条的应用级别上。Access常常比SQL Server快。因为SQL Server需要额外连接，且多了一个带宽连接因素的影响（当然，网站服务器和数据库服务器运行速度和带宽都很OK，那没话说）。

SQL Server 在更高数据级别上的速度优势还是比较明显，毕竟与Access不是一个级别的产品。

为了探索一下Access数据库的极限。在40万条数据的情况下进行了上述分页测试。速度确实大打折扣。但是第三种方案在一万页内还是表现不错的。此时数据库已经达到400多兆。再结合之前处理过的几个4，500兆的Access数据库。我认为40万条数据是Access数据库在一般应用的一个界限，但不是极限。超过这个数，就需要在程序优化上做太多的工作。就有些不太值了。