用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
  
  
   源码下载位置
  http://files.cnblogs.com/eaglet/PagerTest.zip