sqlserver2005 的分页测试附上分页存储过程

r囧r小猫 2022-08-07 09:38 174阅读 0赞

直接贴测试的sql脚本\--数据表Table10001数据量60万 -- 结论1：在1000页以内翻页两种方法没有区别 -- 两个top分页方法是在 (1w)1500毫秒，最后一页（3140毫秒）select drid,title ,year -- ROW\_NUMBER分页方法在(1w)1000毫秒 ,最后一页（1576毫秒）select drid,title ,year --结论2：还发现一个问题，如果多一个选择列的话，速度差别更大，\*标识 30个字段 -- 两个top分页方法是在(1w)43056毫秒，最后一页（93436毫秒）select drid,title ,year -- ROW\_NUMBER分页方法在(1w)27560毫秒 ,最后一页（41210毫秒）select drid,title ,year -------------//测试分页总结--------------------------------------- declare @page int set @page=33203; --当前页 declare @pagecount int --页总数33203 declare @totleCount int set @totleCount=664053--记录总数 declare @perpage int set @perpage=20;--每页 set @pagecount=@totleCount/@perpage+1 ; select @pagecount declare @d datetime --测试时间 ----------利用两个top分页------------------------------- set @d=getdate() declare @nTcount int set @nTcount = @perpage + @perpage \* (@page - 1); select top (@perpage) drid,title ,year from (select top (@nTcount) drid,title,year from Table10001 order by year desc,drid desc) as tbb order by year asc,drid asc select \[语句执行花费时间(毫秒)\]=datediff(ms,@d,getdate()) --------利用ROW\_NUMBER分页--------------------------------- set @d=getdate() declare @s int set @s=(@page-1)\*@perpage declare @e int set @e=(@page)\*(@perpage) ; With Cust AS ( SELECT drid, title,year, ROW\_NUMBER() OVER (order by \[year\] desc,drid desc ) as RowNumber FROM Table10001 ) select \* from Cust Where RowNumber Between @s and @e-1 select \[语句执行花费时间(毫秒)\]=datediff(ms,@d,getdate())

下面 附上 可用 的 ms sql 的查询分页存储过程：

注：（OrderField 必须唯一，如果为主键速度很快）

set ANSI\_NULLS ON set QUOTED\_IDENTIFIER ON go CREATE procedure \[dbo\].\[SqlDataSearchListPaging\] @tbName varchar(255), --表名 @tbFields varchar(1000), --返回字段 @OrderField varchar(255), --排序的字段名 @PageSize int, --页尺寸 @PageIndex int, --页码 @OrderType bit, --排序类型,1是升序,0是降序 @strWhere varchar(1500) --查询条件 --@Total int output , --返回总记录数 as declare @strSql varchar(5000) --主语句 declare @strOrder varchar(200) --排序 declare @strSqlCount nvarchar(500)--查询记录总数主语句 declare @strtemp varchar(50) --排序附加语句 --------------排序-----------------0是降序,1未升序 --set @Total=0 if @OrderType !=0 begin set @strtemp= '>(select max(' set @strOrder='order by ' + @OrderField + ' asc ' end else begin set @strtemp= '<(select min(' set @strOrder='order by '+ @OrderField + ' desc' end --------------总记录数--------------- --if @strWhere !='' -- begin -- set @strSqlCount='Select @TotalCout=count(\*) from ' + @tbName + 'where '+ @strWhere -- end --else -- begin -- set @strSqlCount='Select @TotalCout=count(\*) from ' + @tbName -- end --------------分页为第一页------------ if @PageIndex =1 or @PageIndex <=0 begin set @PageIndex =1 if @strWhere !='' begin set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder end else begin set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder end end else begin if @strWhere !='' begin set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields +' from ' + @tbName + ' where '+@OrderField +' ' + @strtemp+ ' ' +@OrderField+ ') from (select top' +str((@PageIndex-1)\*@PageSize) + ' '+@OrderField +' from '+@tbName+' where ' + @strWhere+ ' '+ @strOrder +') as tb) AND '+ @strWhere+ ' '+ @strOrder end else begin set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields +' from ' + @tbName + ' where '+@OrderField + ' ' + @strtemp+ ' ' +@OrderField+ ') from (select top' +str((@PageIndex-1)\*@PageSize) + ' '+@OrderField +' from '+@tbName + ' '+ @strOrder +') as tb)'+ @strOrder end end --exec sp\_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)