100分求上列存储过程优化

2012-08-25

100分求下列存储过程优化100分求下列存储过程优化数据量很大，特别是第一次运动这个sp 的时候数据量基本是

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 FROM Gps_PE_PT_AOI WHERE UID IN(SELECT tmpUID FROM #tmptb)   
   DELETE FROM Gps_PE_PT_PartList WHERE UID IN(SELECT tmpUID FROM #tmptb)  
 
   --DELETE FROM Gps_PE_PT_Attachment WHERE UID IN(SELECT tmpUID FROM #tmptb)   
   DELETE FROM [Gps_PE_ProductionTraveler] WHERE [ScheduledListUID] IN (SELECT tmpScheduledListUID FROM #tmptb) 
   DELETE FROM Gps_PE_PT_TCAndIPQC WHERE UID IN(SELECT tmpUID FROM #tmptb) 
  END    
  --Add New schedule unit into production traveler when it is not in production traveler list 
 /*DECLARE @Sql VARCHAR(4000) 
 SET @Sql = ' 
 */ 
  INSERT INTO Gps_PE_ProductionTraveler   
  ([ScheduledListUID], 
   [WONO], 
   [LineUID], 
   [SideUID], 
   [ScheduledQty],   
   [ProcessUID],   
   [SolderPasteUID], 
   [CreatedDate] 
  )    
  SELECT  
  T1.[ScheduledListUID], 
  T1.[WONO], 
  T1.[LineUID],   
  T1.[SideUID],   
  T1.[ScheduledQty],   
  T1.ProcessUID,   
  T1.[SolderPasteUID], 
  getdate() 
  FROM [Gps_PM_ScheduledList] t1   
  INNER JOIN [Gps_PM_WaitingList] t2 ON t1.[WONO]=t2.WONO    
  INNER JOIN V_GpsDB_Gps_PE_ProductionTravelerTemplate_Last t4 ON t2.BOM=t4.[Long99PartNumber] --' 
  --SET @Sql = @Sql + '  
  WHERE NOT EXISTS(SELECT * FROM Gps_PE_ProductionTraveler WHERE [ScheduledListUID]=t1.[ScheduledListUID])   
  AND t1.[StatusUID]=1  and (t1.LineUID is not null) and t4.StatusUID=1 
  /*'  
 IF(@StartDate IS NOT NULL) 
 BEGIN 
 SET @Sql = @Sql + ' AND t1.ScheduledDate>=convert(datetime,''' + CONVERT(VARCHAR(200),@StartDate) + ''')' 
 END 
 IF(@EndDate IS NOT NULL) 
 BEGIN 
 SET @Sql = @Sql + ' AND t1.ScheduledDate <=convert(datetime,''' + CONVERT(VARCHAR(200),@EndDate) + ''')' 
 END 
 */ 
  
 --exec(@Sql) 
  
 --add Consumer Log 
 insert into Gps_PM_ScheduleConsumerLog(ScheduledDate,lineUID,Consumer,ConsumerFlag) 
 select ScheduledDate,LineUID,'PE Production Traveler',1 from ( 
 select distinct ScheduledDate,LineUID from Gps_PM_ScheduledList T1 where (T1.LineUID is not null) and t1.[StatusUID]=1 
 group by ScheduledDate,LineUID) a where  
 not exists(select * from Gps_PM_ScheduleConsumerLog where ScheduledDate=a.ScheduledDate and LineUID=a.LineUID) 
  
 --and ScheduledDate>=@StartDate and ScheduledDate <=@EndDate  
   
  
 --If PM scheduled unit finish, TC complete, IPQC will complete by manual 
 select T1.UID,T1.ScheduledListUID into #tblFinish from Gps_PE_PT_TCAndIPQC T1 
 inner join Gps_PM_ScheduledList T2 on T1.ScheduledListUID=T2.ScheduledListUID 
 where T2.StatusUID=7 and T1.StatusUID <>5 and T1.Flag=1 
  
 update Gps_PE_PT_TCAndIPQC set StatusUID=5 where UID in (select UID from #tblFinish) 
  
 --Update finished scheduled unit's stroke count 
 update Gps_PE_StencilPrinting set StrokeCount=Isnull(T1.StrokeCount,0) + IsNull(T2.StrokeCount,0) from 
 Gps_PE_StencilPrinting T1, 
 (   
 select Stencil_num,sum(StrokeCount) as StrokeCount from    
 (   
 select T2.Stencil_num,  
 case when Isnull(T5.LayoutColumn,0)=0 or Isnull(LayoutRow,0)=0 then 0   
 else (T3.ScheduledQty/(T5.LayoutColumn*T5.LayoutRow)) end as StrokeCount   
 from  
 ( 
 select distinct A.Stencil_num,B.ScheduledListUID from Gps_PE_PT_ScreenPrinting A 
 inner join Gps_PE_PT_TCAndIPQC B on A.UID=B.UID 
 where A.UID in (select UID from #tblFinish) 
 ) T2 
 inner join Gps_PM_ScheduledList T3 on T2.ScheduledListUID=T3.ScheduledListUID   
 inner join Gps_PM_WaitingList T4 on T3.WONO=T4.WONO   
 inner join v_GpsDB_GpsK99XDetail_Layout T5 on T4.BOM=T5.Long99PartNumber 
 ) A group by Stencil_num   
 ) T2 where T1.Stencil_num=T2.Stencil_num  
 drop table #tmptb 
 drop table #tblFinish  
 
 GO

[解决办法]
好长，帮顶..
[解决办法]
分步排查，看哪步最慢，再分别优化吧.
[解决办法]
查看执行计划
[解决办法]

探讨
分步排查，看哪步最慢，再分别优化吧.

[解决办法]
看了一下，没有什么明显要改的……
随便说点：
1.单条语句尽量写简短点(配合临时表等)
2.in,not,exists都导致无法有效利用索引，可适当寻求替代方案。
比如：DELETE FROM Gps_PE_PT_ReflowZones WHERE UID IN(SELECT tmpUID FROM #tmptb)
可写成：
DELETE Gps_PE_PT_ReflowZones
FROM #tmptb t
WHERE Gps_PE_PT_ReflowZones.UID=t.tmpUID

[解决办法]

SQL code

http://topic.csdn.net/t/20030513/12/1776646.html
[解决办法]

感觉楼主的嵌套太多了。
如果确实有必要，那另当别论，否则，尽量不要那么多嵌套，影响效率。

还有一个not,exists 这些地方，确实无法利用索引。

最主要的是要一块块的调试。一起调试总归看不出问题来。
[解决办法]
探讨
看了一下，没有什么明显要改的…… 
随便说点： 
1.单条语句尽量写简短点(配合临时表等) 
2.in,not,exists都导致无法有效利用索引，可适当寻求替代方案。 
比如：DELETE FROM Gps_PE_PT_ReflowZones WHERE UID IN(SELECT tmpUID FROM #tmptb) 
可写成： 
DELETE Gps_PE_PT_ReflowZones 
FROM  #tmptb t 
WHERE Gps_PE_PT_ReflowZones.UID=t.tmpUID 


[解决办法]
探讨
看了一下，没有什么明显要改的……
随便说点：
1.单条语句尽量写简短点(配合临时表等)
2.in,not,exists都导致无法有效利用索引，可适当寻求替代方案。
比如：DELETE FROM Gps_PE_PT_ReflowZones WHERE UID IN(SELECT tmpUID FROM #tmptb)
可写成：
DELETE Gps_PE_PT_ReflowZones
FROM  #tmptb t
WHERE Gps_PE_PT_ReflowZones.UID=t.tmpUID


[解决办法]
分步查一下，再用执行计划看看
如果不是需要，嵌套不要太多

热点排行

SQL Server

100分求上列存储过程优化