用DataReader 分页与几种传统的分页方法的比较
作者:肖波[原文:http://www.cnblogs.com/eaglet/archive/2008/10/09/1306806.html]
对于数据库的分页,目前比较传统的方法是采用分页存储过程,其实用 DataReader 也可以实现分页,不需要写存储过程,实现效率上也比几种比较流行的分页方法要略快。
在开始将这个方法之前,让我们先创建一个简单的测试环境:
use
Test
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OpenDataReader(queryString);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (first < 0)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
first = 0;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
for (long i = 0; i < first; i++)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!_DataReader.Read())
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return _SchemaDataSet;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (last < 0)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
last = 0x7FFFFFFFFFFFFFFF;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
for (long i = first; i <= last; i++)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataRow row = NextRow();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (row != null)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_SchemaTable.Rows.Add(row);
}
else
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return _SchemaDataSet;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return _SchemaDataSet;
}
finally
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Declare
@Sql
nVarchar
(
1000
),
@Sql2
NVARCHAR
(
500
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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 |
源码下载位置