很久以前看过,不过因为一直无接触过大数据量的数据库,所以一直无细心了解,现在再看一下作为补充,下面的方法我想会比较适合50万以内的记录数.

其实只优化了sql部分。因为你的代码也可以自定义sql，所以可以写一个sql生成来类来优化一下sql查询语句，当然可以内置到你的类中，请看下面的内容（来源于网络）：

前传

     变量                  用途

@PageSize             每页显示的记录总数

@PageCount       分页总数

@RecordCount       数据表的记录总数

@PageIndex      当前页的索引

@FirstIndex             第一页的索引

@MiddleIndex       中间页的索引

@LastIndex             最后一页的索引

@TableName       数据库表名称

@PrimaryKey       主键字段名称

@QueryFields       要查询的字段集

@Condition             筛选条件

 

定义：

@PageCount  = (int)Math.Ceiling((double)@RecordCount / @PageSize)

@FirstIndex = 0

@LastIndex  = @PageCount - 1

@MiddleIndex     = (int)Math.Ceiling((double)@PageCount / 2) – 1

 

预设：

@PageSize = 2

@RecordCount = 9

@PageCount = 4

 

     现在先让我们来看看速度最慢的 SQL 语句：

 

SELECT TOP @PageSize * FROM @TableName AS a

WHERE @PrimaryKey NOT IN (

     SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName AS b

     ORDER BY @PrimaryKey

)

ORDER BY @PrimaryKey

 

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

 

     还有一种是用两个 TOP 的 SQL 语句，如下所示：

 

SELECT * FROM (

     SELECT TOP @PageSize * FROM (

          SELECT TOP @PageSize*(@PageIndex+1) * FROM @TableName

          ORDER BY @PrimaryKey

     ) TableA

     ORDER BY @PrimaryKey DESC

) TableB

ORDER BY @PrimaryKey

 

     这条 SQL 语句空间复杂度比较大。如果要显示的分页面刚好是最后一页，那么它的效率比直接SELECT 出所有的记录还要低。

一、相关概念

     在 ACCESS 数据库中，一个表的主键（PRIMARY KEY，又称主索引）上必然建立了唯一索引（UNIQUE INDEX），因此主键字段的值是不会重复的。并且索引页依据索引列的值进行排序，每个索引记录包含一个指向它所引用的数据行的指针。我们可以利用主键这两个特点来实现对某条记录的定位，从而快速地取出某个分页上要显示的记录。

 

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

 

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

二、升序

（1）@PageIndex <= @FirstIndex

 

     取第一页的数据是再简单不过了，我们只要用TOP @PageSize 就可以取出第一页要显示的记录。因为数据表中的记录已经按主键字段的值升序排好，所以省去了 ORDER BY 子句，速度更快。

 

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @Condition

-- ORDER BY @PrimaryKey ASC

如图：@PageIndex = 0

 

（2）@FirstIndex < @PageIndex <= @MiddleIndex

 

     把取数据表前半部分记录和取后半部分记录的 SQL 语句分开写，可以有效地改善性能。后面我再详细解释这个问题。现在看看取前半部分记录的 SQL 语句。先取出当前页之前的所有记录的主键值，再从中选出最大值，然后取出主键值大于该最大值的前 @PageSize 条记录。值得注意的是，这里省去了两个 ORDER BY @PrimaryKey ASC 语句，分别在最里面和最外面的 SELECT 语句。前面已经说过，数据库表中的记录已经按主键字段的值升序排好，所以我们没有必要画蛇添足。

 

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @PrimaryKey > (

     SELECT MAX(@PrimaryKey) FROM (

          SELECT TOP @PageSize*@PageIndex @PrimaryKey

          FROM @TableName

          WHERE @Condition

          -- ORDER BY @PrimaryKey ASC

      ) TableA

) WHERE @Condition

-- ORDER BY @PrimaryKey ASC

 

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

 

（3）@MiddleIndex < @PageIndex < @LastIndex

 

     接下来看看取数据表后半部分记录的 SQL 语句。该语句跟前面的语句算法的原理是一样的，只是方法稍微不同。

 

     先取出当前页之后的所有记录的主键值，再从中选出最小值，然后取出主键值小于该最小值的前 @PageSize 条记录。

 

SELECT * FROM (

     SELECT TOP @PageSize @QueryFields

     FROM @TableName

     WHERE @PrimaryKey < (

          SELECT MIN(@PrimaryKey) FROM (

               SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey

               FROM @TableName

               WHERE @Condition

               ORDER BY @PrimaryKey DESC

          ) TableA

     ) WHERE @Condition

     ORDER BY @PrimaryKey DESC

) TableB

ORDER BY @PrimaryKey ASC

 

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

 

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

 

 

（4）@PageIndex >= @LastIndex

 

     取最后一页的记录时要先计算出该页的记录数，作为 TOP 语句的条件，而不能直接用 TOP @PageSize，这样取出来的记录并不只是最后一页的。其实很多网站确实这样做。

 

SELECT * FROM (

     SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields

     FROM @TableName

     WHERE @Condition

     ORDER BY @PrimaryKey DESC

) TableA

ORDER BY @PrimaryKey ASC

 

例如：@PageIndex = 4

三、降序

（1）@PageIndex <= @FirstIndex

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey DESC

 

（2）@FirstIndex < @PageIndex <= @MiddleIndex

 

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @PrimaryKey < (

    SELECT MIN(@PrimaryKey) FROM (

        SELECT TOP @PageSize*@PageIndex @PrimaryKey

        FROM @TableName

        WHERE @Condition

        ORDER BY @PrimaryKey DESC

    ) TableA

) WHERE @Condition

ORDER BY @PrimaryKey DESC

 

 

（3）@MiddleIndex < @PageIndex < @LastIndex

 

SELECT * FROM (

    SELECT TOP @PageSize @QueryFields

    FROM @TableName

    WHERE @PrimaryKey > (

        SELECT MAX(@PrimaryKey) FROM (

            SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey

            FROM @TableName

            WHERE @Condition

            -- ORDER BY @PrimaryKey ASC

        ) TableA

    ) WHERE @Condition

    -- ORDER BY @PrimaryKey ASC

) TableB

ORDER BY @PrimaryKey DESC

 

 

（4）@PageIndex >= @LastIndex

 

SELECT * FROM (

    SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields

    FROM @TableName

    WHERE @Condition

    ORDER BY @PrimaryKey ASC

) TableA

ORDER BY @PrimaryKey DESC 

 

符一个这种写法的sp

CREATE PROCEDURE PageView

@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 "

else

set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

-- 以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

if @OrderType != 0

begin

set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

-- 如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

set @strTmp = ">(select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

if @PageIndex = 1

begin

if @strWhere != ''

set @strSQL = "select top " + str(@PageSize) +" "+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder

else

set @strSQL = "select top " + str(@PageSize) +" "+ " from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin