对于数据库的分页,目前比较传统的方法是采用分页存储过程,其实用 DataReader 也可以实现分页,不需要写存储过程,实现效率上也比几种比较流行的分页方法要略快。
在开始这个方法之前,让我们先创建一个简单的测试环境:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
use Test
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if exists (select * from sysobjects where id = object_id('R_Student') and type = 'u')
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
drop table R_Student
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table R_Student
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Id nvarchar(64) Primary Key,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Class nvarchar(64) NOT NULL,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Age tinyint NOT NULL,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Sex tinyint NOT NULL
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Declare
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@i int
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @i = 0;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
while (@i < 1000000)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert R_Student values('Name' + Str(@i),'Class' + Str(@i), @i % 100, @i % 2)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @i = @i + 1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
通过上述语句创建一个简单的数据表,并插入100万条记录
DataReader 分页的方法:
说出来很简单,见下面程序 源码下载位置
其实就是通过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
这个方法不受排序字段,以及重复键等的约束,非常通用。效率也不错。说白了,就是先将查询结果存到临时表中,
并为这个临时表提供一个自增长的索引字段,然后根据这个字段进行查询范围。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if exists (select * from sysobjects where id = object_id('PagedProcUseROW_NUMBER') and type = 'p')
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
drop procedure PagedProcUseROW_NUMBER
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create procedure PagedProcUseROW_NUMBER
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@currentpage int, -- page no
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@pagesize int --page size
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WITH student AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT *,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM r_student
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT *
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM student
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE RowNumber BETWEEN (@currentpage-1)*@pagesize + 1 AND (@currentpage)*@pagesize;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
3. 通用分页存储过程
这个存储过程的出处:
http://www.cnblogs.com/Tracy-Chuang/archive/2006/10/16/530125.html
我稍微改了一点,去掉了一些功能,方便测试。
这个存储过程有一些缺点,比如不支持多字段主键,重复键的处理看似也有问题,不排序也不可以。单纯从效率看,
还是可以的。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if exists (select * from sysobjects where id = object_id('[spCommonPageData]') and type = 'p')
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
drop procedure [spCommonPageData]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--http://www.cnblogs.com/Tracy-Chuang/archive/2006/10/16/530125.html
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- =============================================
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Author: <张婷婷>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Create date: <2006-08-24>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Description: <通用分页存储过程>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- =============================================
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Create PROCEDURE [dbo].[spCommonPageData]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@Select NVARCHAR(500), -- 要查询的列名,用逗号隔开(Select后面From前面的内容)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@From NVARCHAR(200), -- From后的内容
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@Where NVARCHAR(500) = NULL, -- Where后的内容
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@OrderBy NVARCHAR(100) = NULL, -- 排序字段
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@Key NVARCHAR(50), -- 分页主键
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@Page INT, -- 当前页 ***计数从1开始***
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@PageSize INT -- 每页大小
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Alter By Tracy.Chuang 2006-08-21更改分页算法,采用比较最大值的方法
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Set @Sql=
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'Select Top '+Cast(@PageSize As
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
nVarchar(10))+' '+@Select+ ' From '+@From+ ' Where '+Case
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IsNull(@Where,'') When '' Then '' Else @Where+' And ' End+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@Key+' >( Select ISNULL(MAX('+@Key+'), 0) AS MaxID
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
From (Select Top '+Cast(@PageSize*(@Page-1) As Varchar(10))+'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'+@Key+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
' From '+@From+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
' Order By '+@Key+') As T)'+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
' Order By '+@Key+Case IsNull(@OrderBy,'') When '' Then '' Else
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
','+@OrderBy End
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Exec(@Sql)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
|