22,210
社区成员
发帖
与我相关
我的任务
分享
这个怎存储过程么优化,执行速度超慢!数据量不大,执行效率低!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,lsl>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--测试语句-- exec [proce_Find_ATT] '2011-03-31' ,'MFI','Cartridge','L1'
-- =============================================
ALTER PROCEDURE [dbo].[proce_Find_ATT]
@Testingdate varchar(50),
@M00_Family varchar(20),
@M01_Area varchar(50),
@M02_Line varchar(50)
AS
BEGIN
select
Value_Time= Fixing_DT,
Testingdate,
M00_Family,
M01_Area,
Class,
M06_Station,
P01_HumanCount,
M02_Line,
型号=case when @M01_Area='Cartridge' then M07_ModelTypeCode
else P05_PartNumber
end,
GROUPCode,
设备=case when @M01_Area='Cartridge' then M02_Line+'_'+M06_Station
else M06_Station
end,
AVGValue= (select avg(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,
MaxValue= (select max(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,
MinValue= (select min(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,
Min_Cyc= (case when ((select max(t.bb) from (select count(Fixing_DT) as bb from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and P01_HumanCount=b.P01_HumanCount and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and P01_HumanCount=d.P01_HumanCount and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and GROUPCode=d.GROUPCode and P05_PartNumber=d.P05_PartNumber
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT ) t where t.bb>1))>1
and ((select max(t.bb) from (select count(Fixing_DT) as bb from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and P01_HumanCount=b.P01_HumanCount and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=d.GROUPCode and P05_PartNumber=d.P05_PartNumber
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT ) t where t.bb>1)) is not null
then (select min(k.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station and M07_ModelTypeCode=f.M07_ModelTypeCode and Class=f.Class and GROUPCode=f.GROUPCode and P01_HumanCount=b.P01_HumanCount and P05_PartNumber=f.P05_PartNumber
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT having(count(Fixing_DT)=
((select max(t.bb) from (select count(Fixing_DT) as bb from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=b.GROUPCode and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and GROUPCode=d.GROUPCode and P01_HumanCount=d.P01_HumanCount and P05_PartNumber=d.P05_PartNumber
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,P05_PartNumber,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,P05_PartNumber,GROUPCode,Fixing_DT ) t where t.bb>1))
)) k)
else (select avg(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and P01_HumanCount=b.P01_HumanCount and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station and M07_ModelTypeCode=f.M07_ModelTypeCode and Class=f.Class and GROUPCode=f.GROUPCode and P01_HumanCount=f.P01_HumanCount and P05_PartNumber=f.P05_PartNumber
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P05_PartNumber,P01_HumanCount,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P05_PartNumber,P01_HumanCount,GROUPCode,Fixing_DT ) t)
end)
,
CT_Min_Cyc= case when (select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber )is not null then
(case when ((select max(t.bb) from (select count(Fixing_DT) as bb from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and P01_HumanCount=b.P01_HumanCount and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and P01_HumanCount=b.P01_HumanCount
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,GROUPCode ,P05_PartNumber)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,GROUPCode,P05_PartNumber,Fixing_DT ) t where t.bb>1))>1
and ((select max(t.bb) from (select count(Fixing_DT) as bb from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and P01_HumanCount=b.P01_HumanCount and Fixing_DT<
--另外基于执行计划,LZ可以看看具体问题发生在什么地方。
什么意思?
我要用索引优化有作用吗?