Zawijanie zapytania w JEŚLI ISTNIEJE powoduje, że jest on bardzo wolny

Mam poniższe zapytanie:

select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)

Powyższe zapytanie kończy się w ciągu trzech sekund.

Jeśli powyższe zapytanie zwraca jakąkolwiek wartość, chcemy, aby procedura przechowywana zakończyła się, więc przepisałem ją jak poniżej:

If Exists(
select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
)
Begin
Raiserror('Source missing',16,1)
Return
End

Jednak zajmuje to 10 minut.

Mogę przepisać powyższe zapytanie, jak poniżej, które również kończy się w mniej niż 3 sekundy:

  select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source
if @@rowcount >0
Begin
Raiserror('Source missing',16,1)
Return
End

Problem z powyższym przepisem polega na tym, że powyższe zapytanie jest częścią większej procedury składowanej i zwraca wiele zestawów wyników. W języku C # iterujemy każdy zestaw wyników i wykonujemy pewne przetwarzanie.

Powyższe zwraca pusty zestaw wyników, więc jeśli pójdę z tym podejściem, muszę zmienić mój C # i ponownie wdrożyć.

Więc moje pytanie brzmi:

dlaczego używanie tylko IF EXISTSzmienia plan, który zajmuje tak dużo czasu?

Poniżej znajdują się szczegółowe informacje, które mogą ci pomóc i dać mi znać, jeśli potrzebujesz jakichkolwiek szczegółów:

1. Utwórz skrypt tabeli i statystyk, aby uzyskać taki sam plan jak mój
2. Plan powolnej realizacji

3. Plan szybkiej realizacji

   Powolny plan za pomocą Brentozar Wklej plan
   Szybki plan za pomocą Brentozar Wklej plan

Uwaga: oba zapytania są takie same (przy użyciu parametrów), jedyna różnica polega na EXISTS(mogłem popełnić błędy podczas anonimizacji).

Skrypty tworzenia tabel znajdują się poniżej:

http://pastebin.com/CgSHeqXc - statystyki małego stołu
http://pastebin.com/GUu9KfpS - statystyki dużego stołu

Jak zostało wyjaśnione przez Pawła Białej w swoim blogu: Wewnątrz Optimizer Gole Row dogłębnie te EXISTSwprowadza cel rząd, który preferuje NESTED LOOPSlub MERGE JOINponadHASH MATCH

Jako ostatni przykład rozważmy, że logiczne połączenie częściowe (takie jak kwerenda wprowadzona w EXISTS) ma ten sam ogólny motyw: należy go zoptymalizować, aby szybko znaleźć pierwszy pasujący wiersz.

W twoim zapytaniu najwyraźniej dzieje się tak, gdy wprowadza się zagnieżdżone pętle i usuwa równoległość, co powoduje wolniejszy plan.

Prawdopodobnie będziesz musiał znaleźć sposób na przepisanie zapytania bez korzystania z polecenia NOT EXISTSz zapytania.

Możesz uniknąć przepisania zapytania za pomocą LEFT OUTER JOINi sprawdzenia, czy nie ma wiersza w małej tabeli, testującNULL

If EXISTS(
    SELECT databasename
    FROM somedb.dbo.bigtable l
    LEFT JOIN dbo.smalltable c ON c.source = l.source
    WHERE databasename = 'someval'
    AND source <> 'kt'
    AND c.source IS NULL
)

Prawdopodobnie możesz również użyć EXCEPTzapytania, w zależności od liczby pól, które musisz porównać w następujący sposób:

If EXISTS(
   SELECT source
   FROM somedb.dbo.bigtable l
   WHERE databasename = 'someval'
   AND source <> 'kt'

   EXCEPT

   SELECT source
   FROM dbo.smalltable
)

Pamiętaj, że Aaron Bertrand ma post na blogu, podający powody, dla których woli NIE ISTNIEĆ, które powinieneś przeczytać, aby sprawdzić, czy inne podejścia działają lepiej, i być świadomym potencjalnych problemów z poprawnością w przypadku wartości NULL.

Powiązane pytania i odpowiedzi: JEŚLI ISTNIEJE, trwa dłużej niż osadzona instrukcja select

Musisz przepisać zapytanie, używając jawnych połączeń i określić, jakiej operacji łączenia chcesz użyć (pętla, skrót lub scalanie) w ten sposób.

If not exists(
    select databasename 
    from somedb.dbo.bigtable l
    inner hash join dbo.smalltable c 
        on c.source = l.source
where databasename ='someval' and source  <>'kt')
begin
    Raiserror('Source missing',16,1)
    Return
end

Podczas korzystania z EXISTS lub NOT EXISTS SQL Server wygenerował plan zapytań z operacją NESTED LOOP, zakładając, że powinien on przechodzić przez wszystkie wiersze w zestawie jeden po drugim, szukając pierwszego wiersza, aby spełnić warunek. Użycie HASH JOIN przyspieszy.

Natknąłem się na ten sam problem, udało mi się obejść, unikając używania „ISTNIEJĄCEGO” i używając funkcji „COUNT ()” i instrukcji „JEŚLI ... ELSE”.

Na przykład spróbuj wykonać następujące czynności:

IF
(
    SELECT
        COUNT(l.databasename) + 1 AS databasename
    FROM somedb.dbo.bigtable AS l

    WHERE   l.databasename ='someval'
        AND l.[source]  <> 'kt'
        AND NOT EXISTS(SELECT 1 FROM dbo.smalltable AS c WHERE c.[source]=l.[source])
) > 1 --Acts like EXISTS
BEGIN
    RAISERROR('Source missing', 16, 1)
RETURN
END

Powodem dodawania „+ 1” do liczby jest to, że mogę użyć „> 1” w warunku JEŻELI, użycie „> 0” lub „<> 0” spowoduje, że zapytanie użyje zagnieżdżonych pętli zamiast skrótu Mecz. Nie zastanawiałem się, dlaczego tak się dzieje, byłoby interesujące dowiedzieć się, dlaczego.

Mam nadzieję, że to pomaga!