请帮忙优化一下SQL

[naruto_zy]

数据大概有20W左右,结果出来有点慢,希望帮忙优化一下,SQL如下:




CREATE procedure datagsreport @wtype varchar,@year int
as
set nocount on
create table #temp(custgroup varchar(50),customer varchar(50) ,pfamily varchar(50),pname varchar(50),
productno varchar(3),
okmyear int ,okmqur int ,factoryname varchar(200),uaescause varchar(500) ,dept varchar(500), improvement varchar(500),
implemdate datetime ,u8dreport varchar(500), pcs decimal(13),bnum decimal(13),cnum decimal(13),
snum decimal(13),qty decimal(13))

declare @cg varchar(50),@cm varchar(50),@pf varchar(50),@pm varchar(50),@tyear int ,@tqur int ,
@ps decimal(13),@bm decimal(13),@cnm decimal(13),@sm decimal(13),@qt decimal(13)
,@fc varchar(200),@us varchar(500),@im varchar(500),@imdate datetime,@dp varchar (500),@u8 varchar(500)

if @wtype='o'
begin
select
custgroup,customer,pfamily,pname,okmyear,okmqur ,isnull(sum(pcs),0) as pcs,
isnull(sum(bnum),0) as bnum,isnull(sum(cnum),0) as cnum,isnull(sum(snum),0) as
snum ,factoryname,isnull(sum(qty),0) as qty
into #ta from (
select
custgroup,customer,pfamily,pname,okmyear,okmqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case okmqur when 1 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 1 and 3)
when 2 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 4 and 6)
when 3 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 7 and 9)
when 4 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 10 and 12)
end as qty from vw_datags ab
where typeno in (select typeno from reportswt where rid=24) and okmyear
between
@year-2 and @year
group by
custgroup,customer,pfamily,pname,productno,okmyear,okmqur,factoryname
) ac group by
custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname
order by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname

declare cur cursor for
select custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname,pcs,bnum,cnum,snum,qty from #ta
open cur
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
while @@fetch_status =0
begin
select top 1 @us=uaescause ,@im= improvement,@imdate =implemdate ,@dp=dept ,@u8=u8dreport from vw_datags bb
where typeno in (select typeno from reportswt where rid=24) and isnull(bb.custgroup,' ' )=isnull(@cg, ' ')
and isnull(bb.customer, ' ') =isnull( @cm, ' ') and isnull(bb.pfamily,' ') =isnull(@pf,' ' ) and
isnull(bb.pname,' ')=isnull(@pm,' ' ) and bb.okmyear= @tyear and bb.okmqur=@tqur
and isnull(bb.factoryname,' ' )=isnull(@fc, ' ')
group by uaescause ,dept,improvement,implemdate, u8dreport order by count(uaescause) desc

insert into #temp values (@cg,@cm,@pf,@pm,' ' ,@tyear,@tqur,@fc,@us ,@dp ,@im,@imdate,@u8,@ps,@bm,@cnm,@sm,@qt)
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
end
select * from #temp
end
else
begin
select
custgroup,customer,pfamily,pname,fieldyear,fieldqur ,isnull(sum(pcs),0) as pcs,
isnull(sum(bnum),0) as bnum,isnull(sum(cnum),0) as cnum,isnull(sum(snum),0) as
snum ,factoryname,isnull(sum(qty),0) as qty
into #tb from (
select
custgroup,customer,pfamily,pname,fieldyear,fieldqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case fieldqur when 1 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 1 and 3)
when 2 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 4 and 6)
when 3 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 7 and 9)
when 4 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 10 and 12)
end as qty from vw_datags ab
where typeno in (select typeno from reportswt where rid=25) and fieldyear
between
@year-2 and @year
group by
custgroup,customer,pfamily,pname,productno,fieldyear,fieldqur,factoryname
) ac group by
custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname
order by custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname

declare cur cursor for
select custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname,pcs,bnum,cnum,snum,qty from #tb
open cur
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
while @@fetch_status =0
begin
select top 1 @us=uaescause ,@im= improvement,@imdate =implemdate ,@dp=dept ,@u8=u8dreport from vw_datags bb
where typeno in (select typeno from reportswt where rid=25) and isnull(bb.custgroup,' ' )=isnull(@cg, ' ')
and isnull(bb.customer, ' ') =isnull( @cm, ' ') and isnull(bb.pfamily,' ') =isnull(@pf,' ' ) and
isnull(bb.pname,' ')=isnull(@pm,' ' ) and bb.fieldyear= @tyear and bb.fieldqur=@tqur
and isnull(bb.factoryname,' ' )=isnull(@fc, ' ')
group by uaescause ,dept,improvement,implemdate, u8dreport order by count(uaescause) desc

insert into #temp values (@cg,@cm,@pf,@pm,' ' ,@tyear,@tqur,@fc,@us ,@dp ,@im,@imdate,@u8,@ps,@bm,@cnm,@sm,@qt)
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
end
select * from #temp
end
close cur
deallocate cur
drop table #temp

[naruto_zy]

我已经改成这样了,还是很慢,请高人指点啊:
create procedure datagsreport @wtype varchar,@year int
as
set nocount on

select ac.* into #tb from (
SELECT c.[group] AS custgroup, c.customer,e.pfamily,d.pname, a.productno,year(a.fabdate) as okmyear,
case month(a.fabdate)
when 1 then 1
when 2 then 1
when 3 then 1
when 4 then 2
when 5 then 2
when 6 then 2
when 7 then 3
when 8 then 3
when 9 then 3
when 10 then 4
when 11 then 4
when 12 then 4
end
as okmqur, fac.factoryname ,
case when left(a.analysisresult,1)='4' then a.number
when left(a.analysisresult,1)='5' then b.number else 0 end as bnum,
case when left(a.analysisresult,1)='2' then a.number else 0 end as cnum,
case when left(a.analysisresult,1)='3' then a.number else 0 end as snum,
a.number as pcs ,a.implemdate ,a.dept,a.improvement ,a.u8dreport ,
case when left(a.analysisresult,1)='4' then rs.causedescen
when left(a.analysisresult,1)='5' then rs.causedescen else null end as uaescause
FROM dbo.complaintinfob a INNER JOIN
dbo.complaintinfoa b ON b.caID = a.caID AND a.type <> 'CP' AND b.status = 'E'
and a.typeno in (select typeno from reportswt where rid=25)
and year(a.fabdate) between @year-2 and @year INNER JOIN
dbo.customerinfo c ON a.customer = c.customer AND
c.customer NOT LIKE 'Retail%' INNER JOIN
dbo.productno d ON a.productno = d.productno
inner join productinfo e on b.pname=e.pname INNER JOIN failurecode f on a.analysisresult=f.failurecode
INNER JOIN ii_factory fac on e.ftid=fac.ftid inner join rootcause rs on a.rootcause=rs.id ) ac


select custgroup,customer,pfamily,pname,'' as productno ,okmyear,okmqur,factoryname,
case when isnull(sum(bnum),0) = 0 then null else
( select top 1 uaescause
from #tb bb
where isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')
and isnull(ac.customer,' ') =isnull(bb.customer,' ')
and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')
and isnull(ac.pname,' ') =isnull(bb.pname,' ')
and ac.okmyear =bb.okmyear and ac.okmqur =bb.okmqur
and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' ' )
group by uaescause ,improvement,implemdate,dept, u8dreport
order by count(uaescause) desc
) end as uaescause ,
case when isnull(sum(bnum),0) = 0 then null else
( select top 1 dept
from #tb bb
where isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')
and isnull(ac.customer,' ') =isnull(bb.customer,' ')
and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')
and isnull(ac.pname,' ') =isnull(bb.pname,' ')
and ac.okmyear =bb.okmyear and ac.okmqur =bb.okmqur
and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' ' )
group by uaescause ,improvement,implemdate,dept, u8dreport
order by count(uaescause) desc
) end as dept ,
case when isnull(sum(bnum),0) = 0 then null else
( select top 1 improvement
from #tb bb
where isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')
and isnull(ac.customer,' ') =isnull(bb.customer,' ')
and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')
and isnull(ac.pname,' ') =isnull(bb.pname,' ')
and ac.okmyear =bb.okmyear and ac.okmqur =bb.okmqur
and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' ' )
group by uaescause ,improvement,implemdate,dept, u8dreport
order by count(uaescause) desc
) end as improvement,
case when isnull(sum(bnum),0) = 0 then null else
( select top 1 implemdate
from #tb bb
where isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')
and isnull(ac.customer,' ') =isnull(bb.customer,' ')
and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')
and isnull(ac.pname,' ') =isnull(bb.pname,' ')
and ac.okmyear =bb.okmyear and ac.okmqur =bb.okmqur
and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' ' )
group by uaescause ,improvement,implemdate,dept, u8dreport
order by count(uaescause) desc
) end as implemdate ,
case when isnull(sum(bnum),0) = 0 then null else
( select top 1 u8dreport
from #tb bb
where isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')
and isnull(ac.customer,' ') =isnull(bb.customer,' ')
and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')
and isnull(ac.pname,' ') =isnull(bb.pname,' ')
and ac.okmyear =bb.okmyear and ac.okmqur =bb.okmqur
and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' ' )
group by uaescause ,improvement,implemdate,dept, u8dreport
order by count(uaescause) desc
) end as u8dreport,
isnull(sum(pcs),0) as pcs, isnull(sum(bnum),0) as bnum,
isnull(sum(cnum),0) as cnum,
isnull(sum(snum),0) as snum,
isnull(sum(qty),0) as qty
from ( select
custgroup,customer,pfamily,pname,okmyear,okmqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case okmqur when 1 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 1 and 3)
when 2 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 4 and 6)
when 3 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 7 and 9)
when 4 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 10 and 12)
end as qty from #tb ab group by custgroup,customer,pfamily,pname,productno,okmyear,okmqur,factoryname ) ac
group by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname
order by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname
drop table #tb

[lsqkeke]

有20W的数据 你用了游标处理就是慢了

[naruto_zy]

不用游标用嵌套一样的慢啊,有什么好的方法吗?

[NetCode]

建议使用临时表和while循环来取代游标

[wangdehao]

把游标用别的方式代替