eaglet

本博专注于基于微软技术的搜索相关技术
posts - 189, comments - 3716, trackbacks - 26, articles - 0
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

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

Posted on 2008-10-09 10:02  eaglet  阅读(5876)  评论(23编辑  收藏

用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-1As 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

 
 源码下载位置