日期:2014-05-17  浏览次数:20499 次

100分求下列存储过程优化
100分求下列存储过程优化
数据量很大,特别是第一次运动这个sp 的时候
数据量基本是百万级的
所以希望能够更快点
希望搞人指点下
SQL code
 
create table #tmptb(tmpUID int,tmpScheduledListUID int)

--Check Schedule unit changed or not changed
if exists(select * from Gps_PM_ScheduleConsumerLog where --ScheduledDate>=@StartDate and ScheduledDate <=@EndDate and
Consumer='PE Production Traveler' and ConsumerFlag=0)
begin
insert into #tmptb(tmpUID,tmpScheduledListUID)
select T1.UID,T1.[ScheduledListUID] from Gps_PE_PT_TCAndIPQC T1
inner join Gps_PE_ProductionTraveler T2 on T1.ScheduledListUID=T2.ScheduledListUID
where
exists(select * from Gps_PM_ScheduledList A
inner join Gps_PM_ScheduleConsumerLog B on A.ScheduledDate=B.ScheduledDate and A.LineUID=B.LineUID
where
--B.ScheduledDate>=@StartDate and B.ScheduledDate <=@EndDate and
B.Consumer='PE Production Traveler'
and B.ConsumerFlag=0 and A.ScheduledListUID=T2.ScheduledListUID)
and
(
exists(select * from Gps_PM_ScheduledList A
inner join Gps_PM_ScheduleConsumerLog B on A.ScheduledDate=B.ScheduledDate and A.LineUID=B.LineUID
where --B.ScheduledDate>=@StartDate and B.ScheduledDate <=@EndDate and
B.Consumer='PE Production Traveler'
and B.ConsumerFlag=0 and (A.LineUID <>T2.LineUID or A.ScheduledQty <>T2.ScheduledQty or A.SideUID <>T2.SideUID)
and A.ScheduledListUID=T2.ScheduledListUID)
)

--PM scheduled unit has tc
insert into #tmptb(tmpUID,tmpScheduledListUID)
select T1.UID,T1.[ScheduledListUID] from Gps_PE_PT_TCAndIPQC T1
inner join Gps_PE_ProductionTraveler T2 on T1.ScheduledListUID=T2.ScheduledListUID
where
(not exists(select * from Gps_PM_ScheduledList where ScheduledListUID=T2.ScheduledListUID))


--PM shcheduled unit has not any TC
insert into #tmptb(tmpUID,tmpScheduledListUID)
select -1,[ScheduledListUID] from Gps_PE_ProductionTraveler T1
where (not exists(select * from Gps_PM_ScheduledList where ScheduledListUID=T1.ScheduledListUID))

update Gps_PM_ScheduleConsumerLog set ConsumerFlag=1 where Consumer='PE Production Traveler' and ConsumerFlag=0 --and ScheduledDate>=@StartDate and ScheduledDate <=@EndDate
end

--delete the TC and IPQC by the changed schedule first 
IF EXISTS(SELECT * FROM #tmptb) 
BEGIN 
  DELETE FROM Gps_PE_PT_ReflowZones WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_Reflow WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_StencilPrintingParams WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_ScreenPrinting WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_SectionNotUse WHERE UID IN(SELECT tmpUID FROM #tmptb)   
  DELETE FROM Gps_PE_PT_SPI WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_CheckPoint_Previous WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_CheckPoint WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_PickPlace WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_Labelling WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_Depanel WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM GPS_PE_PT_LaserMarking WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_TakenActions WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FROM Gps_PE_PT_Remark WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  DELETE FR