sql-server – 你可以使用COUNT DISTINCT和OVER子句吗？

我正在尝试提高以下查询的性能：

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

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!