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

[CQP]

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

[CQP]

这个问题困扰我二三天了,要执行8秒
希望高手帮我看看能不能优化一下,在线等

[marco08]

關注

[CQP]

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

[Ivan_it]

晕

[Ivan_it]

mark

[CQP]

看样子没人帮我了:(

[CQP]

up

[lhxhappy]

up

[marco08]

你的存儲過程太誇張了