UPDATE [#TempTable]
SET Received = r.Number
FROM [#TempTable]
INNER JOIN (SELECT AgentID,RuleID,COUNT(DISTINCT (GroupId)) Number
FROM [#TempTable]
WHERE Passed = 1
GROUP BY AgentID,RuleID
) r ON r.RuleID = [#TempTable].RuleID AND
r.AgentID = [#TempTable].AgentID
目前我的测试数据大约需要一分钟.我对此查询所在的所有存储过程的更改输入数量有限,但我可以让他们修改这一个查询.或者添加索引.我尝试添加以下索引：
CREATE CLUSTERED INDEX ix_test ON #TempTable(AgentID,RuleId,GroupId,Passed)
它实际上使查询所花费的时间增加了一倍.我使用NON-CLUSTERED索引获得相同的效果.
我尝试重写它如下,没有任何效果.
WITH r AS (SELECT AgentID,RuleID
)
UPDATE [#TempTable]
SET Received = r.Number
FROM [#TempTable]
INNER JOIN r
ON r.RuleID = [#TempTable].RuleID AND
r.AgentID = [#TempTable].AgentID
接下来我尝试使用这样的窗口函数.
UPDATE [#TempTable]
SET Received = COUNT(DISTINCT (CASE WHEN Passed=1 THEN GroupId ELSE NULL END))
OVER (PARTITION BY AgentId,RuleId)
FROM [#TempTable]
此时我开始收到错误
Msg 102,Level 15,State 1,Line 2 Incorrect Syntax near 'distinct'.
所以我有两个问题.首先,你不能用OVER子句做COUNT DISTINCT,或者我只是错误地写了吗？第二,任何人都可以建议我还没有尝试过改进吗？仅供参考,这是一个sql Server 2008 R2 Enterprise实例.
编辑：这是原始执行计划的链接.我还应该注意,我的大问题是这个查询正在运行30-50次.
https://onedrive.live.com/redir?resid=4C359AF42063BD98%21772
EDIT2：这是语句所在的完整循环,如评论中所要求的那样.关于循环的目的,我正在与定期工作的人核实.
DECLARE @Counting INT
SELECT @Counting = 1
-- BEGIN: Cascading Rule check --
WHILE @Counting <= 30
BEGIN
UPDATE w1
SET Passed = 1
FROM [#TempTable] w1,[#TempTable] w3
WHERE w3.AgentID = w1.AgentID AND
w3.RuleID = w1.CascadeRuleID AND
w3.RulePassed = 1 AND
w1.Passed = 0 AND
w1.NotFlag = 0
UPDATE w1
SET Passed = 1
FROM [#TempTable] w1,[#TempTable] w3
WHERE w3.AgentID = w1.AgentID AND
w3.RuleID = w1.CascadeRuleID AND
w3.RulePassed = 0 AND
w1.Passed = 0 AND
w1.NotFlag = 1
UPDATE [#TempTable]
SET Received = r.Number
FROM [#TempTable]
INNER JOIN (SELECT AgentID,COUNT(DISTINCT (GroupID)) Number
FROM [#TempTable]
WHERE Passed = 1
GROUP BY AgentID,RuleID
) r ON r.RuleID = [#TempTable].RuleID AND
r.AgentID = [#TempTable].AgentID
UPDATE [#TempTable]
SET RulePassed = 1
WHERE TotalNeeded = Received
SELECT @Counting = @Counting + 1
END
解决方法
应用feedback item中列出的报告此缺陷的解决方法之一,您的查询可以重写为：
WITH UpdateSet AS
(
SELECT
AgentID,Received,Calc = SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (
PARTITION BY AgentID,RuleID)
FROM
(
SELECT
AgentID,rn = ROW_NUMBER() OVER (
PARTITION BY AgentID,GroupID
ORDER BY GroupID)
FROM #TempTable
WHERE Passed = 1
) AS X
)
UPDATE UpdateSet
SET Received = Calc;
由此产生的执行计划是：
这样做的好处是避免了Halloween Protection的Eager Table Spool(由于自连接),但它引入了一种排序(对于窗口)和一种通常效率低下的Lazy Table Spool构造来计算和应用SUM OVER(PARTITION BY) )结果到窗口中的所有行.它在实践中的表现只是你可以进行的练习.
总体方法难以表现良好.将更新(特别是基于自连接的更新)递归地应用于大型结构可能对调试很有用,但它是性能不佳的一个方法.重复的大型扫描,内存溢出和万圣节问题只是其中的一些问题.索引和(更多)临时表可以提供帮助,但是需要非常仔细的分析,尤其是如果索引由流程中的其他语句更新(维护索引会影响查询计划选择并添加I / O).
最终,解决潜在的问题会带来有趣的咨询工作,但这对于这个网站来说太过分了.我希望这个答案可以解决表面问题.
原始查询的替代解释(导致更新更多行)：
WITH UpdateSet AS
(
SELECT
AgentID,Calc = SUM(CASE WHEN Passed = 1 AND rn = 1 THEN 1 ELSE 0 END) OVER (
PARTITION BY AgentID,Passed,GroupID
ORDER BY GroupID)
FROM #TempTable
) AS X
)
UPDATE UpdateSet
SET Received = Calc
WHERE Calc > 0;
注意：消除排序(例如通过提供索引)可能会重新引入对Eager Spool或其他东西的需求,以提供必要的万圣节保护. Sort是一个阻塞运算符,因此它提供了完全相位分离.
