存储过程优化,请优化高手进来帮我看看,我这段存储过程要扫行8秒,相当郁闷

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: CHENQP
-- Create date: 9:31 2006-11-13
-- Description: 用于取得初烟货位数据放入全局临时表##LocationsStorageReturnOrder
-- =============================================
ALTER PROCEDURE [dbo].[usp_TBManagerQuery_GetRawTLWarehouse]
AS
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##storInTemp' AND xtype='U')  
        DROP TABLE ##storInTemp
    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##storOutTemp' AND xtype='U')  
        DROP TABLE ##storOutTemp
    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##TempLocationIn' AND xtype='U')
        DROP TABLE ##TempLocationIn
     IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##TempLocationOut' AND xtype='U')
        DROP TABLE ##TempLocationOut
    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##LocationsMoveStorage' AND xtype='U')
        DROP TABLE ##LocationsMoveStorage
    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name='##LocationsStorageReturnOrder' AND xtype='U')
        DROP TABLE ##LocationsStorageReturnOrder    DECLARE @Select varchar(8000) declare @d1 datetime set @d1=getdate()

    -- 货位入库信息
 SELECT  
      VGoodsLocationC,
      Vtrancode,
  VStartPoint,
      V_OldGBGrade,
      V_GBGrade,
      SUM(IPackageCount) AS IPackageCount,
      SUM(NWeight) AS NWeight    
  INTO ##storInTemp                 
  FROM 
      V_InStorehouseInfo 
  WHERE VGoodsLocationC 
IN (SELECT VGoodsLocationC FROM V_InStorehouseInfo) 
or VGoodsLocationC in (SELECT VGoodsLocationC_In FROM V_StorMoveInfo)
  GROUP BY  
      VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade select [d1语句执行花费时间(毫秒)]=datediff(ms,@d1,getdate())  

declare @d2 datetime set @d2=getdate()
  
-- 加上移库移入数、插入冲账数据进行计算
-- Modify by:CHENQP
    INSERT INTO ##storInTemp 
        SELECT 
            VGoodsLocationC_In,
VTranCode,
V_ProducingArea,
V_OldGBGrade,
V_GBGrade,
SUM(IPackageCount) AS IPackageCount,
SUM(NWeight) AS NWeight
        FROM 
            V_StorMoveInfo
         WHERE 
V_CommitFlag = '已经提交' 
AND (VGoodsLocationC_In 
IN (SELECT VGoodsLocationC FROM ##storInTemp) 
OR VGoodsLocationC_In 
IN (SELECT VGoodsLocationC_In FROM V_StorMoveInfo))
    GROUP BY  
        VGoodsLocationC_In, VTranCode,V_ProducingArea,V_OldGBGrade,V_GBGrade UNION ALL 
SELECT 
            VGoodsLocationC,
            V_TranCode,
    V_ProducingArea,
            V_GBGradeOld,
            V_GBGrade,
            I_PackageCountBalance,
            N_WeightBalance
    FROM 
        V_StorStrikeBalanceInfo
    WHERE 
VGoodsLocationC 
IN (SELECT VGoodsLocationC FROM ##storInTemp) select [d2语句执行花费时间(毫秒)]=datediff(ms,@d2,getdate()) -- 将所有货位入库信息分组得出SUM
-- ##storInTemp 记录数 34492
declare @d8 datetime set @d8=getdate()
    SELECT 
        VGoodsLocationC,
        Vtrancode,
VStartPoint,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        ##TempLocationIn 
    FROM 
        ##storInTemp
    GROUP BY 
        VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade 
select [d8语句执行花费时间(毫秒)]=datediff(ms,@d8,getdate()) declare @d4 datetime set @d4=getdate()
    -- 货位出库信息
    SELECT 
        VGoodsLocationC,
        Vtrancode,
VGoodsLocation,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO
        ##storOutTemp
    FROM 
        V_OutStorehouseInfo
    WHERE 
        VGoodsLocationC IN (SELECT VGoodsLocationC FROM ##storInTemp)
    GROUP BY  
        VGoodsLocationC,Vtrancode, VGoodsLocation, V_OldGBGrade,V_GBGrade
select [d4语句执行花费时间(毫秒)]=datediff(ms,@d4,getdate()) declare @d9 datetime set @d9=getdate()
    -- 加上移库移出数
    INSERT INTO 
        ##storOutTemp (
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
        )
    SELECT 
        VGoodsLocationC_Out,
        VTranCode,
        V_ProducingArea,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    FROM 
        V_StorMoveInfo
    WHERE 
        VGoodsLocationC_Out 
IN (SELECT VGoodsLocationC FROM ##storInTemp)
        AND V_CommitFlag = '已经提交'
    GROUP BY  
        VGoodsLocationC_Out,Vtrancode,V_ProducingArea,V_OldGBGrade,V_GBGrade
select [d9语句执行花费时间(毫秒)]=datediff(ms,@d9,getdate())

declare @d10 datetime set @d10=getdate()
    -- 出库数据 + 移库移出数据
    SELECT 
        VGoodsLocationC,
        Vtrancode,
    VGoodsLocation,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        ##TempLocationOut
    FROM 
        ##storOutTemp
    GROUP BY  
        VGoodsLocationC,Vtrancode,VGoodsLocation,V_OldGBGrade,V_GBGrade
select [d10语句执行花费时间(毫秒)]=datediff(ms,@d10,getdate()) declare @d11 datetime set @d11=getdate()    UPDATE 
        ##TempLocationIn 
    SET 
        ##TempLocationIn.IPackageCount = ##TempLocationIn.IPackageCount - ##TempLocationOut.IPackageCount,
        ##TempLocationIn.NWeight = ##TempLocationIn.NWeight - ##TempLocationOut.NWeight 
    FROM 
        ##TempLocationOut 
    WHERE 
        ##TempLocationOut.[VGoodsLocationC] = ##TempLocationIn.[VGoodsLocationC] AND 
        ##TempLocationOut.[Vtrancode] = ##TempLocationIn.[Vtrancode]  AND
        ##TempLocationOut.[VGoodsLocation] = ##TempLocationIn.[VStartPoint]  AND
        ##TempLocationOut.[V_OldGBGrade] = ##TempLocationIn.[V_OldGBGrade]  AND
        ##TempLocationOut.[V_GBGrade] = ##TempLocationIn.[V_GBGrade]  
  
select [d11语句执行花费时间(毫秒)]=datediff(ms,@d11,getdate())
    ---------------------------------------------------------------------------------------------------
declare @d12 datetime set @d12=getdate()
    DELETE FROM ##TempLocationIn WHERE IPackageCount = 0 AND NWeight = 0
select [d12语句执行花费时间(毫秒)]=datediff(ms,@d12,getdate())     -- 获取发货地 年度 收购类型 入库日期 运单号 司磅员  
declare @d13 datetime set @d13=getdate()
    SELECT 
        A.*,
        B.DTobaccoDate,
        B.VSBY, 
        B.VReserveType,
        B.DInDate
    INTO 
        ##LocationsMoveStorage
    FROM 
        ##TempLocationIn A
    LEFT JOIN 
        T_StorRealIn B 
    ON 
        A.Vtrancode = B.Vtrancode 
    ORDER BY A.VGoodsLocationC
select [d13语句执行花费时间(毫秒)]=datediff(ms,@d13,getdate()) declare @d14 datetime set @d14=getdate()
      SELECT 
        identity(int,1,1) AS OrderID,
        VGoodsLocationC,
        V_GBGrade,
        VStartPoint,
        CONVERT(CHAR(4),DTobaccoDate,112) AS DTobaccoDate,
        VReserveType,
        VSBY,
        DInDate,
        VTranCode,
        V_OldGBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO    
        ##LocationsStorageReturnOrder 
    FROM 
        ##LocationsMoveStorage
    GROUP BY 
        VGoodsLocationC,V_GBGrade,VStartPoint,DTobaccoDate,VReserveType,VSBY,DInDate,VTranCode,V_OldGBGrade
select [d14语句执行花费时间(毫秒)]=datediff(ms,@d14,getdate())
--    select * from ##LocationsStorageReturnOrder -- 最终初烟货位库存结果集    DROP TABLE ##storOutTemp
    DROP TABLE ##TempLocationIn
    DROP TABLE ##TempLocationOut
    DROP TABLE ##LocationsMoveStorage

Answer 1

我的调试结果:
d1语句执行花费时间(毫秒)   236
d2语句执行花费时间(毫秒)   453
d8语句执行花费时间(毫秒)   1670
d4语句执行花费时间(毫秒)   533
d9语句执行花费时间(毫秒)   186
d10语句执行花费时间(毫秒)  1156
d11语句执行花费时间(毫秒)  2656
d12语句执行花费时间(毫秒)  216
d13语句执行花费时间(毫秒)  203
d14语句执行花费时间(毫秒)  876