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

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

作者：肖波

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

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

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> 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 分页的方法：

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

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> public DataSetRangeQuery( string queryString, long first, long last)
{
try
{
OpenDataReader(queryString);

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

for(longi=0;i<first;i++)
{
if(!_DataReader.Read())
{
return_SchemaDataSet;
}
}

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

for(longi=first;i<=last;i++)
{
DataRowrow=NextRow();

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

return_SchemaDataSet;
}
finally
{
CloseDataReader();
}
}

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

数据，所以效率很高。

几种常用方法介绍

1. 二次 TOP

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

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> GO
if exists ( select * from sysobjects where id = object_id ( ' PagedProc ' ) and type = ' p ' )
drop procedure PagedProc
GO
create procedure PagedProc
@currentpage int , -- pageno
@pagesize int -- pagesize
as
declare
@sqlstr nvarchar ( 4000 ) -- Querystring

if @currentpage = 1
begin
set @sqlstr = ' SELECTTOP ' + Str ( @pagesize ) + ' *fromr_studentorderbyId '
end
else
begin

set @sqlstr = ' SELECTTOP ' + Str ( @pagesize ) + ' *fromr_studentwhereidnotin ' ;
set @sqlstr = @sqlstr + ' (SELECTTOP ' + Str (( @currentpage - 1 ) * @pagesize ) + ' idfromr_studentorderbyId) '

end

exec ( @sqlstr )

GO

2. ROWNUMBER

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

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

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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 , -- pageno
@pagesize int -- pagesize
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

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

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

还是可以的。

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> 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:<张婷婷>
-- Createdate:<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 )

-- AlterByTracy.Chuang2006-08-21更改分页算法，采用比较最大值的方法
Set @Sql =
' SelectTop ' + Cast ( @PageSize As
nVarchar ( 10 )) + ' ' + @Select + ' From ' + @From + ' Where ' + Case
IsNull ( @Where , '' ) When '' Then '' Else @Where + ' And ' End +
@Key + ' >(SelectISNULL(MAX( ' + @Key + ' ),0)ASMaxID
From(SelectTop ' + Cast ( @PageSize * ( @Page - 1 ) As Varchar ( 10 )) + '
' + @Key +
' From ' + @From +
Case IsNull ( @Where , '' ) When '' Then '' Else ' Where ' + @Where End +
' OrderBy ' + @Key + ' )AsT) ' +
' OrderBy ' + @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

源码下载位置