100分：大抛卖啦 走过路过不要错过，一个sp问题
有个SP执行要化十多分钟，代码如下：
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N '[dbo].[usp_StatisticsTotalPurchase] ') AND OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
DROP PROC [dbo].[usp_StatisticsTotalPurchase]
GO
CREATE PROC [dbo].[usp_StatisticsTotalPurchase]
@Reference INT,
@BeginDate DATETIME,
@EndDate DATETIME,
@ErrorNumber INT OUTPUT,
@ErrorInfo NVARCHAR(200) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @Profit MONEY
, @RefStandardCost MONEY
, @PartID NVARCHAR(60)
, @Mode NVARCHAR(10)
, @Date DATETIME
, @YearMonth NVARCHAR(6);
--得到利润率
SELECT @Profit = Profit
FROM dbo.Profit
WHERE Active = 1;
IF @Profit IS NULL
BEGIN
SELECT @ErrorNumber = 110, @ErrorInfo = N 'Profit data not set. ';
SET NOCOUNT OFF;
RETURN CAST(0 AS BIT);
END;
--确定统计的部件
DECLARE @Parts TABLE
(
PartID NVARCHAR(60) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY,
RefStdCost MONEY
);
IF LEN(CAST(@Reference AS NVARCHAR)) = 6
INSERT INTO @Parts (PartID)
SELECT DISTINCT PartID
FROM dbo.StandardCost
WHERE IsPurchased = 1
AND YEAR([Date]) * 100 + MONTH([Date]) <= @Reference;
ELSE
INSERT INTO @Parts (PartID)
SELECT DISTINCT PartID
FROM dbo.StandardCost
WHERE IsPurchased = 1
AND YEAR([Date]) <= @Reference;
DELETE @Parts
FROM @Parts p
INNER JOIN
(
SELECT PartID
FROM dbo.StandardCost
GROUP BY PartID
HAVING SUM(Price) = 0
) c
ON p.PartID = c.PartID;
DELETE @Parts
FROM @Parts p
WHERE NOT EXISTS
(
SELECT 1
FROM iERP72tech.dbo.POM pom
INNER JOIN iERP72tech.dbo.POI poi
ON poi.POI_PurchOrderID = pom.POM_PurchOrderID
INNER JOIN iERP72tech.dbo.POD pod
ON pod.POD_PurchOrderID = pom.POM_PurchOrderID
AND pod.POD_POLineNbr = poi.POI_POLineNbr
WHERE poi.POI_LineNbrTypeCode = N 'Item '
AND pod.POD_POUnitPrice > 0
AND pom.POM_PurchOrderDate > = @BeginDate
AND pom.POM_PurchOrderDate < DATEADD(DAY, 1, @EndDate)
AND poi.POI_ItemID COLLATE Chinese_PRC_CI_AS = p.PartID
);
IF NOT EXISTS (SELECT 1 FROM @Parts)
BEGIN
SELECT @ErrorNumber = 110, @ErrorInfo = N 'Standard cost in reference period not exists. ';
SET NOCOUNT OFF;
RETURN CAST(0 AS BIT);
END;
--得到参照标准成本
IF LEN(CAST(@Reference AS NVARCHAR)) = 6
UPDATE @Parts
SET RefStdCost = sc.Price
FROM @Parts p
INNER JOIN
(
SELECT PartID, AVG(Price) Price
FROM dbo.StandardCost
WHERE YEAR([Date]) * 100 + MONTH([Date]) = @Reference
GROUP BY PartID
) sc
ON p.PartID = sc.PartID;
ELSE
UPDATE @Parts
SET RefStdCost = sc.Price
FROM @Parts p
INNER JOIN
(
SELECT PartID, AVG(Price) Price
FROM dbo.StandardCost sc
WHERE YEAR([Date]) = @Reference
GROUP BY PartID
) sc
ON p.PartID = sc.PartID;
IF LEN(CAST(@Reference AS NVARCHAR)) = 6
UPDATE @Parts
SET RefStdCost = c.Price
FROM @Parts p
INNER JOIN
(
SELECT PartID, MAX([Date]) MaxDate
FROM dbo.StandardCost
WHERE YEAR([Date]) * 100 + MONTH([Date]) < @Reference
GROUP BY PartID
) s
ON s.PartID = p.PartID
INNER JOIN dbo.StandardCost c
ON c.PartID = s.PartID AND c.[Date] = s.MaxDate
WHERE p.RefStdCost IS NULL;
ELSE
UPDATE @Parts
SET RefStdCost = c.Price
FROM @Parts p
INNER JOIN
(
SELECT PartID, MAX([Date]) MaxDate
FROM dbo.StandardCost
WHERE YEAR([Date]) < @Reference
GROUP BY PartID
) s
ON s.PartID = p.PartID
INNER JOIN dbo.StandardCost c
ON c.PartID = s.PartID AND c.[Date] = s.MaxDate
WHERE p.RefStdCost IS NULL;
SELECT @RefStandardCost = AVG(RefStdCost)
FROM @Parts;
--PRINT '@RefStandardCost = ' + CAST(@RefStandardCost AS NVARCHAR(20));
[解决办法]
............好长~~先路过再看看
[解决办法]
自查询1秒出来很正常，Update时间比子查询长，看看索引之类的是不是可以优化
[解决办法]
/*****************************************
如果注释下面三句话，则2-3秒就可以执行完成
加上下面一句，则十几分都出不来
*****************************************/
--存在采购价格的部件取平均值
UPDATE @Price
SET ActualPrice = t.ActualPrice
FROM @Price p
INNER JOIN
(
/***********************
单独执行只要1秒钟
************************/
=========没看明白,你说的三行是哪三行
[解决办法]
我想你的时间肯定是花在@Price表变量和后面很长一段查询的INNER JOIN上了
我想你能不能先把后面是用表变量和一个结果集做INNER JOIN我没试过
你看能不能把表变量换成一个临时表试试，也许会好一点
[解决办法]
/*****************************************
如果注释下面三句话，则2-3秒就可以执行完成
加上下面一句，则十几分都出不来
*****************************************/
循环里面有频繁的UPDATE操作，自然慢。
可以想办法把循环里的UPDATE语句改成使用动态SQL语句，再指定循环多少次执行一次EXECUTE(动态SQL语句)
[解决办法]
学习
[解决办法]
up
[解决办法]
的确挺长的。
[解决办法]
路过学习
[解决办法]
你的代码也太长了，要看千年啊
[解决办法]
可以试试把UPDATE后的子查询，存到临时表里，然后再用临时表来更新数据
[解决办法]
因为你的UPDATE现在是一个相关子查询来进行更新，是一个循环每次更新都要执行一便子查询，每次1秒*表中的数据，自然很慢
