优化!大佬们帮帮忙!

qq_15126639 qq_15126639 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
存储过程:

tblName="(select jlls.*,jllsfb.CLH,jllsfb.CZCLH,jllsfb.CZJE,jllsfb.CZYID,case jym.XSFX when 0 then jfsu.name else dfsu.name end yhmc, case jym.XSFX when 0 then jfsu.user_no else dfsu.user_no end yhbh,case jym.XSFX when 0 then jfdept.name else dfdept.name end bmmc, case jym.XSFX when 0 then jlls.JFZHJE else jlls.DFZHJE end zhje,case jym.XSFX when 0 then jlls.JFKYE else jlls.DFKYE end kye, case jym.XSFX when 0 then jfzh.FHZZH else dfzh.FHZZH end khzh,case jym.XSFX when 0 then jfzhfb.ZHMC else dfzhfb.ZHMC end zhmc,supersu.user_no suno, supersu.name suname, jym.JYMMC,sbxx.SBMC from T_Z_JLLSTMP jlls inner join T_Z_JYM jym on jlls.JYMID = jym.id left join T_Z_JLLSFB jllsfb on jlls.id = jllsfb.CLH left join sys_user supersu on jllsfb.CZYID = supersu.id left join T_Z_ZH jfzh on jlls.JFKHZHID = jfzh.id left join sys_user jfsu on jfzh.YHID = jfsu.id left join sys_dept jfdept on jfsu.dept_id = jfdept.id left join T_Z_ZH dfzh on jlls.DFKHZHID = dfzh.id left join sys_user dfsu on dfzh.YHID = dfsu.id left join sys_dept dfdept on dfsu.dept_id = dfdept.id left join T_Z_ZHFB dfzhfb on dfzh.id=dfzhfb.ZHID left join T_Z_ZHFB jfzhfb on jfzh.id= jfzhfb.ZHID left join T_D_SBXX sbxx on sbxx.id =jlls.SBID) jl"

fieldname="id,ZT,yhbh,yhmc,bmmc,zhmc,JYMMC,RQ_UINT,SJCZRQ_UINT,ZT,XKZT,FSJE,kye,zhje,LJJYXH_SHORT,TJJYXH_SHORT,suname,SBMC"

OrderField=" id desc"

PageSize=20

strWhere=" 1=1 and jl.org_id=70 "


CREATE PROCEDURE [dbo].[UP_GetRecordByPageNewBB] 
@tblName varchar(2000), --表名
@fieldName varchar(2000) = '*', --字段名(全部字段为*)
@OrderField varchar(1000), --排序字段(必须!支持多字段)
@PageSize int, --每页多少条记录
@PageIndex int = 1 , --指定当前为第几页
@strWhere varchar(2000) = Null, --条件语句(不用加where)
@GroupField varchar(1000) --分组字段(必须!支持多字段)
AS
BEGIN

Begin Tran --开始事务
declare @TotalPage int;
declare @totalRecord int;
declare @totalMoney money;
Declare @sql nvarchar(4000);

--计算总页数
if (@strWhere='' or @strWhere=NULL)
set @sql = 'select @totalRecord = count(*),@totalMoney = sum(fsje) from ' + @tblName
else
set @sql = 'select @totalRecord = count(*),@totalMoney = sum(fsje) from ' + @tblName + ' where ' + @strWhere

EXEC sp_executesql @sql,N'@totalRecord int OUTPUT,@totalMoney money OUTPUT',@totalRecord OUTPUT,@totalMoney OUTPUT--计算总记录数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)


set @sql=' Select '+@fieldName+' FROM '+@tblName+' where id in ( '

if (@strWhere='' or @strWhere=NULL)
set @sql+='select id from (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId, jl.* from '+@tblName+' ) as t '

else
set @sql+='select id from (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId, jl.* from '+@tblName+' where '+@strWhere+') as t '

--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage

--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int

set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
set @sql= @sql+' ) group by '+@GroupField+' order by '+@OrderField+''

print @sql
Exec(@Sql)
---------------------------------------------------
--计算总记录数
--if (@strWhere='' or @strWhere=NULL)
--set @sql = 'select count(*) from ' + @tblName
--else
--set @sql = 'select count(*) from ' + @tblName + ' where ' + @strWhere
--EXEC (@sql)
select @totalRecord,@totalMoney

If @@Error <> 0
Begin
RollBack Tran
End
Else
Begin
Commit Tran
End
END


sql语句执行计划


索引:


要怎么优化这个存储过程,首先要优化的是存储过程中计算总页数与计算总记录数 。然后要如何提升整体速率,已经做了分页处理。应该主要是计算总页数与记录数的耗时太久。
...全文

相关推荐

评论 8
收藏 已收藏
分享
返回顶部
qq_15126639
疑难问题 22,298 成员 121,727 内容