Sprawdź istnienie za pomocą EXISTS, wyprzedzając COUNT! … Nie?

Często czytam, kiedy trzeba sprawdzić istnienie wiersza, zawsze powinno się to odbywać za pomocą ISTNIENIA zamiast LICZENIA.

Jednak w kilku ostatnich scenariuszach zmierzyłem poprawę wydajności przy użyciu liczenia.
Wzór wygląda następująco:

LEFT JOIN (
    SELECT
        someID
        , COUNT(*)
    FROM someTable
    GROUP BY someID
) AS Alias ON (
    Alias.someID = mainTable.ID
)

Nie jestem zaznajomiony z metodami informowania o tym, co się dzieje w „SQL Server”, więc zastanawiałem się, czy nie istniała jakaś luka w EXISTS, która doskonale nadała sens pomiarom, które zrobiłem (czy EXISTS może być RBAR ?!).

Czy masz jakieś wyjaśnienie tego zjawiska?

EDYTOWAĆ:

Oto pełny skrypt, który możesz uruchomić:

SET NOCOUNT ON
SET STATISTICS IO OFF

DECLARE @tmp1 TABLE (
    ID INT UNIQUE
)


DECLARE @tmp2 TABLE (
    ID INT
    , X INT IDENTITY
    , UNIQUE (ID, X)
)

; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
) 
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp1
SELECT n
FROM tally AS T1
WHERE n < 10000


; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
) 
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp2
SELECT T1.n
FROM tally AS T1
CROSS JOIN T AS T2
WHERE T1.n < 10000
AND T1.n % 3 <> 0
AND T2.n < 1 + T1.n % 15

PRINT '
COUNT Version:
'

WAITFOR DELAY '00:00:01'

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN n > 0 THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
LEFT JOIN (
    SELECT
        T2.ID
        , COUNT(*) AS n
    FROM @tmp2 AS T2
    GROUP BY T2.ID
) AS T2 ON (
    T2.ID = T1.ID
)
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF

PRINT '

EXISTS Version:'

WAITFOR DELAY '00:00:01'

SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN EXISTS (
        SELECT 1
        FROM @tmp2 AS T2
        WHERE T2.ID = T1.ID
    ) THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF 

Na SQL Server 2008R2 (siedem 64 bitów) otrzymuję ten wynik

COUNT Wersja:

Tabela „# 455F344D”. Liczba skanów 1, logiczne odczyty 8, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela '# 492FC531'. Liczba skanów 1, logiczne odczyty 30, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 0 ms, czas, który upłynął = 81 ms.

EXISTS Wersja:

Tabela „# 492FC531”. Liczba skanów 1, logiczne odczyty 96, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela '# 455F344D'. Liczba skanów 1, logiczne odczyty 8, fizyczne odczyty 0, odczytywanie z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty 0.

Czasy wykonania programu SQL Server:
czas procesora = 0 ms, czas, który upłynął = 76 ms.

Często czytam, kiedy trzeba sprawdzić istnienie wiersza, zawsze powinno się to odbywać za pomocą ISTNIENIA zamiast LICZENIA.

Bardzo rzadko zdarza się, aby wszystko było zawsze prawdziwe, szczególnie jeśli chodzi o bazy danych. Istnieje wiele sposobów wyrażania tego samego semantycznego w SQL. Jeśli istnieje użyteczna praktyczna zasada, może być pisanie zapytań przy użyciu najbardziej naturalnej dostępnej składni (i tak, to jest subiektywne) i rozważanie przepisania tylko wtedy, gdy otrzymany plan zapytań lub wydajność jest niedopuszczalna.

Jeśli chodzi o to, co warto, moje własne podejście do problemu polega na tym, że zapytania o istnienie są najbardziej naturalnie wyrażane za pomocą EXISTS. Z moich doświadczeń wynika, że EXISTS lepiej optymalizować niż alternatywa OUTER JOINodrzucania NULL. Używanie COUNT(*)i filtrowanie =0to kolejna alternatywa, która ma pewne wsparcie w optymalizatorze zapytań SQL Server, ale osobiście uważam, że jest to niewiarygodne w bardziej złożonych zapytaniach. W każdym razie EXISTSwydaje mi się to bardziej naturalne (dla mnie) niż jedna z tych alternatyw.

Zastanawiałem się, czy istnieje jakaś nieznana wada z ISTNIENIAMI, która doskonale nadała sens wykonanym pomiarom

Twój konkretny przykład jest interesujący, ponieważ podkreśla sposób, w jaki optymalizator radzi sobie z podzapytaniami w CASEwyrażeniach (a EXISTSzwłaszcza w testach).

Podkwerendy w wyrażeniach CASE

Rozważ następujące (całkowicie legalne) zapytanie:

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

W semantykaCASE to, że WHEN/ELSEklauzule są na ogół oceniana w celu tekstowej. W powyższym zapytaniu zwracanie błędu przez SQL Server byłoby błędne, jeśli ELSEpodzapytanie zwróciło więcej niż jeden wiersz, jeśli WHENklauzula była spełniona. Aby uszanować tę semantykę, optymalizator tworzy plan wykorzystujący predykaty przekazywania:

Wewnętrzna strona zagnieżdżonych połączeń pętli jest oceniana tylko wtedy, gdy predykat tranzytu zwraca wartość false. Ogólnym efektem jest to, że CASEwyrażenia są testowane w kolejności, a podzapytania są oceniane tylko wtedy, gdy poprzednie wyrażenie nie zostało spełnione.

Wyrażenia CASE z podzapytaniem EXISTS

W przypadku zastosowania CASEpodzapytania EXISTSlogiczny test istnienia jest implementowany jako połączenie częściowe, ale wiersze, które normalnie byłyby odrzucane przez połączenie częściowe, muszą zostać zachowane na wypadek, gdyby potrzebowała ich późniejsza klauzula. Rzędy przepływające przez ten szczególny rodzaj złączenia łączącego zdobywają flagę wskazującą, czy łączenie łączące znalazło dopasowanie, czy nie. Ta flaga jest znana jako kolumna sondy .

Szczegóły implementacji polegają na tym, że podkwerenda logiczna zostaje zastąpiona połączeniem skorelowanym („zastosuj”) z kolumną sondy. Praca jest wykonywana przez regułę uproszczenia w optymalizatorze zapytań o nazwie RemoveSubqInPrj(usuń podzapytanie w projekcji). Możemy zobaczyć szczegóły za pomocą flagi śledzenia 8606:

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Część drzewa wprowadzania pokazująca EXISTStest pokazano poniżej:

ScaOp_Exists 
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

Przekształca RemoveSubqInPrjsię to w strukturę kierowaną przez:

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

Jest to lewe połączenie częściowe z sondą opisaną wcześniej. Ta początkowa transformacja jest jedyną dostępną do tej pory w optymalizatorach zapytań SQL Server, a kompilacja zakończy się niepowodzeniem, jeśli ta transformacja zostanie wyłączona.

Jednym z możliwych kształtów planu wykonania dla tego zapytania jest bezpośrednia implementacja tej logicznej struktury:

Końcowy skalar obliczeniowy ocenia wynik CASEwyrażenia przy użyciu wartości kolumny sondy:

Podstawowy kształt drzewa planu zostaje zachowany, gdy optymalizacja uwzględnia inne typy połączeń fizycznych dla połączenia częściowego. Tylko łączenie przez scalenie obsługuje kolumnę sondy, więc łączenie pół-skrótowe, choć logicznie możliwe, nie jest brane pod uwagę:

Zwróć uwagę, że dane wyjściowe scalania zawierają wyrażenie oznaczone Expr1008(że nazwa jest taka sama jak poprzednio, to przypadek), chociaż żadna definicja tego planu nie pojawia się na żadnym operatorze. To znowu tylko kolumna sondy. Tak jak poprzednio, końcowy skalar obliczeniowy używa tej wartości sondy do oceny CASE.

Problem polega na tym, że optymalizator nie w pełni odkrywa alternatywy, które stają się opłacalne tylko przy łączeniu scalającym (lub mieszaniu). W planie zagnieżdżonych pętli nie ma żadnej korzyści ze sprawdzania, czy wiersze w wierszu T2pasują do zakresu na każdej iteracji. W przypadku planu scalania lub mieszania może to być przydatna optymalizacja.

Jeśli dodamy BETWEENdo T2zapytania pasujący predykat , wszystko co się stanie, to sprawdzenie zostanie wykonane dla każdego wiersza jako reszta na łączeniu częściowym scalania (trudne do wykrycia w planie wykonania, ale tam jest):

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

Mamy nadzieję, że BETWEENorzeczenie zostanie zamiast tego sprowadzone do T2poszukiwania. Zwykle optymalizator rozważałby to (nawet bez dodatkowego predykatu w zapytaniu). Rozpoznaje implikowane predykaty ( BETWEENon T1i predykat łączenia między T1i T2razem implikują BETWEENon T2) bez ich obecności w oryginalnym tekście zapytania. Niestety, wzór zastosowanej sondy oznacza, że ​​nie jest to badane.

Istnieją sposoby na napisanie zapytania w celu utworzenia poszukiwań dla obu danych wejściowych do połączenia półspołączenia. Jednym ze sposobów jest napisanie zapytania w dość nienaturalny sposób (pokonanie powodu, który ogólnie wolę EXISTS):

WITH T2 AS
(
    SELECT TOP (9223372036854775807) * 
    FROM #T2 AS T2 
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT 
    T1.ID, 
    DoesExist = 
        CASE 
            WHEN EXISTS 
            (
                SELECT * FROM T2 
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

Nie byłbym szczęśliwy, pisząc to zapytanie w środowisku produkcyjnym, aby pokazać, że pożądany kształt planu jest możliwy. Jeśli prawdziwe zapytanie, które musisz napisać, korzysta CASEw ten właśnie sposób, a wydajność spada, ponieważ po stronie próbnej połączenia częściowego łączenia nie występuje poszukiwanie, możesz rozważyć napisanie zapytania przy użyciu innej składni, która daje prawidłowe wyniki i bardziej wydajny plan realizacji.

Argument „COUNT (*) vs EXISTS” ma na celu sprawdzenie, czy rekord istnieje. Na przykład:

WHERE (SELECT COUNT(*) FROM Table WHERE ID=@ID)>0

vs

WHERE EXISTS(SELECT ID FROM Table WHERE ID=@ID)

Twój skrypt SQL nie używa COUNT(*)jako rekordu kontroli, dlatego nie powiedziałbym, że ma on zastosowanie w twoim scenariuszu.