存储过程如此之慢！怎么解决

flingpig 2010-12-13 05:48:52



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO









ALTER     procedure DateDetail

@DateBegin smallDatetime,

@DateEnd smallDatetime,

@message varchar(50) = '' output

as

create Table #t_dateDetail(

Tdate varchar(10),

Allhow decimal(11,2),--营业收入合计

CPhow decimal(11,2),--菜品收入合计

wtHow decimal(11,2),--煨汤飞饼合计

jsHow decimal(11,2),--酒水合计

qtHow decimal(11,2),--其它合计



MoneyHow decimal(11,2),--现金合计

ZkHow decimal(11,2),--折扣合计

cqHow decimal(11,2),--餐券合计

mdHow decimal(11,2),--免单合计

yhHow decimal(11,2),--优惠券合计

qdHow decimal(11,2),--签单合计

zdHow decimal(11,2), --招待合计

pepoleTotal int,--人数合计

PepoleAvg decimal(11,2),--人均小计

)

--先将日期插入表格用

declare @d smalldatetime

set @d = @Datebegin

while @d<dateAdd(dd,1,@DateEnd)

begin

  insert into #t_DateDetail(Tdate) values(CONVERT(VARCHAR(10),@d,111))

  set @d = dateadd(dd,1,@d)

end

update #T_dateDetail set allHow= (select  isnull(Sum(应付金额),0)  from 帐单 where (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

delete from #T_dateDetail where allhow=0

update #T_dateDetail set PepoleTotal= (select  isnull(Sum(人数),1)  from 帐单 where (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set yhHow= (select  isnull(Sum(收到餐券),0)  from 帐单 where (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set cpHow= (  select  isnull( Sum(小计),0)  from 点菜明细表 where (goodsLB=0) and        (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set wtHow= (  select  isnull(Sum(小计),0)  from 点菜明细表 where (goodsLB=2) and        (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set jshow= (  select isnull(Sum(小计),0)  from 点菜明细表 where (goodsLB=1) and        (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set qthow= (  select isnull( Sum(小计),0)  from 点菜明细表 where (goodsLB=3) and        (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set moneyHow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='现金') and    (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and  (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set mdhow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='免单') and      (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set qdhow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='签单') and      (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set zdhow= (  select isnull( Sum(实收金额),0) from 帐单 where (结帐方式='招待') and      (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set zkhow= (  select isnull(Sum(折扣),0)  from 帐单  where   (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_dateDetail set cqhow= (  select isnull(Sum(收到餐券),0)  from 帐单 where   (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

update #T_datedetail set pepoleAVG=allHow/PepoleTotal



--update #T_dateDetail set yhkhow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='银行卡') and     (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

--update #T_dateDetail set xykhow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='信用卡') and     (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

--update #T_dateDetail set hykhow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='会员储值') and    (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))

--update #T_dateDetail set gzHow= (  select isnull(Sum(实收金额),0) from 帐单 where (结帐方式='挂帐') and        (开始时间>convert(datetime,#T_dateDetail.Tdate+' 0:00:00')) and (开始时间<convert(datetime,#T_dateDetail.Tdate+' 23:59:59')) and (isEnd=1))





select * from #T_dateDetail







GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO