依稀记得初初向一位师兄推荐.NET平台的时候,用的例子就是一个简单数据表格的展现,师兄看完例子后劈头盖脸就问这数据分页怎么做?性能如何?对大量的数据支持如何?...那时的我其实也十分懵懂,水平充其量就是照着ScottGu博客中的文章生硬模仿而已,对与这些问题没有什么感性的认知.进入职场后,面对记录数上百万级,甚至上千万级的数据库,数据处理效能问题慢慢浮现在工作的每个角落,编写的代码和设计的结构使效率慢上那几秒钟,也深怕客户来句"体现不良好"之云云,现在想想也惹人发笑:)
今日拿了些旧项目出来扫扫尘,左动动又动动,可全是无伤大雅之举,唯独那些数据分页不明确的迂腐代码特别碍眼,说砍就砍,参照百家之言,憋了一股劲就写好一个比较通用的数据分页的存储过程,生成百万行的数据测试了一下,性能还行,基本都是毫秒级的运算.
我向来的做分页的习惯都是用索引过的主键进行分页,但新问题突然就来了,如果主键是GUID,是个uniqueidentifier怎么办呢?想了很多方法,最后只能锁定在SQL 2005 的新函ROW_NUMBER()上,用上它不就没有那些限制了么?说改就改,代码完工以测试,效能上还没有一些大大吹得那么鸡肋,基本还是毫秒级就完成我给它的任务.
正当我自鸣得意的时候,突然发觉原来自己还是个傻冒,试想谁会去用一个没有规律的值来进行分页,即是做到又有什么意义呢?看这次真是走火入魔,庸人自扰咯 T_T.不过也好,碰壁的过程就是求知的过程,至少今天也对SQL 2005的系统数据库和一些函数特性有进一步了解,不错不错:)
1.没有使用ROW_NUMBER()的存储过程
/**/
/***********************************************************************
* 文件名: GetRecordsPagination.sql* 功能: 创建存储过程GetRecordsPagination [注:只能用于能用MAX,MIN等方法的字段进行分页!]* 创建时间: 2007-10-17* 创建人: 清风* 最后修改时间: 2007-10-17* 最后修改人: 清风
***********************************************************************/
IF
EXISTS
(
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
SPECIFIC_NAME
=
'
GetRecordsPagination
'
)
DROP
PROCEDURE
GetRecordsPagination
GO
CREATE
PROCEDURE
GetRecordsPagination(
@tableName
varchar
(
256
),
--
进行分页表名
@columnName
varchar
(
256
),
--
用作分页的字段名
@pageSize
int
=
10
,
--
每页记录数 (默认是10)
@pageIndex
int
=
1
,
--
页码 (默认第一页)
@sortType
bit
=
0
,
--
数据查找排序类型,
--
0 升序,1 降序 (默认 0)
@visibleColumns
varchar
(
2000
)
=
''
,
--
要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
@sqlCondition
varchar
(
2000
)
=
''
,
--
附加查询条件 (不包含'where'字串)
@orderByColumn
varchar
(
256
)
=
''
,
--
记录最后排序OrderBy的字段 (默认等于@columnName)
@orderBySortType
bit
=
0
--
记录最后排序类型,
--
0 升序,1 降序 (默认 0)
)
AS
declare
@strSQL
varchar
(
8000
)
--
最终合成的SQL语句
declare
@strTemp
varchar
(
256
)
--
临时字符串
declare
@strOrderBy
varchar
(
1000
)
--
查找排序类型子句
--
设置数据查找排序类型子句
if
@sortType
!=
0
begin
set
@strTemp
=
'
<(select min
'
set
@strOrderBy
=
'
order by [
'
+
@columnName
+
'
] desc
'
end
else
begin
set
@strTemp
=
'
>(select max
'
set
@strOrderBy
=
'
order by [
'
+
@columnName
+
'
] asc
'
end
--
设置要返回字段
if
@visibleColumns
=
''
set
@visibleColumns
=
'
*
'
--
设置查找语句
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from [
'
+
@tableName
+
'
] where [
'
+
@columnName
+
'
]
'
+
@strTemp
+
'
([
'
+
@columnName
+
'
]) from (select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
[
'
+
@columnName
+
'
] from [
'
+
@tableName
+
'
]
'
+
@strOrderBy
+
'
) as TempTable)
'
+
@strOrderBy
--
添加附加查询条件
if
@sqlCondition
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from [
'
+
@tableName
+
'
] where [
'
+
@columnName
+
'
]
'
+
@strTemp
+
'
([
'
+
@columnName
+
'
]) from (select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
[
'
+
@columnName
+
'
] from [
'
+
@tableName
+
'
] where
'
+
@sqlCondition
+
'
'
+
@strOrderBy
+
'
) as TempTable) and
'
+
@sqlCondition
+
'
'
+
@strOrderBy
--
优化第一页查询
if
@pageIndex
=
1
begin
set
@strTemp
=
''
if
@sqlCondition
!=
''
set
@strTemp
=
'
where (
'
+
@sqlCondition
+
'
)
'
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from [
'
+
@tableName
+
'
]
'
+
@strTemp
+
'
'
+
@strOrderBy
end
--
设置自定义输出OrderBy
if
@orderByColumn
!=
''
begin
set
@strTemp
=
@strSQL
if
@orderBySortType
=
0
set
@strSQL
=
'
select * from (
'
+
@strTemp
+
'
) as TempTable2 order by [
'
+
@orderByColumn
+
'
] asc
'
else
set
@strSQL
=
'
select * from (
'
+
@strTemp
+
'
) as TempTable2 order by [
'
+
@orderByColumn
+
'
] desc
'
end
--
测试生成的SQL语句
--
PRINT @strSQL
exec
(
@strSQL
)
2.使用ROW_NUMBER()的存储过程
/**/
/************************************************************************ 文件名: GetRecordsPaginationWithRowNumber.sql* 功能: 创建存储过程GetRecordsPaginationWithRowNumber [注:运用SQL 2005新增函数ROW_NUMBER(),有局限性!]* 创建时间: 2007-10-17* 创建人: 清风* 最后修改时间: 2007-10-17* 最后修改人: 清风***********************************************************************/
IF
EXISTS
(
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
SPECIFIC_NAME
=
'
GetRecordsPaginationWithRowNumber
'
)
DROP
PROCEDURE
GetRecordsPaginationWithRowNumber
GO
CREATE
PROCEDURE
GetRecordsPaginationWithRowNumber(
@tableName
varchar
(
256
),
--
进行分页表名
@columnName
varchar
(
256
),
--
用作分页的字段名
@pageSize
int
=
10
,
--
每页记录数 (默认是10)
@pageIndex
int
=
1
,
--
页码 (默认第一页)
@sortType
bit
=
0
,
--
数据查找排序类型,
--
0 升序,1 降序 (默认 0)
@visibleColumns
varchar
(
2000
)
=
''
,
--
要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
@sqlCondition
varchar
(
2000
)
=
''
,
--
附加查询条件 (不包含'where'字串)
@orderByColumn
varchar
(
256
)
=
''
,
--
记录最后排序OrderBy的字段 (默认等于@columnName)
@orderBySortType
bit
=
0
--
记录最后排序类型,
--
0 升序,1 降序 (默认 0)
)
AS
declare
@strSQL
varchar
(
8000
)
--
最终合成的SQL语句
declare
@strTemp
varchar
(
256
)
--
临时字符串
declare
@strOrderBy
varchar
(
1000
)
--
查找排序类型子句
--
设置数据查找排序类型子句
if
@sortType
!=
0
begin
set
@strOrderBy
=
'
order by [
'
+
@columnName
+
'
] desc
'
end
else
begin
set
@strOrderBy
=
'
order by [
'
+
@columnName
+
'
] asc
'
end
--
设置要返回字段
if
@visibleColumns
=
''
set
@visibleColumns
=
'
*
'
--
设置查找语句
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from ( select * , ROW_NUMBER() Over (
'
+
@strOrderBy
+
'
) as RowNum from [
'
+
@tableName
+
'
] ) as TempTable
'
+
'
where RowNum between
'
+
str
( (
@pageIndex
-
1
)
*
@pageSize
)
+
'
and
'
+
str
(
@pageIndex
*
@pageSize
)
--
添加附加查询条件
if
@sqlCondition
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from ( select * , ROW_NUMBER() Over (
'
+
@strOrderBy
+
'
) as RowNum from [
'
+
@tableName
+
'
] where
'
+
@sqlCondition
+
'
) as TempTable
'
+
'
where RowNum between
'
+
str
( (
@pageIndex
-
1
)
*
@pageSize
)
+
'
and
'
+
str
(
@pageIndex
*
@pageSize
)
--
优化第一页查询
if
@pageIndex
=
1
begin
set
@strTemp
=
''
if
@sqlCondition
!=
''
set
@strTemp
=
'
where (
'
+
@sqlCondition
+
'
)
'
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@visibleColumns
+
'
from [
'
+
@tableName
+
'
]
'
+
@strTemp
+
'
'
+
@strOrderBy
end
--
设置自定义输出OrderBy
if
@orderByColumn
!=
''
begin
set
@strTemp
=
@strSQL
if
@orderBySortType
=
0
set
@strSQL
=
'
select * from (
'
+
@strTemp
+
'
) as TempTable2 order by [
'
+
@orderByColumn
+
'
] asc
'
else
set
@strSQL
=
'
select * from (
'
+
@strTemp
+
'
) as TempTable2 order by [
'
+
@orderByColumn
+
'
] desc
'
end
--
测试生成的SQL语句
PRINT
@strSQL
exec
(
@strSQL
)
3.测试代码
/**/
/************************************************************************ 文件名: PageTest.sql* 功能: 测试分页存储过程* 创建时间: 2007-10-17* 创建人: 清风* 最后修改时间: 2007-10-17* 最后修改人: 清风***********************************************************************/
--
创建测试数据
--
CREATE TABLE PagerTest
--
(
--
pGUID uniqueidentifier primary key,
--
pNum int NULL
--
);
----
DECLARE @i int
--
set @i = 1
--
这里只使用了一百万行数据进行测试
--
WHILE @i < 1000000
--
BEGIN
--
INSERT INTO PagerTest
--
VALUES(NEWID(),@i)
--
set @i = @i + 1
--
END
----
CREATE INDEX IdxGUID ON PagerTest(pGUID);
--
CREATE INDEX IdxNum ON PagerTest(pNum);
--
测试数据
--
SELECT *
--
FROM PagerTest
--
ORDER BY pNUM ASC
----
清空数据
--
DROP TABLE PagerTest
--
测试GetRecordsPagination
exec
GetRecordsPagination PagerTest,pNum,
10
,
10000
,
0
,
''
,
''
,pNum,
1
;
exec
GetRecordsPagination PagerTest,pNum,
10
,
10
--
测试GetRecordsPaginationWithRowNumber
exec
GetRecordsPaginationWithRowNumber PagerTest,pNum,
10
,
10
exec
GetRecordsPaginationWithRowNumber PagerTest,pNum,
10
,
10
,
1
,
'
pNum
'
,
'
pNum > 999903
'
,pNum,
0
;
exec
GetRecordsPaginationWithRowNumber PagerTest,pGUID,
10
,
10
,
1
,
''
,
''
,pNum,
0
;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
