这个存储过程我要执行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

上面有一段注释的，我用参数代替了！
...全文
138 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
  • 打赏
  • 举报
 回复
语句够长,有缓存试试看看
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章