SP執行起來太慢，跑一次8個小時左右，如何優化？

Question

後臺數據量比較大，所以撈一次就差不多要7.8個小時左右，如何優化代碼？

ALTER PROCEDURE [dbo].[EMS_GETReferlife]
 @PartNum varchar(20)='4201CUT001210991',
 @BeginDate  varchar(30)='2019/01/01',
 @EndDate varchar(30)='2019/02/15' ,
 --@EType varchar(12)='H-1'
 @EType varchar(12)=''
as

If @BeginDate='12/30/1899'
Begin
set @BeginDate=''
set @EndDate=''
end 

Create Table #EMS_GetReferLife
(
  PartNum varchar(32) null,
  MatName varchar(120) null,
  ReferLife float null,
  UseLife float null
)

Create Table #EMS_GetReferLife_Type
(
  PartNum varchar(32) null,
  MatName varchar(120) null,
  ReferLife float null,
  UseLife float null,
  EquipType varchar(50) null --新增組別
)

Create Table #UseLife
(
  PartNum varchar(32) null,
  UseLife float null,
  EquipType varchar(50) null --新增組別
)

Declare @Cond varchar(300) = ''
--Declare @UseLife_Temp float = 0

IF ISNULL(@PartNum,'') <> ''
Begin
    Select @Cond = @Cond + ' And PartNum = ''' + @PartNum + ''' '
End

Exec(
    'insert into  #EMS_GetReferLife(PartNum,MatName,ReferLife) '+
    'select PartNum,MatName,ReferLife '+
    'from EMS_PartNumBasic(nolock) '+
    'where 1=1 '+ 
    @Cond
    )


declare @PartNumt Varchar(max)
   DECLARE cur CURSOR FOR  
        Select PartNum From #EMS_GetReferLife(NoLock)             
    OPEN cur
    FETCH NEXT FROM cur INTO @PartNumt
    WHILE @@FETCH_STATUS = 0
    BEGIN
        insert into #UseLife(PartNum,UseLife,EquipType)
        select t1.partnum,t1.uselife,t2.EquipType
        from dbo.EMS_RequestPart t1(nolock) ,dbo.EMS_RequestMas t2(nolock) 
         where  t2.papernum = t1.PaperNum
         and  t1.PartNum =@PartNumt
         and ((paperdate>@BeginDate) or (@BeginDate=''))
         and ((paperdate<@EndDate) or (@EndDate=''))
         and ((t2.Equiptype=@EType) or (@EType=''))

        insert into #UseLife(PartNum,UseLife,EquipType)
        select t1.PartNum,t1.uselife,t2.EquipType
        from dbo.EMS_RequestPart_main t1(nolock) ,dbo.EMS_maintainnoticeMas t2(nolock) 
        where  t2.papernum = t1.PaperNum
        and  t1.PartNum =@PartNumt
        and paperdate>@BeginDate
        and paperdate<@EndDate
        and ((t2.Equiptype=@EType) or (@EType=''))

        --計算平均值之前先按組別分類彙總

         select PartNum,EquipType,avg(uselife) as UseLife_Temp
         into #UseLife_Temp
         from #UseLife(nolock)
         group by PartNum,equipType

         --select * from #UseLife_Temp(nolock)

            declare @EquipType Varchar(max)
            DECLARE @UseLife_Type varchar(max)
            DECLARE curType CURSOR FOR  
                Select EquipType,UseLife_Temp From #UseLife_Temp(NoLock)             
            OPEN curType
            FETCH NEXT FROM curType INTO @EquipType,@uselife_Type
            WHILE @@FETCH_STATUS = 0
            BEGIN
                 

                 insert into #EMS_GetReferLife_Type(PartNum,MatName,ReferLife,EquipType)
                 SELECT PartNum,MatName,ReferLife,@EquipType
                 from #EMS_GetReferLife t1(nolock)
                 where t1.PartNum=@PartNumt

                 update t1
                 set t1.UseLife=@Uselife_Type
                 from #EMS_GetReferLife_Type t1(nolock)
                 where t1.PartNum=@PartNumt
                 and t1.EquipType=@EquipType

                  FETCH NEXT FROM curType 
              INTO  @EquipType,@uselife_Type
            END
            CLOSE curType
            DEALLOCATE curType


         delete from #UseLife
         drop table #UseLife_Temp

      FETCH NEXT FROM cur 
      INTO  @PartNumt
    END
    CLOSE cur
    DEALLOCATE cur
    


     select  PartNum as 物料編碼,MatName as 物料名稱,ReferLife as 參考日期,UseLife as 使用日期 ,EquipType as 組別
     from  #EMS_GetReferLife_Type(nolock)

truncate table #EMS_GetReferLife
truncate table #EMS_GetReferLife_Type
truncate table #UseLife

有想過用View取代臨時表，但速度上并沒有優化。

Answer 1

我給一個思路，你看一下是否可以緩解你的問題：

-- 就拿以下代碼來說
DECLARE cur CURSOR FOR   -- 這個位置的遊標我覺得必要性不大，如果返回值較多的話，太影響效率了
        Select PartNum From #EMS_GetReferLife(NoLock)      -- 可以將這個語句作為下面insert 語句中的條件       
    OPEN cur
    FETCH NEXT FROM cur INTO @PartNumt
    WHILE @@FETCH_STATUS = 0
    BEGIN
      -- 先註釋掉 
     --   insert into #UseLife(PartNum,UseLife,EquipType)
     --   select t1.partnum,t1.uselife,t2.EquipType
     --   from dbo.EMS_RequestPart t1(nolock) ,dbo.EMS_RequestMas t2(nolock) 
     --    where  t2.papernum = t1.PaperNum
     --    and  t1.PartNum =@PartNumt
     --    and ((paperdate>@BeginDate) or (@BeginDate=''))
     --    and ((paperdate<@EndDate) or (@EndDate=''))
     --    and ((t2.Equiptype=@EType) or (@EType=''))

--  改為：
        insert into #UseLife(PartNum,UseLife,EquipType)
        select t1.partnum,t1.uselife,t2.EquipType
        from dbo.EMS_RequestPart t1(nolock) ,dbo.EMS_RequestMas t2(nolock) 
         where   t2.papernum = t1.PaperNum
         and  exists(Select  1 From #EMS_GetReferLife(NoLock) t3 where t3.PartNum = t1.PartNum ) -- 取代  t1.PartNum =@PartNumt
         and ((paperdate>@BeginDate) or (@BeginDate=''))
         and ((paperdate<@EndDate) or (@EndDate=''))
         and ((t2.Equiptype=@EType) or (@EType=''))

-- 下同：
        insert into #UseLife(PartNum,UseLife,EquipType)
        select t1.PartNum,t1.uselife,t2.EquipType
        from dbo.EMS_RequestPart_main t1(nolock) ,dbo.EMS_maintainnoticeMas t2(nolock) 
        where  t2.papernum = t1.PaperNum
        and exists(Select  1 From #EMS_GetReferLife(NoLock) t3 where t3.PartNum = t1.PartNum ) -- 取代  t1.PartNum =@PartNumt 
        and paperdate>@BeginDate
        and paperdate<@EndDate
        and ((t2.Equiptype=@EType) or (@EType=''))

現在的情況是一個遊標又套了一層遊標，insert和update的次數無法控制，效率難以保證，將能夠避免的遊標全部去掉，然後儘量使用“批處理”，應該是可以提升不少執行效率的。

希望可以幫助到你。