DataGrid基于Access的快速分页法

【 tulaoshi.com - Web开发 】

　　DataGrid是一个功能非常强大的ASP.NET Web服务器端控件，它除了能够方便地按各种方式格式化显示表格中的数据，还可以对表格中的数据进行动态的排序、编辑和分页。使Web开发人员从繁琐的代码中解放。实现DataGrid的分页功能一直是很多初学ASP.NET的人感到棘手的问题，特别是自定义分页功能，实现方法多种多样，非常灵活。本文将向大家介绍一种DataGird控件在Access数据库下的快速分页法，帮助初学者掌握DataGrid的分页技术。

　　目前的分页方法

　　DataGrid内建的分页方法是使用诸如SELECT * FROM TABLE的SQL语句从数据库表中取出所有的记录到DataSet中，DataGrid控件绑定到该DataSet之后，它的自动分页功能会帮你从该DataSet中筛选出当前分页的数据并显示出来，其他没有用的数据将被丢弃。

　　还有一种方法是使用自定义分页功能，先将DataGrid的AllowCustomPaging属性设置为True，再利用DataAdapter的Fill方法将数据的筛选工作提前到填充DataSet时，而不是让DataGrid帮你筛选：

public int Fill (　DataSet dataSet, //要填充的 DataSet。　int startRecord, //从其开始的从零开始的记录号。　int maxRecords, //要检索的最大记录数。　string srcTable //用于表映射的源表的名称。);

　　该方法首先将来自查询处的结果填充到DataSet中，再将不需要显示的数据丢弃。当然，自定义分页功能需要完成的事情还不止这些，本文将在后面详细介绍。

　　以上两种方法的工作原理都是先从数据库中取出所有的记录，然后筛选出有用的数据显示出来。可见，两种方法的效率基本上是一致的，因为它们在数据访问阶段并没有采取有效的措施来减少Access对磁盘的访问次数。对于小数量的记录，这种开销可能是比较小的，如果针对大量数据的分页，开销将会非常巨大，从而导致分页的速度非常的慢。换句话说，就算每个DataGrid分页面要显示的数据只是一个拥有几万条记录的数据库表的其中10条，每次DataGrid进行分页时还是要从该表中取出所有的记录。

　　很多人已经意识到了这个问题，并提出了解决方法：用自定义分页，每次只从数据库中取出要显示的数据。这样，我们需要在SQL语句上下功夫了。由于Access不支持真正的存储过程，在编写分页算法上就没有SQL Server那么自由了。SQL Server可以在存储过程中利用临时表来实现高效率的分页算法，受到了广泛的采用。而对于Access，我们必须想办法在一条SQL语句内实现最高效的算法。

　　用一条SQL语句取得某段数据的方法有好几种。算法不同，效率也就不同。我经过粗略的测试，发现效率最差的SQL语句执行时耗费的时间大概是效率最高的SQL语句的3倍！而且这个数值会随着记录总数的增加而增加。下面将介绍其中两条常用的SQL语句。

　　为了方便接下来的讨论，我们先约定如下：

　　变量

　　定义

　　先让我们看看效率最差的SQL语句：

SELECT TOP @PageSize * FROM @TableNameWHERE @PrimaryKey NOT IN (　SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName　ORDER BY @PrimaryKey ASC) ORDER BY @PrimaryKey ASC 

　　这条SQL语句慢就慢在NOT IN这里，主SELECT语句遍历的每个@PrimaryKey的值都要跟子SELECT语句的结果集中的每一个@PrimaryKey的值进行比较，这样时间复杂度非常大。这里不得不提醒一下大家，平时编写SQL语句时应该尽量避免使用NOT IN语句，因为它往往会增加整个SQL语句的时间复杂度。

　　另一种是使用了两个TOP和三个ORDER BY的SQL语句，如下所示：

SELECT * FROM (　SELECT TOP @PageSize * FROM (　SELECT TOP @PageSize*(@PageIndex+1) * FROM @TableName　　ORDER BY @PrimaryKey ASC　) TableA ORDER BY @PrimaryKey DESC) TableB ORDER BY @PrimaryKey ASC 

　　这条SQL语句空间复杂度比较大。如果要显示的分页面刚好是最后一页，那么它的效率比直接SELECT出所有的记录还要低。因此，对于分页算法，我们还应该具体情况具体分析，不能一概而论。下面将简单介绍一下相关概念，如果您对主键和索引非常熟悉，可以直接跳过。

　　有关主键和索引的概念

　　在 ACCESS中，一个表的主键（PRIMARY KEY，又称主索引）必然是唯一索引（UNIQUE INDEX），它的值是不会重复的。除此之外，索引依据索引列的值进行排序，每个索引记录包含着一个指向它所引用的数据行的指针，这对ORDER BY的执行非常有帮助。我们可以利用主键这两个特点来实现对某条记录的定位，从而快速地取出某个分页上要显示的记录。

　　举个例子，假设主键字段为INTEGER型，在数据库表中，记录的索引已经按主键字段的值升序排好（默认情况下），那么主键字段值为11的记录的索引，肯定刚好在值为12的记录的索引前面（假设数据库表中存在主键的值为12的记录）。如果主键字段不具备UNIQUE约束，数据库表中将有可能存在两个或两个以上主键字段的值为11的记录，这样就无法确定这些记录之间的前后位置了。

　　下面就让我们看看如何利用主键来进行数据的分段查询吧。

　　快速分页法的原理

　　其实该分页法是从其他方法衍生而来的。本人对原来的方法认真地分析，发现通过优化和改进可以非常有效地提高它的效率。原算法本身效率很高，但缺乏对具体问题的具体分析。同一个分页算法，可能在取第一页的数据时效率非常高，但是在取最后一页的数据时可能反而效率更低。

　　经过分析，我们可以把分页算法的效率状态分为四种情况：

　　（1）@PageIndex = @FirstIndex

　　（2）@FirstIndex @PageIndex = @MiddleIndex

　　（3）@MiddleIndex @PageIndex @LastIndex

　　（4）@PageIndex = @LastIndex

　　状态（1）和（4）分别表示第一页和最后一页。它们属于特殊情况，我们不必对其使用特殊算法，直接用TOP就可以解决了，不然会把问题复杂化，反而降低了效率。对于剩下的两种状态，如果分页总数为偶数，我们可以看作是从数据库表中删掉第一页和最后一页的记录，再把剩下的按前后位置平分为两部分，即前面的一部分，也就是状态（2），后面的为另一部分，也就是状态（3）；如果分页总数为奇数，则属于中间页面的记录归于前面的部分。这四种状态分别对应着四组SQL语句，每组SQL语句由升序和降序两条SQL语句组成。

　　下面是一个数据库表，左边第一列是虚拟的，不属于该数据库表结构的一部分，它表示相应记录所在的分页索引。该表将用于接下来的SQL语句的举例中：

　　由表可得：@PageSize = 2，@RecordCount = 10，@PageCount = 5

　　升序的SQL语句

　　（1）@PageIndex = @FirstIndex

　　取第一页的数据是再简单不过了，我们只要用TOP @PageSize就可以取出第一页要显示的记录了。

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @Condition ORDER BY @PrimaryKey ASC

　　（2）@FirstIndex @PageIndex = @MiddleIndex

　　把取数据表前半部分记录和取后半部分记录的SQL语句分开写，可以有效地改善性能。后面我再详细解释这个原因。现在看看取前半部分记录的SQL语句。先取出当前页之前的所有记录的主键值，再从中选出最大值，然后取出主键值大于该最大值的前@PageSize条记录。这里@PrimaryKey的数据类型可以不是INTEGER类型，CHAR、VARCHAR等其他类型照样可以。

SELECT TOP @PageSize @QueryFieldsFROM @TableNameWHERE @PrimaryKey  (SELECT MAX(@PrimaryKey) FROM (SELECT TOP @PageSize*@PageIndex @PrimaryKeyFROM @TableNameWHERE @ConditionORDER BY @PrimaryKey ASC) TableA) WHERE @ConditionORDER BY @PrimaryKey ASC

　　例如：@PageIndex=1，红--黄--蓝

　　（3）@MiddleIndex @PageIndex @LastIndex

　　接下来看看取数据库表中后半部分记录的SQL语句。该语句跟前面的语句算法的原理是一样的，只是方法稍微不同。先取出当前页之后的所有记录的主键值，再从中选出最小值，然后取出主键值小于该最小值的前@PageSize条记录。

SELECT * FROM (SELECT TOP @PageSize @QueryFieldsFROM @TableNameWHERE @PrimaryKey  (SELECT MIN(@PrimaryKey) FROM (SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKeyFROM @TableNameWHERE @ConditionORDER BY @PrimaryKey DESC) TableA) WHERE @ConditionORDER BY @PrimaryKey DESC) TableBORDER BY @PrimaryKey ASC

　　之所以把取数据表前半部分记录和取后半部分记录的SQL语句分开写，是因为使用取前半部分记录的SQL语句时，当前页前面的记录数目随页数递增，而我们还要从这些记录中取出它们的主键字段的值再从中选出最大值。这样一来，分页速度将随着页数的增加而减慢。因此我没有这样做，而是在当前页索引大于中间页索引时（@MiddleIndex @PageIndex）选用了分页速度随着页数的增加而加快的算法。由此可见，假设把所有分页面划分为前面、中间和后面三部分，则最前面和最后面的分页速度最快，最中间的分页速度最慢。

　　例如：@PageIndex=3，红 -- 黄 -- 蓝

　　（4）@PageIndex = @LastIndex

　　取最后一页的记录可以简单地使用类似状态（1）的做法：

SELECT * FROM (SELECT TOP @PageSize @QueryFieldsFROM @TableNameWHERE @ConditionORDER BY @PrimaryKey DESC) TableA ORDER BY @PrimaryKey ASC

　　不过，这样产生的最后一页不一定是实际意义上的最后一页。因为最后一页的记录数未必刚好跟@PageSize相等，而上面的SQL语句是直接取得倒数的@PageSize条记录。如果想要精确地取得最后一页的记录，应该在先计算出该页的记录数，作为TOP语句的条件：

SELECT * FROM (SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFieldsFROM @TableName WHERE @ConditionORDER BY @PrimaryKey DESC) TableA ORDER BY @PrimaryKey ASC

　　降序的SQL语句

　　降序的SQL语句跟升序的大同小异，这里就不在罗嗦了J

　　（1）@PageIndex = @FirstIndex

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @Condition ORDER BY @PrimaryKey DESC

　　（2）@FirstIndex @PageIndex = @MiddleIndex

SELECT TOP @PageSize @QueryFieldsFROM @TableNameWHERE @PrimaryKey  (SELECT MIN(@PrimaryKey) FROM (SELECT TOP @PageSize*@PageIndex @PrimaryKeyFROM @TableNameWHERE @ConditionORDER BY @PrimaryKey DESC) TableA) WHERE @ConditionORDER BY @PrimaryKey DESC

　　（3）@MiddleIndex @PageIndex @LastIndex

SELECT * FROM (SELECT TOP @PageSize @QueryFieldsFROM @TableNameWHERE @PrimaryKey  (SELECT MAX(@PrimaryKey) FROM (SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKeyFROM @TableNameWHERE @ConditionORDER BY @PrimaryKey ASC) TableA) WHERE @ConditionORDER BY @PrimaryKey ASC) TableB ORDER BY @PrimaryKey DESC