SQL Server IN a EXISTS Performance

Ciekawi mnie, które z poniższych byłoby bardziej wydajne?

Zawsze byłem trochę ostrożny przy używaniu, INponieważ uważam, że SQL Server zamienia zestaw wyników w dużą IFinstrukcję. W przypadku dużego zestawu wyników może to spowodować słabą wydajność. W przypadku małych zestawów wyników nie jestem pewien, czy któryś z nich jest lepszy. W przypadku dużych zestawów wyników nie EXISTSbyłby bardziej wydajny?

WHERE EXISTS (SELECT * FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

vs.

WHERE bx.BoxID IN (SELECT BoxID FROM Base WHERE [Rank = 2])

EXISTS będzie szybszy, ponieważ po znalezieniu trafienia silnik przestanie wyglądać, ponieważ stan się sprawdził.

W programie INbędzie zbierać wszystkie wyniki z zapytania podrzędnego przed dalszym przetwarzaniem.

Przyjęta odpowiedź jest krótkowzroczna, a pytanie jest nieco luźne:

1) Ani wyraźnie nie wspomnieć, czy indeks pokrywający występuje po lewej, prawej czy po obu stronach.

2) Żadne z nich nie uwzględnia rozmiaru zestawu wejściowego po lewej stronie i zestawu po prawej stronie.
(Pytanie tylko wspomina o ogólnym dużym zestawie wyników ).

Uważam, że optymalizator jest wystarczająco inteligentny, aby dokonać konwersji między „w” a „istnieje”, gdy występuje znaczna różnica kosztów ze względu na (1) i (2), w przeciwnym razie może być użyty jako wskazówka (np. Istnieje, aby zachęcić do korzystania z indeks do przeszukiwania po prawej stronie).

Oba formularze można przekonwertować tak, aby łączyć formularze wewnętrznie, odwrócić kolejność łączenia i uruchamiać jako pętlę, mieszanie lub scalanie - na podstawie szacowanej liczby wierszy (po lewej i prawej) oraz indeksowania po lewej, prawej lub po obu stronach.

Przeprowadziłem kilka testów na SQL Server 2005 i 2008 i na obu EXISTS i IN wróciłem z dokładnie tym samym rzeczywistym planem wykonania, jak stwierdzili inni. Optymalizator jest optymalny. :)

Coś, o czym należy pamiętać, EXISTS, IN i JOIN może czasami zwracać różne wyniki, jeśli zapytanie nie jest odpowiednio sformułowane: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210 .aspx

Jest tu wiele mylących odpowiedzi, w tym ta bardzo pozytywnie oceniana (chociaż nie wierzę, że ich operacje oznaczały krzywdę). Krótka odpowiedź brzmi: te są takie same.

W języku (T-) SQL jest wiele słów kluczowych, ale ostatecznie jedyną rzeczą, która naprawdę dzieje się na sprzęcie, są operacje widoczne w planie zapytań wykonania.

Relacyjna (teoria matematyczna) operacja, którą wykonujemy, gdy wywołujemy [NOT] INi [NOT] EXISTSjest półzłączeniem (anty-złączeniem, gdy używamy NOT). To nie przypadek, że odpowiednie operacje na serwerze sql mają taką samą nazwę . Nie ma operacji, o której wspomina INani EXISTSgdziekolwiek - tylko (anty-) półłącza. W związku z tym nie ma możliwości, aby logicznie równoważny INlub EXISTSwybór mógł wpłynąć na wydajność, ponieważ istnieje jeden i jedyny sposób wykonania operacji (anty) półłączenia, aby uzyskać wyniki .

Przykład:

Zapytanie 1 ( plan )

select * from dt where dt.customer in (select c.code from customer c where c.active=0)

Zapytanie 2 ( plan )

select * from dt where exists (select 1 from customer c where c.code=dt.customer and c.active=0)

Poszedłbym z EXISTS over IN, zobacz poniższy link:

SQL Server: JOIN vs IN vs EXISTS - logiczna różnica

Istnieje powszechne błędne przekonanie, że IN zachowuje się tak samo jak EXISTS lub JOIN pod względem zwracanych wyników. To po prostu nieprawda.

IN: Zwraca wartość true, jeśli określona wartość pasuje do dowolnej wartości w podzapytaniu lub na liście.

Exists: zwraca wartość true, jeśli podzapytanie zawiera jakiekolwiek wiersze.

Dołącz: Łączy 2 zestawy wyników w kolumnie łączącej.

Kredyt na blogu: https://stackoverflow.com/users/31345/mladen-prajdic

Plany wykonania będą zazwyczaj identyczne w takich przypadkach, ale dopóki nie zobaczysz, jak optymalizator bierze pod uwagę wszystkie inne aspekty indeksów itp., Naprawdę nigdy się nie dowiesz.

Tak więc IN nie jest tym samym, co EXISTS, ani nie wytworzy tego samego planu wykonania.

Zwykle EXISTS jest używane w podzapytaniu skorelowanym, co oznacza, że ​​DOŁĄCZENIE zapytania wewnętrznego ISTNIEJE do zapytania zewnętrznego. Spowoduje to dodanie większej liczby kroków w celu uzyskania wyniku, ponieważ musisz rozwiązać zewnętrzne sprzężenia zapytania, a wewnętrzne sprzężenia zapytań, a następnie dopasuj ich klauzule where, aby połączyć oba.

Zwykle IN jest używane bez korelowania zapytania wewnętrznego z zapytaniem zewnętrznym i można to rozwiązać tylko w jednym kroku (w najlepszym przypadku).

Rozważ to:

1. Jeśli użyjesz IN, a wewnętrzny wynik zapytania to miliony wierszy odrębnych wartości, prawdopodobnie wykona on SLOWER niż EXISTS, biorąc pod uwagę, że zapytanie EXISTS jest wydajne (ma odpowiednie indeksy do połączenia z zewnętrznym zapytaniem).

2. Jeśli używasz EXISTS, a łączenie z zapytaniem zewnętrznym jest złożone (wykonanie zajmuje więcej czasu, brak odpowiednich indeksów), spowolni to zapytanie o liczbę wierszy w tabeli zewnętrznej, czasami szacowany czas zakończenia może być wyrażony w dniach. Jeśli liczba wierszy jest akceptowalna dla danego sprzętu lub liczność danych jest poprawna (na przykład mniej wartości DISTINCT w dużym zestawie danych) IN może działać szybciej niż EXISTS.

3. Wszystkie powyższe zostaną odnotowane, gdy w każdej tabeli znajduje się spora liczba wierszy (przez sprawiedliwe mam na myśli coś, co przekracza progi przetwarzania procesora i / lub pamięci RAM dla buforowania).

A więc ODPOWIEDŹ: TO ZALEŻY. Możesz napisać złożone zapytanie wewnątrz IN lub EXISTS, ale z reguły powinieneś spróbować użyć IN z ograniczonym zestawem odrębnych wartości i EXISTS, gdy masz wiele wierszy z wieloma różnymi wartościami.

Sztuczka polega na ograniczeniu liczby skanowanych wierszy.

Pozdrowienia,

MarianoC

Aby zoptymalizować EXISTS, bądź bardzo dosłowny; coś po prostu musi tam być, ale w rzeczywistości nie potrzebujesz żadnych danych zwracanych z skorelowanego zapytania podrzędnego. Po prostu oceniasz warunek logiczny.

Więc:

WHERE EXISTS (SELECT TOP 1 1 FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

Ponieważ skorelowane zapytanie podrzędne to RBAR, pierwsze trafienie wyniku powoduje spełnienie warunku i nie jest ono dalej przetwarzane.

Z góry mojej głowy i nie gwarantujemy, że będzie poprawna: wierzę, że w tym przypadku sekunda będzie szybsza.

1. W pierwszym podzapytanie skorelowane prawdopodobnie spowoduje uruchomienie podzapytania dla każdego wiersza.
2. W drugim przykładzie podzapytanie powinno być uruchamiane tylko raz, ponieważ nie jest skorelowane.
3. W drugim przykładzie INnastąpi zwarcie, gdy tylko znajdzie dopasowanie.