两个分页存储过程的效率比较.doc

两个分页存储过程的效率比较两个分页存储过程的效率比较(均为多表查询) -解密存储过程exec sp_decrypt p_page use Newt8c8go if exists(select * from dbo.sysobjects where name=p_page)drop procedure p_pagego /*分页的存储过程*/create procedure p_page( Tables varchar(1000), -表名,可以是多个表,但不能用别名 PrimaryKey varchar(100), -主键,可以为空,但Order为空时该值不能为空 Fields varchar(1000) = *, -查询字段(多个表的字段),为空表示select * PageSize int = 15, -每页记录数 CurrentPage int = 1, -当前页，表示第页 Filter varchar(1000) = null,-条件，可以为空，不用填where Group varchar(1000) = null, -分组依据，可以为空，不用填group by Sort varchar(200) = null, -排序,默认按主键升序排列,不用填order by TotalPage int output -总页数)with encryption -加密 asset nocount ondeclare intResult int begin tran declare sql nvarchar(4000)if Filter is null or Filter= set Sql = select intResult = count( + PrimaryKey + ) from + Tableselse set Sql = select intResult = count( + PrimaryKey + ) from + Tables + where + + Filter exec sp_executesql sql,NintResult int output,intResult outputselect TotalPage= ceiling(intResult+0.0)/PageSize) if Sort is null or Sort = set Sort = PrimaryKey declare SortTable varchar(100)declare SortName varchar(100)declare strSortColumn varchar(200)declare operator char(2)declare type varchar(100)declare prec int if charindex(desc,Sort)0 begin set strSortColumn = replace(Sort, desc, ) set operator = 0 begin set strSortColumn = replace(Sort, asc, ) set operator = = end else begin set strSortColumn = SORT set operator = = end if charindex(., strSortColumn) 0 begin set SortTable = substring(strSortColumn, 0, charindex(.,strSortColumn) set SortName=substring(strSortColumn,charindex(.,strSortColumn)+1, len(strSortColumn) endelse begin set SortTable = Tables set SortName = strSortColumn end select type=, prec=c.precfrom sysobjects o join syscolumns c on o.id=c.idjoin systypes t on c.xusertype=t.xusertypewhere = SortTable and = SortName if charindex(char, type) 0 set type = type + ( + cast(prec as varchar) + ) declare strPageSize varchar(50)declare strStartRow varchar(50)declare strFilter varchar(1000)declare strSimpleFilter varchar(1000)declare strGroup varchar(1000) if CurrentPage 1 set CurrentPage = 1 set strPageSize = cast(PageSize as varchar(50)set strStartRow = cast(CurrentPage - 1)*PageSize + 1) as varchar(50) if Filter is not null and Filter != begin set strFilter = where + Filter + set strSimpleFilter = and + Filter + endelse begin set strSimpleFilter = set strFilter = end if Group is not null and Group != set strGroup = group by + Group + else set strGroup = set sql = declare SortColumn + type + set rowcount + strStartRow + select SortColumn= + strSortColumn + from + Tables + strFilter + + strGroup + order by + Sort + set rowcount + strPageSize + select + Fields + from + Tables + where + strSortColumn + operator + SortColumn + strSimpleFilter + + strGroup + order by + Sort + exec(sql)print sqlif Error 0 begin RollBack Tran Return -1 endelse begin commit Tran return intResult end select count(1) from song -测试数据(音乐专辑)declarereturn_value int,TotalPage int,timediff datetimeselect timediff=getdate()exec return_value=p_pageTables=MusicAlbum left outer join Singer on MusicAlbum.SingerID=Singer.RecordID left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID,PrimaryKey=MusicAlbum.RecordID,Fields=MusicAlbum.RecordID,MusicAlbum.SingerID,MusicAlbum.Name as AlbumName,Singer.Name as SingerName,MusicArea.Name as MusicArea, MusicAlbum.SongList,MusicAlbum.IntroductionSmall,MusicAlbum.IssueDate, MusicAlbum.Label,MusicAlbum.CreateDate,PageSize=20,CurrentPage=1,Filter=,Group=,Sort=MusicAlbum.CreateDate desc,TotalPage=TotalPage outputselect TotalPage as NTotalPage select Return Value = return_valueselect datediff(ms,timediff,GetDate() as 耗时 -查看CPU用时和占用时间,以此优化存储过程set statistics io on/off set statistics time on/off -测试数据(流行歌曲)declarereturn_value int,TotalPage int,timediff datetime select timediff=Getdate()exec return_value=p_pageTables=Song left outer join Singer on Song.SingerIDList=Singer.RecordID left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID,PrimaryKey=Song.RecordID,Fields=Song.RecordID,MusicArea.Name as Area,Song.Name as songName, Singer.Name as singerName,Song.State as IsOpen, Song.CreateDate as Date,Song.EditPeople as Editor,PageSize=20,CurrentPage=15244,Filter=,Group=,Sort=Song.CreateDate desc,TotalPage=TotalPage outputselect TotalPage as NTotalPage select Return Value = return_valueselect datediff(ms,timediff,GetDate() as 耗时 -查看CPU用时和占用时间,以此优化存储过程set statistics io onset statistics time on set ansi_nulls ongo set quoted_identifier ongo use Newt8c8go if exists(select * from dbo.sysobjects where name=page_2005)drop procedure page_2005go create proc page_2005 TableName varchar(500), -表名 Fields varchar(5000) = *, -字段名(全部字段为*) OrderField varchar(5000), -排序字段(必须!支持多字段) sqlWhere varchar(5000) = Null, -条件语句(不用加where) pageSize int, -每页多少条记录 pageIndex int = 1 , -指定当前为第几页 totalRecord int = 0, -总记录数 TotalPage int output -返回总页数 asbegin Begin tran -开始事务 declare sql nvarchar(4000); if totalRecord=0 begin -计算总记录数 if (SqlWhere= or sqlWhere=null) set sql = select totalRecord = count(*) from + TableName else set sql = select totalRecord = count(*) from + TableName + with(nolock) where + sqlWhere exec sp_executesql sql,NtotalRecord int output,totalRecord output-计算总记录数 end -计算总页数 select TotalPage=ceiling(totalRecord+0.0)/PageSize) if (SqlWhere= or sqlWhere=NULL) set sql = select * from (select row_number() over(order by + OrderField + ) as rowId, + Fields + from + TableName else set sql = select * FROM (select row_number() over(order by + OrderField + ) as rowId, + Fields + from + TableName + with(nolock) where + SqlWhere -处理页数超出范围情况 if PageIndexTotalPage set pageIndex = TotalPage -处理开始点和结束点 declare StartRecord int declare EndRecord int set StartRecord = (pageIndex-1)*PageSize + 1 set EndRecord = StartRecord + pageSize - 1 -继续合成sql语句 set Sql = Sql + ) as t where rowId between + Convert(varchar(50),StartRecord) + and + Convert(varchar(50),EndRecord) print sql exec(Sql) - If Error 0 begin rollBack tran return -1 end else begin commit tran return totalRecord -返回记录总数 end endgo -测试数据declarereturn_value int,totalRecord int,TotalPage int,timediff datetime select totalRecord=count(*) from Songselect timediff=Getdate()exec return_value=page_2005TableName=Song left outer join Singer on Song.SingerIDList=Singer.RecordID left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID,Fields=Song.RecordID,MusicArea.Name as Area,Song.Name as songName, Singer.Name as singerName,Song.State as IsOpen, Song.CreateDate a