优化存储过程 优化SQL

[udsbud]

CREATE PROCEDURE p_GetHouChuXiaoShouBaoBiao
@Date DateTime,
@HCPos varchar(20),
@HCID Int
AS
Declare @DateEnd datetime, @KCDC Bit, @HCMC varchar(20)
Select @DateEnd=@Date + 1, @KCDC=KCDC From t_sXTXX
Select @HCMC=HCMC From t_sDYXX Where PrintID= @HCID


Select case IsNULL(一级菜品分类名称, '')
when '' then
'总计:'
else
case IsNULL(二级菜品分类名称, '')
when '' then
一级菜品分类名称 + ' 合计:'
else
一级菜品分类名称
end
end AS 一级菜品分类名称,
case IsNULL(a.CPBM, '')
when '' then
二级菜品分类名称 + ' 小计:'
else 二级菜品分类名称
end AS 二级菜品分类名称,
a.CPBM AS 菜品编码, b.CPMC AS 菜品名称,
c.DWMC AS 单位, a.XSSL AS 销售数量,
a.ZSSL AS 赠送数量, a.GZSL AS 挂帐数量
From
(
Select h.FLMC AS 一级菜品分类名称,
g.FLMC AS 二级菜品分类名称,
b.CPBM,
d.JJDW AS JJDW,
Sum( Round( e.JBDWSL/d.JBDWSL* b.XFSL,0)) AS XSSL ,
Sum(dbo.f_IsZSSL(e. JBDWSL *b. XFSL,b.MXLX) ) AS ZSSL,
Sum(dbo.f_IsGZSL_ye ( e.JBDWSL * b.XFSL,b.MXLX,b.JZDJ)) AS GZSL ,
d.DYMC
From
(select * from t_dBXMX union all select * from t_dBXMX_bk) b
--上面这个表结构完全一样,前一个是当 月数据明细,后一个是历史数据明细
Left Outer Join t_dBXZFMX k on k.MXID=b.MXID
Left Outer Join ( select * from t_dBXDCDJ
union all
select * from t_dBXDCDJ_bk) c On b.DJBM=c.DJBM
--上面这个表结构完全一样,前一个是当 月点菜单据,后一个是历史点菜单据
Left Outer Join t_iBXXX a on a.BXBH=IsNull(k.YBX,c.BXBH)
--t_iBXXX包厢状态
Left Outer Join(
Select a.DYMC,a.CPBM,a.SSCP,b.JJDW,a.JBDWSL
from
(
Select DYMC, CPBM, SSCP, min(JBDWSL) JBDWSL
From t_dXSJG a --t_dXSJG菜品价格
Left Outer Join t_iCPJJDW b On JJDW=dWID
group by DYMC, CPBM, SSCP --t_iCPJJDW单位
) a
left outer join
(
Select Distinct DYMC, CPBM, SSCP, JJDW ,JBDWSL
From t_dXSJG a Left Outer Join t_iCPJJDW b
On JJDW=dWID
) b on a.DYMC=b.DYMC and a.CPBM=b.CPBM
and a.SSCP=b.SSCP and a.JBDWSL=b.JBDWSL

)d On b.CPBM=d.CPBM
Left Outer Join t_iCPJJDW e On b.XFDW=e.DWID
Left Outer Join t_iCPXX f On b.CPBM=f.CPBM
Left Outer Join t_iCPFL g On f.CPFL=g.FLBM
Left Outer Join t_iCPFL h On cast(f.CPFL / 1000 as int) = h.FLBM
Where (
Not( Exists(
Select * From t_iHCDY
Where HCMC=@HCPos and CKID=@HCID
and DYID=d.DYMC and DYWZ is not null
)
)
or
a.BXWZ in (
Select DYWZ From t_iHCDY
Where HCMC=@HCPos and CKID=@HCID and DYID=d.DYMC
)
)
and d.DYMC in (Select DYID from t_iHCDY Where HCMC=@HCPos AND CKID=@HCID)
and @Date = c.BBRQ
and cast(d.SSCP/1000 as int)=b.SSCP
Group By d.DYMC,h.FLMC, g.FLMC, b.CPBM, d.JJDW
) a
Left Outer Join t_iCPXX b On a.CPBM=b.CPBM
Left Outer Join t_iCPJJDW c On a.JJDW=c.DWID

[udsbud]

select * from t_dBXMX union all select * from t_dBXMX_bk
把这个用
select MXID,CPBM,XFSL,MXLX,JZDJ,DJBM,SSCP ,XFDW from t_dBXMX
union all
select MXID,CPBM,XFSL,MXLX,JZDJ,DJBM,SSCP,XFDW from t_dBXMX_bk
来代替是不是会快一点?

这个存储过程在用客户传过来的数据库,一两秒就可以查出来了.接下来几天查也是一两秒就出来.
可给客户用,第一天就可以一两秒就可以查出来,第二天以后都要20分钟才查得出来.晕死!

还有,我去客户那里发现查不出来.然后我就drop 掉,然后也是create这个存储过程,
也是,一两秒就可以查出来了,也是只能用一两天.第三天查就要20分钟左右了.

[dawugui]

很长很复杂,帮顶.

[懒得去死]

优化存储过程的本质是优化里面的SELECT语句。

EXPLAIN ，然后自己分析。

[udnui]

看的眼花,很长也很复杂,按你上面的说法,发现查不出来.然后我就drop 掉,然后也是create这个存储过程就正常了,要不要程序里试下在每次执行前drop,再create
这样也不行,达不到效果,试下改成视图看看