这个存储过程我要执行35秒,请问有什么方法优化下吗?

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

ALTER proc [dbo].[L_DaCangKuaiJiFenLei]
(
@Inc_id nvarchar(8),
@CangKu nvarchar(4),
@CangKuSmall nvarchar(4000),
@RiQi_Begin nvarchar(10),
@RiQi_End nvarchar(10)
)
as
begin
declare @CangKuIds nvarchar(4000)
declare @ExecSql nvarchar(4000)
--if(@Inc_id='2')
--begin
-- if(@CangKu='仓1')
-- begin
-- set @CangKuIds = '1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 15, 16, 218, 219, 222, 213, 194, 156, 146,252'
-- end
-- else if(@CangKu='仓2')
-- begin
-- set @CangKuIds = '227,228,229,230,231,223,224,225,226,232,233,234,235,236,237,238,239,240,241,242,243,253'
-- end
-- else if(@CangKu='仓3')
-- begin
-- set @CangKuIds = '304,293,294,295,296,297,298,299,300,301,302,303'
-- end
-- else if(@CangKu='仓4')
-- begin
-- set @CangKuIds = '251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271'
-- end
-- else if(@CangKu='所有')
-- begin
-- set @CangKuIds = '1,2,3,4,5,6,7,9,10,11,12,13,15,16,218,219,222,213,194,156,146,227,228,229,230,231,223,224,225,226,232,233,234,235,236,237,238,239,240,241,242,243,304,293,294,295,296,297,298,299,300,301,302,303,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271'
-- end
--end
--else if(@Inc_id='3')
--begin
-- if(@CangKu='仓1')
-- begin
-- set @CangKuIds = '19,28,29,31,138'
-- end
-- else if(@CangKu='仓2')
-- begin
-- set @CangKuIds = '246,247,248,249,250'
-- end
--end
--else if(@Inc_id='19')
--begin
-- if(@CangKu='仓1')
-- begin
-- set @CangKuIds = '305,306,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,307,308'
-- end
--end
set @CangKuIds=@CangKuSmall
set @ExecSql ='select isnull(T.kuaiJi_sort,'''') as kuaiJi_sort,isnull(T.mer_unit,'''') as mer_unit,sum(T.ZhengChang) as ZhengChang,sum(T.CheJianRuKu) as CheJianRuKu,sum(T.ZhuanCang) as ZhuanCang,sum(T.WaiGou) as WaiGou,sum(T.PanYingPanKui) as PanYingPanKui,sum(T.XiaoShou) as XiaoShou,sum(T.ZuoFei) as ZuoFei,sum(T.ZiYong) as ZiYong,sum(T.ShangYueJieCun) as ShangYueJieCun,sum(T.BenYueRuKu) as BenYueRuKu,sum(T.BenYueChuKu) as BenYueChuKu from
(
select sp.mer_num,sp.mer_spec,cksp.kuaiJi_sort,sp.mer_unit
,
isnull(sum(
case
when ckzb.inout_modename=''正常'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''ZhengChang''
,
isnull(sum(
case
when ckzb.inout_modename=''车间入库'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''CheJianRuKu''
,
isnull(sum(
case
when ckzb.inout_modename=''转仓'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''ZhuanCang''
,
isnull(sum(
case
when ckzb.inout_modename=''外购'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''WaiGou''
,
isnull(sum(
case
when ckzb.inout_modename=''盘盈盘亏'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''PanYingPanKui''
,
isnull(sum(
case
when ckzb.inout_modename=''销售'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''XiaoShou''
,
isnull(sum(
case
when ckzb.inout_modename=''作废'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''ZuoFei''
,
isnull(sum(
case
when ckzb.inout_modename=''自用'' and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''ZiYong''
,
isnull(
cksp.ultimo_stock+
sum(case when ckzb.bill_type=2 and isnull(ckzb.auditing_flag,0)>1 and
ckzb.bill_date< '''+@RiQi_Begin+'''
then ckfb.mer_sum else 0 end)-
sum(case when ckzb.bill_type=1 and isnull(ckzb.auditing_flag,0)>1
and ckzb.bill_date< '''+@RiQi_Begin+'''
then ckfb.mer_sum else 0 end),0) as ''ShangYueJieCun''
,
isnull(sum(
case
when ckzb.bill_type = 2 and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''BenYueRuKu''
,
isnull(sum(
case
when ckzb.bill_type = 1 and isnull(ckzb.auditing_flag,0)>1 and (ckzb.bill_date between '''+@RiQi_Begin+''' and '''+@RiQi_End+''')
then ckfb.mer_sum else 0 end),0) as ''BenYueChuKu''
from ral_depot_mer as cksp
left join tbl_depot_accessorily_bill as ckfb
on cksp.id =ckfb.depot_mer_id
left join tbl_depot_mostly_bill as ckzb
on ckzb.id = ckfb.mostly_id
inner join mst_merchandise as sp
on sp.id = cksp.mer_id
where cksp.del_flag = 0 and sp.del_flag = 0
AND cksp.depot_id IN ('+@CangKuIds+') AND mer_fittings in (''管材'',''配件'')
group by sp.mer_num,sp.mer_spec,cksp.kuaiJi_sort,sp.mer_unit,cksp.ultimo_stock) as T group by T.kuaiJi_sort,T.mer_unit order by T.kuaiJi_sort'

--print @ExecSql
execute sp_executesql @ExecSql
end

上面有一段注释的,我用参数代替了!
...全文

相关推荐

Java代码性能优化的35个方法总结

本篇文章主要介绍了Java代码性能优化的35个方法,具有很好的参考价值,下面跟着小编一起来看下吧

weixin_38566180

35、存储过程性能优化与SARG查询解析

本文详细介绍了数据库存储过程性能优化技巧和SARG(Search Argument)查询的相关知识。涵盖游标选项选择、避免万圣节问题、减少事务中游标循环修改行、使用存储过程输出参数、禁用DONE_IN_PROC消息、DBCC命令使用等内容,并深入解析了SARG查询的定义、判断标准及优化方法,帮助提升数据库查询效率和整体性能。

甜甜圈HTTP

35、存储过程入门与应用详解

本文详细介绍了数据库存储过程的基本概念、创建方法以及在实际开发中的应用。通过具体示例,讲解了如何使用 SQL Server 查询分析器、企业管理器和 Visual Studio 的服务器资源管理器来创建存储过程,并结合 Web 表单演示了存储过程的数据检索功能。同时,文章还探讨了参数化存储过程的使用方式、优势及注意事项,并提供了性能优化技巧和错误处理机制,帮助开发者提高数据库应用的安全性与效率。

dapp9builder

MySQL 5.7.35 Windows版数据库系统免费下载

JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,易于人阅读和编写,同时也易于机器解析和生成。在MySQL 5.7.35中引入了对JSON数据类型的支持,使得MySQL可以更方便地存储和查询JSON文档。存储方式:JSON数据类型在MySQL中以一种优化的二进制格式存储。索引能力:MySQL还支持在JSON文档的path上创建索引,这允许数据库引擎高效地处理JSON文档查询。函数支持。

芝士校园

基于DSP 28M35的串口SCI软件在线升级IAP方法

基于DSP的IAP在线软件烧写升级,通过串口SCI来烧写升级程序,程序仍然是从flash启动不需要跳线,内含例程代码,可以参考。

archdevilcs
评论 15
收藏 已收藏
分享
返回顶部
Leo_0924
MS-SQL Server 34,876 成员 254,635 内容