Czy możesz użyć COUNT DISTINCT z klauzulą ​​OVER?

Usiłuję poprawić wydajność następującego zapytania:

        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                            

Obecnie z moimi danymi testowymi zajmuje to około minuty. Mam ograniczoną ilość danych wejściowych do zmian w całej procedurze składowanej, w której znajduje się to zapytanie, ale prawdopodobnie mogę je zmusić do zmodyfikowania tego jednego zapytania. Lub dodaj indeks. Próbowałem dodać następujący indeks:

CREATE CLUSTERED INDEX ix_test ON #TempTable(AgentID, RuleId, GroupId, Passed)

I faktycznie podwoił czas potrzebny na zapytanie. Ten sam efekt uzyskuję dzięki indeksowi NON-CLUSTERED.

Próbowałem przepisać go w następujący sposób, bez efektu.

        WITH r AS (SELECT  AgentID,
                            RuleID,
                            COUNT(DISTINCT (GroupId)) Number
                    FROM    [#TempTable]
                    WHERE   Passed = 1
                    GROUP BY AgentID,
                            RuleID
            ) 
        UPDATE  [#TempTable]
        SET     Received = r.Number
        FROM    [#TempTable] 
        INNER JOIN r 
            ON r.RuleID = [#TempTable].RuleID AND
               r.AgentID = [#TempTable].AgentID                            

Następnie próbowałem użyć takiej funkcji okienkowania.

        UPDATE  [#TempTable]
        SET     Received = COUNT(DISTINCT (CASE WHEN Passed=1 THEN GroupId ELSE NULL END)) 
                    OVER (PARTITION BY AgentId, RuleId)
        FROM    [#TempTable] 

W tym momencie zacząłem otrzymywać błąd

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.

Mam więc dwa pytania. Po pierwsze, czy nie możesz wykonać ODLICZANIA LICZBY z klauzulą ​​OVER, czy po prostu napisałem go niepoprawnie? Po drugie, czy ktoś może zasugerować ulepszenie, którego jeszcze nie próbowałem? Do Twojej dyspozycji jest instancja SQL Server 2008 R2 Enterprise.

EDYCJA: Oto link do oryginalnego planu wykonania. Powinienem również zauważyć, że moim wielkim problemem jest to, że to zapytanie jest uruchamiane 30-50 razy.

https://onedrive.live.com/redir?resid=4C359AF42063BD98%21772

EDYCJA 2: Oto pełna pętla, w której znajduje się instrukcja, zgodnie z żądaniem w komentarzach. Sprawdzam z osobą, która pracuje z tym regularnie, co do celu pętli.

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,
                            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                            

        UPDATE  [#TempTable]
        SET     RulePassed = 1
        WHERE   TotalNeeded = Received              

        SELECT  @Counting = @Counting + 1              
    END

Ta konstrukcja nie jest obecnie obsługiwana w SQL Server. Moim zdaniem (i moim zdaniem) powinien zostać zaimplementowany w przyszłej wersji.

Po zastosowaniu jednego z obejść wymienionych w elemencie opinii zgłaszającym ten brak, zapytanie można przepisać jako:

WITH UpdateSet AS
(
    SELECT 
        AgentID, 
        RuleID, 
        Received, 
        Calc = SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY AgentID, RuleID) 
    FROM 
    (
        SELECT  
            AgentID,
            RuleID,
            Received,
            rn = ROW_NUMBER() OVER (
                PARTITION BY AgentID, RuleID, GroupID 
                ORDER BY GroupID)
        FROM    #TempTable
        WHERE   Passed = 1
    ) AS X
)
UPDATE UpdateSet
SET Received = Calc;

Wynikowy plan wykonania to:

Ma to tę zaletę, że pozwala uniknąć chętnej szpuli stołowej do ochrony Halloween (ze względu na samozłączenie), ale wprowadza rodzaj (dla okna) i często nieefektywną konstrukcję szpuli stołowej do obliczania i stosowania SUM OVER (PARTITION BY)wyniku do wszystkich rzędów w oknie. Jak to działa w praktyce, jest to ćwiczenie, które tylko Ty możesz wykonać.

Ogólne podejście jest trudne, aby zapewnić dobre wyniki. Rekurencyjne stosowanie aktualizacji (zwłaszcza opartych na samozłączeniu) do dużej struktury może być przydatne do debugowania, ale jest to przepis na niską wydajność. Powtarzające się duże skany, wycieki pamięci i problemy z Halloween to tylko niektóre z problemów. Indeksowanie i (więcej) tabel tymczasowych może pomóc, ale konieczna jest bardzo dokładna analiza, szczególnie jeśli indeks jest aktualizowany przez inne instrukcje w tym procesie (utrzymanie indeksów wpływa na wybory planu zapytań i dodaje operacje wejścia / wyjścia).

Ostatecznie rozwiązanie problemu leżałoby u podstaw interesujących prac doradczych, ale dla tej witryny jest to zbyt wiele. Mam nadzieję, że ta odpowiedź dotyczy jednak powierzchownych pytań.

Alternatywna interpretacja pierwotnego zapytania (powoduje aktualizację większej liczby wierszy):

WITH UpdateSet AS
(
    SELECT 
        AgentID, 
        RuleID, 
        Received, 
        Calc = SUM(CASE WHEN Passed = 1 AND rn = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY AgentID, RuleID) 
    FROM 
    (
        SELECT  
            AgentID,
            RuleID,
            Received,
            Passed,
            rn = ROW_NUMBER() OVER (
                PARTITION BY AgentID, RuleID, Passed, GroupID
                ORDER BY GroupID)
        FROM    #TempTable
    ) AS X
)
UPDATE UpdateSet
SET Received = Calc
WHERE Calc > 0;

Uwaga: wyeliminowanie sortowania (np. Poprzez udostępnienie indeksu) może przywrócić potrzebę Szybkiej Szpuli lub czegoś innego, aby zapewnić niezbędną Ochronę Halloween. Sortowanie jest operatorem blokującym, więc zapewnia pełną separację faz.

Nekromancja:

Relatywnie łatwo jest emulować liczbę odrębną dla partycji za pomocą DENSE_RANK:

;WITH baseTable AS
(
              SELECT 'RM1' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM1' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR3' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR2' AS ADR
)
,CTE AS
(
    SELECT RM, ADR, DENSE_RANK() OVER(PARTITION BY RM ORDER BY ADR) AS dr 
    FROM baseTable
)
SELECT
     RM
    ,ADR

    ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY ADR) AS cnt1 
    ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM) AS cnt2 
    -- Geht nicht / Doesn't work 
    --,COUNT(DISTINCT CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY CTE.ADR) AS cntDist
    ,MAX(CTE.dr) OVER (PARTITION BY CTE.RM ORDER BY CTE.RM) AS cntDistEmu 
FROM CTE