用DataReader 分页与几种传统的分页方法的比较

用DataReader 分页与几种传统的分页方法的比较

 作者：肖波

        对于数据库的分页，目前比较传统的方法是采用分页存储过程，其实用 DataReader 也可以实现分页，不需要写存储过程，实现效率上也比几种比较流行的分页方法要略快。

        在开始这个方法之前，让我们先创建一个简单的测试环境：

       

use  Test
GO

if   exists  ( select   *   from  sysobjects  where  id  =   object_id ( ' R_Student ' )  and  type  =   ' u ' )
     drop   table  R_Student
GO
create   table  R_Student
(
    Id           nvarchar ( 64 )   Primary   Key ,
    Class        nvarchar ( 64 )   NOT   NULL ,
    Age          tinyint         NOT   NULL ,
    Sex          tinyint         NOT   NULL     
)

GO
Declare
@i   int
set   @i   =   0 ;
while  ( @i   <   1000000 )
begin
insert  R_Student  values ( ' Name '   +   Str ( @i ), ' Class '   +   Str ( @i ),  @i   %   100 ,  @i   %   2 )
set   @i   =   @i   +   1
end

 通过上述语句创建一个简单的数据表，并插入100万条记录

 DataReader 分页的方法：

 说出来很简单，见下面程序   源码下载位置

 

         public  DataSet RangeQuery( string  queryString,  long  first,  long  last)
         {
            try
            {
                OpenDataReader(queryString);

                if (first < 0)
                {
                    first = 0;
                }

                for (long i = 0; i < first; i++)
                {
                    if (!_DataReader.Read())
                    {
                        return _SchemaDataSet;
                    }
                }

                if (last < 0)
                {
                    last = 0x7FFFFFFFFFFFFFFF;
                }

                for (long i = first; i <= last; i++)
                {
                    DataRow row = NextRow();

                    if (row != null)
                    {
                        _SchemaTable.Rows.Add(row);
                    }
                    else
                    {
                        return _SchemaDataSet;
                    }
                }

                return _SchemaDataSet;
            }
            finally
            {
                CloseDataReader();
            }
        }

 

 其实就是通过DataReader 将当前记录移动到起始页对应的那条纪录，然后再开始读数据。由于之前只是移动记录指针，并不读取

数据，所以效率很高。

几种常用方法介绍

 1. 二次 TOP 

 这种方法效率较低,问题主要处在那个 not in 上面，另外如果Id 是可重复的，得出的结果是

GO
if   exists  ( select   *   from  sysobjects  where  id  =   object_id ( ' PagedProc ' )  and  type  =   ' p ' )
     drop   procedure  PagedProc
GO
create   procedure  PagedProc
@currentpage   int ,  --  page no
@pagesize   int   -- page size
as
declare
@sqlstr   nvarchar ( 4000 )  -- Query string

if   @currentpage   =   1  
begin
set   @sqlstr   =   ' SELECT TOP  '   +   Str ( @pagesize )  +   ' * from r_student order by Id '
end
else
begin

set   @sqlstr   =   ' SELECT TOP  '   +   Str ( @pagesize )  +   '  * from r_student where id not in ' ;
set   @sqlstr   =   @sqlstr   +   ' (SELECT TOP  ' +   Str (( @currentpage - 1 ) * @pagesize )  +   '  id from  r_student order by Id) '

end

exec  ( @sqlstr )

GO

 

 2. ROWNUMBER

 这个方法不受排序字段，以及重复键等的约束，非常通用。效率也不错。说白了，就是先将查询结果存到临时表中，

并为这个临时表提供一个自增长的索引字段，然后根据这个字段进行查询范围。

 

if   exists  ( select   *   from  sysobjects  where  id  =   object_id ( ' PagedProcUseROW_NUMBER ' )  and  type  =   ' p ' )
     drop   procedure  PagedProcUseROW_NUMBER
GO
create   procedure  PagedProcUseROW_NUMBER
@currentpage   int ,  --  page no
@pagesize   int   -- page size
as
begin
WITH  student  AS
(
     SELECT   * ,
    ROW_NUMBER()  OVER  ( ORDER   BY  Id)  AS   ' RowNumber '
     FROM  r_student 
) 
SELECT   *  
FROM  student 
WHERE  RowNumber  BETWEEN  ( @currentpage - 1 ) * @pagesize   +   1   AND  ( @currentpage ) * @pagesize ;
end
GO

 

 3. 通用分页存储过程

这个存储过程的出处：

http://www.cnblogs.com/Tracy-Chuang/archive/2006/10/16/530125.html

我稍微改了一点，去掉了一些功能，方便测试。

这个存储过程有一些缺点，比如不支持多字段主键，重复键的处理看似也有问题，不排序也不可以。单纯从效率看，

还是可以的。

if   exists  ( select   *   from  sysobjects  where  id  =   object_id ( ' [spCommonPageData] ' )  and  type  =   ' p ' )
     drop   procedure   [ spCommonPageData ]
GO
-- http://www.cnblogs.com/Tracy-Chuang/archive/2006/10/16/530125.html

--  =============================================
--  Author:  <张婷婷>
--  Create date: <2006-08-24>
--  Description: <通用分页存储过程>
--  =============================================
Create   PROCEDURE   [ dbo ] . [ spCommonPageData ]
  @Select   NVARCHAR ( 500 ),    --  要查询的列名，用逗号隔开（Select后面From前面的内容）
  @From   NVARCHAR ( 200 ),    --  From后的内容
  @Where   NVARCHAR ( 500 )  =   NULL ,  --  Where后的内容
  @OrderBy   NVARCHAR ( 100 )  =   NULL ,  --  排序字段
  @Key   NVARCHAR ( 50 ),     --  分页主键
  @Page   INT ,       --  当前页 ***计数从1开始***
  @PageSize   INT       --  每页大小
AS
BEGIN
  SET  NOCOUNT  ON ;

  Declare   @Sql   nVarchar ( 1000 ),  @Sql2   NVARCHAR ( 500 )

-- Alter By Tracy.Chuang 2006-08-21更改分页算法，采用比较最大值的方法
  Set   @Sql =
  ' Select Top  ' + Cast ( @PageSize   As
  nVarchar ( 10 )) + '   ' + @Select +    '  From  ' + @From +    '  Where  ' + Case
  IsNull ( @Where , '' )  When   ''   Then   ''   Else   @Where + '  And  '   End +
  @Key + '  >( Select ISNULL(MAX( ' + @Key + ' ), 0) AS MaxID
    From (Select Top  ' + Cast ( @PageSize * ( @Page - 1 )  As   Varchar ( 10 )) + '  
  ' + @Key +
        '  From  ' + @From +
        Case   IsNull ( @Where , '' )  When   ''   Then   ''   Else   '  Where  ' + @Where   End +
        '  Order By  ' + @Key + ' ) As T) ' +
  '  Order By  ' + @Key + Case   IsNull ( @OrderBy , '' )  When   ''   Then   ''   Else
  ' , ' + @OrderBy   End

  Exec ( @Sql )
 
END

 

 四种方法的效率比较。只做了一种条件下测试，其他条件大家有兴趣可以自己测。

 PageSize = 10, 记录总数 100万,时间单位为毫秒

 

分页方法	第1页	第10页	第100页	第1000页	第10000页	第100000页
二次 Top	4	7	404	28	271	3926
ROW_NUMBER	1	1	2	12	108	3594
通用分页	1	1	1	10	82	3487
DataReader	0	0	1	9	91	3380

 
 源码下载位置