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

Leo_0924 2009-08-17 09:38:28
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

上面有一段注释的，我用参数代替了！
...全文
177 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
Leo_0924 2009-08-18
  • 打赏
  • 举报
 回复
頂最後一下，結貼~
Leo_0924 2009-08-18
  • 打赏
  • 举报
 回复
不是說用臨時表 變量啥的耗費資源嗎？我用過一次臨時表，他的查詢時間也是30多秒，好太無耐了，我就又改回來了。。。
chencane 2009-08-18
  • 打赏
  • 举报
 回复
看来是分类统计，建议楼主用临时表或表变量，把现在一次查询得到的数据，分开进行统计，分别插入到临时表中。
chencane 2009-08-18
  • 打赏
  • 举报
 回复
帮楼主重新贴下。




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


上面有一段注释的，我用参数代替了！
cgx_4 2009-08-18
  • 打赏
  • 举报
 回复
可以试试先跑出一张中间表来,然后再在中间表上做计算~~
Leo_0924 2009-08-18
  • 打赏
  • 举报
 回复
自己頂一下！
Leo_0924 2009-08-17
  • 打赏
  • 举报
 回复
临时表的处理方式也需要30多秒，还是不行啊
Leo_0924 2009-08-17
  • 打赏
  • 举报
 回复
好，那我就先用下临时表看看，希望大家仍然继续给意见。
SQL77 2009-08-17
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 leo_0924 的回复:]
请问下，还有什么解决方案吗？最后是对SQL语句下手
[/Quote]
SQL 语句能优化的地方就优化,做成视图
昵称被占用了 2009-08-17
  • 打赏
  • 举报
 回复
思路问题，为什么一定要用这么多层的子查询凑成一个一句实现呢，应该根据算法拆成多步实现，中间数据可以用临时表，具体的实现因为需要看清楚你的业务，就不化时间了
SQL77 2009-08-17
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 leo_0924 的回复:]
是用来统计报表的，没办法，希望得到大家帮助！~
[/Quote]
是否可以做成视图,我这边都用的视图
Leo_0924 2009-08-17
  • 打赏
  • 举报
 回复
请问下，还有什么解决方案吗？最后是对SQL语句下手
jinjazz 2009-08-17
  • 打赏
  • 举报
 回复
最直接的办法，定时按照报表格式生成静态表
Leo_0924 2009-08-17
  • 打赏
  • 举报
 回复
是用来统计报表的，没办法，希望得到大家帮助！~
SQL77 2009-08-17
  • 打赏
  • 举报
 回复
语句够长,有缓存试试看看
人工智能基于Spring AI的七境诊断引擎架构设计：东方修心智慧驱动的智能体核心创新实现
内容概要：本文提出“华光境·Spring AI智能体架构”，强调在AI系统设计中应突出核心创新而非盲目堆砌技术。通过将“七境诊断系统”（真诚、清净、平等、华光、无畏、欢喜、自在）作为智能体的中枢神经系统，构建以七境引擎为核心的Spring AI架构。文章详细阐述了七境引擎的架构设计、匹配算法（七境归元器）、结构化知识库（心境图谱）以及可视化呈现方式，主张将东方修心智慧深度融入AI架构，实现可解释、可追踪、可更新的智能化诊断与输出。同时提供了架构评估的“自信矩阵”与“五个必须”检查清单，避免装饰化、黑箱化等常见误区。; 适合人群：具备Spring Boot与AI应用开发经验，关注架构设计与系统创新的技术负责人、AI产品经理及中高级研发工程师。; 使用场景及目标：① 设计具有文化内涵与情感智能的企业级AI智能体；② 在技术架构中突出核心创新点，提升系统的可解释性与差异化竞争力；③ 避免AI项目陷入技术堆砌或价值模糊的困境，实现“大道至简”的自信架构表达。; 阅读建议：此资源不仅提供代码实现，更强调架构思维与创新表达，建议结合文中的架构图、接口设计与可视化方案进行实践，并运用“五个必须”清单持续检验核心创新在系统中的渗透程度。
FATF 资产追回的运营挑战.pdf
FATF 资产追回的运营挑战.pdf
CSDN首页 发布文章 CSDN同步助手 全部(10335) 已发布(10276) 审核中未通过(0) 回收站(59) 草稿箱(2041) 清空草稿箱 请输入关键词 文章 操作 SCI
内容概要：本文详细介绍了一个基于Simulink构建的功能完备的单相非控全波桥式整流器仿真模型，重点聚焦于其在电阻-电感（RL）负载条件下的工作特性分析。该模型完整实现了交流到直流的转换过程，能够精确呈现整流电路的关键工作原理，并动态展示输出电压与电流的波形变化，涵盖导通角、换向过程及负载影响等核心现象，适用于对电力电子变换技术的基础研究与高级仿真分析。; 适合人群：电气工程、自动化、电力电子及相关专业的本科生、研究生，以及从事电力电子系统设计与仿真的初级科研人员和工程技术人员。; 使用场景及目标：①用于高校课程教学与实验，帮助学生深入理解全波桥式整流器的工作机制及RL负载对输出特性的影响规律；②作为电力电子系统仿真的基础模块，支撑更复杂的电源变换系统（如滤波电路、稳压系统）的设计与验证；③辅助工程技术人员开展整流电路的参数优化、性能评估与故障诊断研究。; 阅读建议：建议读者在Simulink环境中动手搭建并调试该模型，通过调整电源频率、电感与电阻参数，观察其对输出波形平滑度、电流连续性及谐波含量的影响，进而深入掌握整流电路的动态响应特性，并可在此基础上拓展为可控整流、PWM整流或功率因数校正电路的研究。
无人机基于改进粒子群算法的无人机路径规划研究[和遗传算法、粒子群算法进行比较]（Matlab代码实现）
内容概要：本文聚焦于“基于改进粒子群算法的无人机路径规划研究”，通过Matlab平台实现算法仿真，并与遗传算法、标准粒子群算法进行对比分析。研究旨在通过改进粒子群优化（PSO）算法，提升无人机在复杂环境下的路径规划性能，重点优化路径长度、避障能力及算法收敛速度。文中系统阐述了改进PSO的算法设计原理、数学模型构建、关键参数设定及仿真环境搭建流程，通过大量仿真实验验证了所提算法在路径规划任务中的有效性与优越性，为智能优化算法在无人系统中的应用提供了可靠的技术支持。; 适合人群：具备一定智能优化算法基础和Matlab编程能力，从事无人机控制、路径规划、智能导航、自动化及人工智能等相关领域的研究生、科研人员和工程技术人员。; 使用场景及目标：①应用于无人机自主飞行、智能交通系统、无人作战平台等实际场景中的路径规划任务，提升系统智能化决策水平；②为算法研究人员提供改进PSO算法的完整实现方案，支持与遗传算法（GA）等主流智能算法的性能对比研究；③服务于科研论文复现、课程设计、毕业设计及教学演示，推动智能优化技术的教学与实践发展。; 阅读建议：建议读者结合提供的Matlab代码进行同步运行与调试，深入理解算法迭代机制与参数敏感性，可进一步尝试在不同地形条件、动态障碍物环境或三维空间中开展扩展性实验，以全面掌握路径规划算法的设计思路与优化技巧。
arcgis空间插值和等值线生成
代码下载地址： https://pan.quark.cn/s/b55ca947cc87 概述 SpatialImageService提供了一个后台AQI渲染图片服务，服务采用NodeJS开发，通过空间插值算法计算污染分布，并通过NodeCanvas绘制图片\ 前端通过post将数据传入后台处理，后台返回渲染后的图片，可用于前端地图叠加\ 系统可独立部署，图片获取可跨域，与应用相对独立。 运行环境 nodejs 依赖 node-canvas\ d3\ express 配置 通过配置bin/www 配置服务端口号 启动 node hot.js 接口 渲染服务： url: /render/spa\ 说明:返回渲染后的png图片\ 方式：get\ 参数：{data:\ {datas:[{lat:117,lng:21,value}...],}\ size:[200,300],\ center:[117.0,21.2],\ scale:4000,\ sectorName:'广东省'}\ 返回：image/png\ \ url: /render/location\ 说明：返回图片ne,sw对应的坐标\ 方式：get\ 参数：{ size:[200,300],\ center:[117.0,21.2],\ scale:4000,\}\ 返回：appliction/json\ 渲染图
MS-SQL Server

34,876

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章