记一次优化存储过程

USE [SFMM39]
GO
/****** Object:  StoredProcedure [dbo].[sp_Rpt_SplitHistoryReport]    Script Date: 2022/2/10 22:38:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Rpt_SplitHistoryReport]
    -- Add the parameters for the stored procedure here
    @SystemID int,
    @GRN nvarchar(16)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @n int
    declare @GRN1 nvarchar(16)
    declare @SubGRN nvarchar(16)
    declare @TopGRN nvarchar(16)
    
    --1. Get TOP GRN
    set @n=1000
    set @GRN1 =@GRN 
    
    if NOT exists (select OrgGRN from Pull_SplitGRN where SubGRN =@GRN1 and SystemID =@SystemID)
    begin
        SET @TopGRN=@GRN
    end
    else
    begin
        while @n>0
        begin        
            select @TopGRN=OrgGRN from Pull_SplitGRN where SubGRN =@GRN1 and SystemID =@SystemID 
            
            if @@ROWCOUNT >0
            begin
                set @GRN1 = @TopGRN 
            end
            else
            begin
                set @TopGRN =@GRN1  
                break
            end
            set @n=@n-1
        end
    end
    
    declare @PartNumber nvarchar(50)
    declare @Vendor nvarchar(30)
    declare @DateCode nvarchar(30)
    declare @LotCode nvarchar(30)
    
    select @PartNumber =PartNumber,@Vendor =Vendor,@DateCode =SAPDateCode ,@LotCode =SAPLotCode 
     from Pull_OrgGRN where OrgGRN =@TopGRN and SystemID =@SystemID 
    
    
    
    --2. Create report temp table
    create table #report(Level int,
    GRN nvarchar(16),
    PartNumber nvarchar(50),
    Vendor nvarchar(50),
    DateCode nvarchar(50),
    LotCode nvarchar(50),
    OriginalQTY INT,
    RemainQTY INT,
    SubReelQTY INT,
    SubGRNQTY INT,
    SplitDate datetime,
    UserName nvarchar(30),
    seq int,
    GRNLevel int)
    
    create table #GRNList(GRN nvarchar(16))
    create table #GRNList2(GRN nvarchar(16))
    select top 0 * into #SplitGRN from Pull_SplitGRN
    alter table #SplitGRN add nLevel int
    CREATE INDEX index1 ON #SplitGRN (OrgGRN,OrgGRNQTY)
    --3. Get multi level split GRN list by TOP GRN
    declare @m int
    declare @level int
    set @m=1000
    set @level =1
    
    insert into #GRNList values(@TopGRN) --multi level GRN LIST
    insert into #GRNList2 values(@TopGRN)  --only one level GRN list
    
    while @m>0
    begin
        if exists(select * from Pull_SplitGRN s
        inner join #GRNList2 gl on s.OrgGRN =gl.GRN)
        
        begin
              
            delete #GRNList2 
            insert into #GRNList2 select s.SubGRN from Pull_SplitGRN s
        inner join #GRNList gl on s.OrgGRN =gl.GRN 
            insert into #SplitGRN select s.*,@level as nLevel from Pull_SplitGRN s
        inner join #GRNList gl on s.OrgGRN =gl.GRN
            delete #GRNList 
            insert into #GRNList select * from #GRNList2 
            set @m=@m-1
            set @level =@level +1   
        end
        else
        begin
            break
        end

    end
    
    
    --4. Format final report
    declare @m_OrgGRN nvarchar(16)
    declare @m_SubGRN nvarchar(16)
    declare @m_OrgGRNQTY INT
    declare @m_SubGRNQTY INT
    declare @m_UserID INT
    declare @m_Lastupdated nvarchar(30)
    
    
    declare cSplitGRN CURSOR STATIC for
    select distinct OrgGRN,OrgGRNQTY from #SplitGRN 
    
    open cSplitGRN
    --fetch next from cSplitGRN into @m_OrgGRN,@m_SubGRN,@m_OrgGRNQTY,@m_SubGRNQTY,@m_UserID,@m_Lastupdated
    fetch next from cSplitGRN into @m_OrgGRN,@m_OrgGRNQTY
    
    while @@FETCH_STATUS =0
    begin
            --1. Insert OrgGRN
        insert into #report 
        select distinct 0 as [Level],s.OrgGRN as GRN,o.PartNumber ,o.Vendor,o.SAPDateCode as DateCode,o.SAPLotCode as LotCode,
        s.OrgGRNQTY as OriginalQTY,0 as RemainQTY,0 as SubReelQTY,0 as SubGRNQTY,NULL AS SplitDate, '' as UserName,
        0 as seq,s.nLevel-1 as grnlevel
        from #SplitGRN s with (index(index1))
        inner join Pull_OrgGRN o on s.OrgGRN=o.OrgGRN 
        inner join ITMIS_Users u on u.UserID =s.userid
        where s.OrgGRN=@m_OrgGRN and s.OrgGRNQTY=@m_OrgGRNQTY 

          --2. Update OrgGRN's remainQTY,SubReelQTY and splitdate
        update #report set RemainQTY =@m_OrgGRNQTY -(select SUM(SubGRNQTY) from Pull_SplitGRN where OrgGRN =@m_OrgGRN and OrgGRNQTY =@m_OrgGRNQTY),
        SubReelQTY =(select COUNT(*) from Pull_SplitGRN with (index(U_index)) where OrgGRN =@m_OrgGRN and OrgGRNQTY =@m_OrgGRNQTY),
        SplitDate =dateadd(s,-1,(select top 1 Lastupdated from Pull_SplitGRN where OrgGRN =@m_OrgGRN and OrgGRNQTY =@m_OrgGRNQTY order by Lastupdated))
        where GRN =@m_OrgGRN and OriginalQTY =@m_OrgGRNQTY 

            --3. Insert SubGRN
        insert into #report 
        select distinct 0 as [Level],s.SubGRN as GRN,o.PartNumber ,o.Vendor,o.SAPDateCode as DateCode,o.SAPLotCode as LotCode,
        0 as OriginalQTY,0 as RemainQTY,0 as SubReelQTY,s.SubGRNQTY as SubGRNQTY,s.Lastupdated AS SplitDate, u.WindowsUserName as UserName,
        0 as seq,s.nLevel as grnlevel
        from #SplitGRN s  with (index(index1))
        inner join Pull_OrgGRN o on s.OrgGRN=o.OrgGRN 
        inner join ITMIS_Users u on u.UserID =s.userid
        where s.OrgGRN=@m_OrgGRN and s.OrgGRNQTY =@m_OrgGRNQTY
        
        --fetch next from cSplitGRN into @m_OrgGRN,@m_SubGRN,@m_OrgGRNQTY,@m_SubGRNQTY,@m_UserID,@m_Lastupdated
        fetch next from cSplitGRN into @m_OrgGRN,@m_OrgGRNQTY
    end
    
    close cSplitGRN
    deallocate cSplitGRN
    
    --Update seq
    update r set r.seq=r2.seq
    from #report r
    inner join (select SplitDate, RANK() over (order by SplitDate) as seq from #report) r2
    on r.SplitDate =r2.SplitDate
    --Update SplitDate to null for orgGRN
    update #report set SplitDate='' where UserName =''
    
    select GRNLevel,GRN,PartNumber ,Vendor ,DateCode ,LotCode ,OriginalQTY ,
    RemainQTY ,SubReelQTY,SubGRNQTY ,SplitDate ,UserName from #report order by seq
    
    
    
    drop table #report
    drop table #GRNList
    drop table #GRNList2
    drop table #SplitGRN
    
    
END

创建复合索引

CREATE NONCLUSTERED INDEX U_index ON [Pull_SplitGRN]
([OrgGRN],[OrgGRNQTY])

临时表也要创建复合索引。

 

查看一个表的索引

EXEC Sp_helpindex tablename