天目网

设置首页 - 加入收藏
首页   女人情感   投资理财   大众美食   宠物乐园   娱乐八卦   电视剧情介绍   电脑技术
 

基础温习(二)再写数据分页的存储过程-清风阁

2008-5-7 12:52:20 来源：本站
 
新一篇:谈2.0网站初期规划应该考虑的问题

依稀记得初初向一位师兄推荐.NET平台的时候,用的例子就是一个简单数据表格的展现,师兄看完例子后劈头盖脸就问这数据分页怎么做?性能如何?对大量的数据支持如何?...那时的我其实也十分懵懂,水平充其量就是照着ScottGu博客中的文章生硬模拟而已,对与这些问题没有什么感性的认知.进入职场后,面对记录数上百万级,甚至上千万级的数据库,数据处理效能问题慢慢浮现在工作的每个角落,编写的代码和设计的结构使效率慢上那几秒钟,也深怕客户来句"体现不良好"之云云,现在想想也惹人发笑:)

今日拿了些旧项目出来扫扫尘,左动动又动动,可全是无伤大雅之举,唯独那些数据分页不明确的迂腐代码非凡碍眼,说砍就砍,参照百家之言,憋了一股劲就写好一个比较通用的数据分页的存储过程,生成百万行的数据测试了一下,性能还行,基本都是毫秒级的运算.

我向来的做分页的习惯都是用索引过的主键进行分页,但新问题忽然就来了,假如主键是GUID,是个uniqueidentifier怎么办呢?想了很多方法,最后只能锁定在SQL2005的新函ROW_NUMBER()上,用上它不就没有那些限制了么?说改就改,代码完工以测试,效能上还没有一些大大吹得那么鸡肋,基本还是毫秒级就完成我给它的任务.

正当我自鸣自得的时候,忽然发觉原来自己还是个傻冒,试想谁会去用一个没有规律的值来进行分页,即是做到又有什么意义呢?看这次真是走火入魔,庸人自扰咯T_T.不过也好,碰壁的过程就是求知的过程,至少今天也对SQL2005的系统数据库和一些函数特性有进一步了解,不错不错:)

1.没有使用ROW_NUMBER()的存储过程

/**//***********************************************************************
*文件名:GetRecordsPagination.sql
*功能:创建存储过程GetRecordsPagination
[注:只能用于能用MAX,MIN等方法的字段进行分页!]

*创建时间:2007-10-17
*创建人:清风
*最后修改时间:2007-10-17
*最后修改人:清风
***********************************************************************/

IFEXISTS(
SELECT*
FROMINFORMATION_SCHEMA.ROUTINES
WHERESPECIFIC_NAME='GetRecordsPagination')
DROPPROCEDUREGetRecordsPagination

GO

CREATEPROCEDUREGetRecordsPagination
(
@tableNamevarchar(256),--进行分页表名
@columnNamevarchar(256),--用作分页的字段名
@pageSizeint=10,--每页记录数(默认是10)
@pageIndexint=1,--页码(默认第一页)
@sortTypebit=0,--数据查找排序类型,
--0升序,1降序(默认0)
@visibleColumnsvarchar(2000)='',--要返回的字段,格式"A1,B1,C1"(若为空,则返回全部)
@sqlConditionvarchar(2000)='',--附加查询条件(不包含'where'字串)
@orderByColumnvarchar(256)='',--记录最后排序OrderBy的字段(默认等于@columnName)
@orderBySortTypebit=0--记录最后排序类型,
--0升序,1降序(默认0)
)
AS

declare@strSQLvarchar(8000)--最终合成的SQL语句
declare@strTempvarchar(256)--临时字符串
declare@strOrderByvarchar(1000)--查找排序类型子句

--设置数据查找排序类型子句
if@sortType!=0
begin
set@strTemp='<(selectmin'
set@strOrderBy='orderby[' @columnName ']desc'
end
else
begin
set@strTemp='>(selectmax'
set@strOrderBy='orderby[' @columnName ']asc'
end

--设置要返回字段
if@visibleColumns=''
set@visibleColumns='*'

--设置查找语句
set@strSQL='selecttop' str(@pageSize) '' @visibleColumns 'from['
@tableName ']where[' @columnName ']' @strTemp '(['
@columnName '])from(selecttop' str((@pageIndex-1)*@pageSize) '['
@columnName ']from[' @tableName ']' @strOrderBy ')asTempTable)'
@strOrderBy

--添加附加查询条件
if@sqlCondition!=''
set@strSQL='selecttop' str(@pageSize) '' @visibleColumns 'from['
@tableName ']where[' @columnName ']' @strTemp '(['
@columnName '])from(selecttop' str((@pageIndex-1)*@pageSize) '['
@columnName ']from[' @tableName ']where' @sqlCondition ''
@strOrderBy ')asTempTable)and' @sqlCondition '' @strOrderBy

--优化第一页查询
if@pageIndex=1
begin
set@strTemp=''
if@sqlCondition!=''
set@strTemp='where(' @sqlCondition ')'

set@strSQL='selecttop' str(@pageSize) '' @visibleColumns 'from['
@tableName ']' @strTemp '' @strOrderBy
end

--设置自定义输出OrderBy
if@orderByColumn!=''
begin
set@strTemp=@strSQL
if@orderBySortType=0
set@strSQL='select*from(' @strTemp ')asTempTable2orderby['
@orderByColumn ']asc'
else
set@strSQL='select*from(' @strTemp ')asTempTable2orderby['
@orderByColumn ']desc'
end

--测试生成的SQL语句
--PRINT@strSQL

exec(@strSQL)


2.使用ROW_NUMBER()的存储过程

/**//***********************************************************************
*文件名:GetRecordsPaginationWithRowNumber.sql
*功能:创建存储过程GetRecordsPaginationWithRowNumber
[注:运用SQL2005新增函数ROW_NUMBER(),有局限性!]

*创建时间:2007-10-17
*创建人:清风
*最后修改时间:2007-10-17
*最后修改人:清风
***********************************************************************/

IFEXISTS(
SELECT*
FROMINFORMATION_SCHEMA.ROUTINES
WHERESPECIFIC_NAME='GetRecordsPaginationWithRowNumber')
DROPPROCEDUREGetRecordsPaginationWithRowNumber

GO

CREATEPROCEDUREGetRecordsPaginationWithRowNumber
(
@tableNamevarchar(256),--进行分页表名
@columnNamevarchar(256),--用作分页的字段名
@pageSizeint=10,--每页记录数(默认是10)
@pageIndexint=1,--页码(默认第一页)
@sortTypebit=0,--数据查找排序类型,
--0升序,1降序(默认0)
@visibleColumnsvarchar(2000)='',--要返回的字段,格式"A1,B1,C1"(若为空,则返回全部)
@sqlConditionvarchar(2000)='',--附加查询条件(不包含'where'字串)
@orderByColumnvarchar(256)='',--记录最后排序OrderBy的字段(默认等于@columnName)
@orderBySortTypebit=0--记录最后排序类型,
--0升序,1降序(默认0)
)
AS

declare@strSQLvarchar(8000)--最终合成的SQL语句
declare@strTempvarchar(256)--临时字符串
declare@strOrderByvarchar(1000)--查找排序类型子句

--设置数据查找排序类型子句
if@sortType!=0
begin
set@strOrderBy='orderby[' @columnName ']desc'
end
else
begin
set@strOrderBy='orderby[' @columnName ']asc'
end

--设置要返回字段
if@visibleColumns=''
set@visibleColumns='*'

--设置查找语句
set@strSQL='selecttop' str(@pageSize) '' @visibleColumns
'from(select*,ROW_NUMBER()Over('
@strOrderBy ')asRowNumfrom[' @tableName '])asTempTable'
'whereRowNumbetween' str((@pageIndex-1)*@pageSize)
'and' str(@pageIndex*@pageSize)

--添加附加查询条件
if@sqlCondition!=''
set@strSQL='selecttop' str(@pageSize) '' @visibleColumns
'from(select*,ROW_NUMBER()Over('
@strOrderBy ')asRowNumfrom[' @tableName ']where'
@sqlCondition ')asTempTable'
'whereRowNumbetween' str((@pageIndex-1)*@pageSize)
'and' str(@pageIndex*@pageSize)

--优化第一页查询
if@pageIndex=1
begin
set@strTemp=''
if@sqlCondition!=''
set@strTemp='where(' @sqlCondition ')'

set@strSQL='selecttop' str(@pageSize) '' @visibleColumns 'from['
@tableName ']' @strTemp '' @strOrderBy
end

--设置自定义输出OrderBy
if@orderByColumn!=''
begin
set@strTemp=@strSQL
if@orderBySortType=0
set@strSQL='select*from(' @strTemp ')asTempTable2orderby['
@orderByColumn ']asc'
else
set@strSQL='select*from(' @strTemp ')asTempTable2orderby['
@orderByColumn ']desc'
end

--测试生成的SQL语句
PRINT@strSQL

exec(@strSQL)


3.测试代码

/**//***********************************************************************
*文件名:PageTest.sql
*功能:测试分页存储过程

*创建时间:2007-10-17
*创建人:清风
*最后修改时间:2007-10-17
*最后修改人:清风
***********************************************************************/

--创建测试数据
--CREATETABLEPagerTest
--(
--pGUIDuniqueidentifierprimarykey,
--pNumintNULL
--);
--
--DECLARE@iint
--set@i=1
--这里只使用了一百万行数据进行测试
--WHILE@i<1000000
--BEGIN
--INSERTINTOPagerTest
--VALUES(NEWID(),@i)
--set@i=@i 1
--END
--
--CREATEINDEXIdxGUIDONPagerTest(pGUID);
--CREATEINDEXIdxNumONPagerTest(pNum);

--测试数据
--SELECT*
--FROMPagerTest
--ORDERBYpNUMASC
--
--清空数据
--DROPTABLEPagerTest


--测试GetRecordsPagination
execGetRecordsPaginationPagerTest,pNum,10,10000,0,'','',pNum,1;
execGetRecordsPaginationPagerTest,pNum,10,10

--测试GetRecordsPaginationWithRowNumber
execGetRecordsPaginationWithRowNumberPagerTest,pNum,10,10
execGetRecordsPaginationWithRowNumberPagerTest,pNum,10,10,1,'pNum','pNum>999903',pNum,0;
execGetRecordsPaginationWithRowNumberPagerTest,pGUID,10,10,1,'','',pNum,0;

上一篇：用户权限管理经典篇-mengyao||AndyDotnetWorld--www.edongguan.com
下一篇：安装oracle10G即时客户端instantclient时msvcr71.dll缺失问题解决-zmxj的专栏
相关文章
·暂无
feedsky
抓虾
pageflakes
Rojo
google reader
netvibes
my yahoo
newsgator
bloglines
鲜果
哪吒
有道