22,209
社区成员
发帖
与我相关
我的任务
分享优化！大佬们帮帮忙！
存储过程：
sql语句执行计划
索引：
要怎么优化这个存储过程，首先要优化的是存储过程中计算总页数与计算总记录数 。然后要如何提升整体速率，已经做了分页处理。应该主要是计算总页数与记录数的耗时太久。
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
ENDsql语句执行计划
索引：
要怎么优化这个存储过程，首先要优化的是存储过程中计算总页数与计算总记录数 。然后要如何提升整体速率，已经做了分页处理。应该主要是计算总页数与记录数的耗时太久。
...全文
请发表友善的回复…
发表回复
qq_15126639 2017-11-01
- 打赏
- 举报
你真的是太棒了~
吉普赛的歌 2017-10-31
- 打赏
- 举报
--改成：
--因为我不知道 fsje 是哪里来的，只取了主表。
--如果需要连接其它表，也只应该连接必要的，其它的不相干的表连接起来作甚？
SELECT COUNT(*),SUM(fsje)
FROM T_Z_JLLSTMP jlls吉普赛的歌 2017-10-31
- 打赏
- 举报
--原SQL：
SELECT count(*),
SUM(fsje)
FROM (
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
) j
二月十六 2017-10-31
- 打赏
- 举报
把left join能改成inner 改成inner join
SELECT COUNT(*) ,
SUM(fsje)
FROM ( SELECT jlls.fsje
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
) jlqq_15126639 2017-10-31
- 打赏
- 举报
先要优化统计数量和统计金额。
select 、count(*) ,sum(fsje) from (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吉普赛的歌 2017-10-31
- 打赏
- 举报
不想看你一大堆的存储过程了， 没有意义。
你直接把最终的SQL语句发出来吧。
qq_15126639 2017-10-31
- 打赏
- 举报
来人啊！！！！！！！！！！！！！11
二月十六 2017-10-31
- 打赏
- 举报
再改改，如果是left join的还没有where条件的，可以把left join去掉，inner join保留
SELECT COUNT(*) ,
SUM(fsje)
FROM T_Z_JLLSTMP jlls
INNER JOIN T_Z_JYM jym ON jlls.JYMID = jym.id
