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是一个阻塞运算符,因此它提供了完全相位分离.