专注于 JetBrains IDEA 全家桶，永久激活，教程
持续更新 PyCharm，IDEA，WebStorm，PhpStorm，DataGrip，RubyMine，CLion，AppCode 永久激活教程
当前位置：搜云库技术团队 Java汇总 正文

MSSQL一个高效率SQL储存过程分页，执行效率测试通过500万以上的数据

2020-07-29 分类：Java汇总 阅读(606) 
/****** Object: StoredProcedure [dbo].[proc_paged_2part_selectMax]  Script Date: 01/06/2010 15:06:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[proc_paged_2part_selectMax] 

( 

@tblName nvarchar(200), ----要显示的表或多个表的连接 

@fldName nvarchar(500) = '*', ----要显示的字段列表 

@pageSize int = 10, ----每页显示的记录个数 

@page int = 1, ----要显示哪一页的记录 

@fldSort nvarchar(200) = null, ----排序字段列表或条件 

@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') 

@strCondition nvarchar(1000) = null, ----查询条件,不需where 

@ID nvarchar(150), ----主表的主键 

@Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 

@pageCount int = 1 output, ----查询结果分页后的总页数 

@Counts int = 1 output ----查询到的记录数 

) 

AS 

SET NOCOUNT ON 

Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句 

Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句 

Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句 

Declare @strSortType nvarchar(10) ----数据排序规则A 

Declare @strFSortType nvarchar(10) ----数据排序规则B 

Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造 

Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 

declare @timediff datetime --耗时测试时间差 

select @timediff=getdate() 

if @Dist = 0 

begin 

 set @SqlSelect = 'select ' 

 set @SqlCounts = 'Count(*)' 

end 

else 

begin 

 set @SqlSelect = 'select distinct ' 

 set @SqlCounts = 'Count(DISTINCT '[+@ID+](mailto:+@ID+)')' 

end 

if @Sort=0 

begin 

 set @strFSortType=' ASC ' 

 set @strSortType=' DESC ' 

end 

else 

begin 

 set @strFSortType=' DESC ' 

 set @strSortType=' ASC ' 

end 

--------生成查询语句-------- 

--此处@strTmp为取得查询结果数量的语句 

if @strCondition is null or @strCondition='' --没有设置显示条件 

begin 

 set @sqlTmp = @fldName + ' From ' + @tblName 

 set @strTmp = @SqlSelect+' @Counts='[+@SqlCounts+](mailto:+@SqlCounts+)' FROM '[+@tblName](mailto:+@tblName) 

 set @strID = ' From ' + @tblName 

end 

else 

begin 

 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 

 set @strTmp = @SqlSelect+' @Counts='[+@SqlCounts+](mailto:+@SqlCounts+)' FROM '[+@tblName](mailto:+@tblName) + ' where (1>0) ' + @strCondition 

 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 

end 

----取得查询结果总数量----- 

exec sp_executesql @strTmp,N'@Counts int out '[,@Counts](mailto:,@Counts) out 

declare @tmpCounts int 

if @Counts = 0 

 set @tmpCounts = 1 

else 

 set @tmpCounts = @Counts 

 --取得分页总数 

 set @pageCount=(@[tmpCounts+@pageSize-1](mailto:tmpCounts+@pageSize-1))/@pageSize 

 /**//**//**//**当前页大于总页数 取最后一页**/ 

 if @page>@pageCount 

 set @page=@pageCount 

 --/*-----数据分页2分处理-------*/ 

 declare @pageIndex int --总数/页大小 

 declare @lastcount int --总数%页大小 

 set @pageIndex = @tmpCounts/@pageSize 

 set @lastcount = @tmpCounts%@pageSize 

 if @lastcount > 0 

 set @pageIndex = @pageIndex + 1 

 else 

 set @lastcount = @pagesize 

 --//***显示分页 

 if @strCondition is null or @strCondition='' --没有设置显示条件 

 begin 

 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 

 begin 

 if @page=1 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType 

 else 

 begin 

 if @Sort=1 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 end 

 else 

 begin 

 set @page = @[pageIndex-@page+1](mailto:pageIndex-@page+1) --后半部分数据处理 

 if @page <= 1 --最后一页数据显示 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else 

 if @Sort=1 

 begin 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 end 

 else --有查询条件 

 begin 

 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 

 begin 

 if @page=1 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType 

 else if(@Sort=1) 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 else 

 begin 

 set @page = @[pageIndex-@page+1](mailto:pageIndex-@page+1) --后半部分数据处理 

 if @page <= 1 --最后一页数据显示 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else if(@Sort=1) 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

------返回查询结果----- 

exec sp_executesql @strTmp 

select datediff([ms,@timediff,getdate](mailto:ms,@timediff,getdate)()) as 耗时 

--print @strTmp 

SET NOCOUNT OFF 

--经过测试，目前是我遇到执行性能最高的一个储存过程分页！相比sql2005的 ROW_NUMBER()的一些算法还高几乎快1倍，可以看看

文章永久链接：https://tech.souyunku.com/29824

AD：【JetBrains 全家桶，激活、破解、教程】获取 IDEA 激活码、PyCharm 激活码、WebStorm 激活码和 DataGrip 激活码，提供详细破解教程与永久激活方法。支持 IDEA 永久激活与破解，免费获取注册码与激活码，解决 2024/2025 版本激活问题，轻松实现所有 JetBrains 工具的激活。
未经允许不得转载：搜云库技术团队 » MSSQL一个高效率SQL储存过程分页，执行效率测试通过500万以上的数据

相关推荐

热门推荐专题

JetBrains 全家桶，激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程，支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程，还是最新激活码，均可免费获得，帮助开发者解决常见激活问题，确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码，快速解决激活难题，全面覆盖 2024/2025 版本！

联系我们联系我们