對於數據庫的分頁，目前比較傳統的方法是采用分頁存儲過程，其實用 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本文配套源碼
