SQL łączy z podzapytaniami SQL (wydajność)?

Chciałbym wiedzieć, czy mam zapytanie typu join podobne do tego -

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

i podzapytanie podobne do tego -

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

Kiedy rozważam wydajność, które z dwóch zapytań byłoby szybsze i dlaczego ?

Czy jest też czas, kiedy powinienem preferować jedno nad drugim?

Przepraszam, jeśli jest to zbyt trywialne i pytane wcześniej, ale jestem zdezorientowany. Byłoby również wspaniale, gdybyście mogli zasugerować mi narzędzia, których powinienem użyć do pomiaru wydajności dwóch zapytań. Wielkie dzięki!

SPODZIEWAŁEM SIĘ, że pierwsze zapytanie będzie szybsze, głównie dlatego, że masz równoważność i jawne JOIN. Z mojego doświadczenia INwynika, że ​​jest to bardzo powolny operator, ponieważ SQL zwykle ocenia go jako serię WHEREklauzul oddzielonych znakiem „LUB” ( WHERE x=Y OR x=Z OR...).

Podobnie jak w przypadku ALL THINGS SQL, przebieg może się różnić. Szybkość będzie w dużym stopniu zależała od indeksów (czy masz indeksy na obu kolumnach ID? To bardzo pomoże ...), między innymi.

Jedynym PRAWDZIWYM sposobem na stwierdzenie ze 100% pewnością, który jest szybszy, jest włączenie śledzenia wydajności (szczególnie przydatne są statystyki IO) i uruchomienie obu. Pamiętaj, aby wyczyścić pamięć podręczną między biegami!

Cóż, uważam, że jest to pytanie „Stare, ale złote”. Odpowiedź brzmi: „To zależy!”. Przedstawienia są tak delikatnym tematem, że byłoby zbyt głupio powiedzieć: „Nigdy nie używaj podzapytań, zawsze łącz”. W poniższych linkach znajdziesz kilka podstawowych sprawdzonych metod, które okazały się bardzo pomocne:

• Optymalizacja podzapytań
• Optymalizacja podzapytań za pomocą transformacji Semijoin
• Przepisywanie podzapytań jako łączenia

Mam stół z 50000 elementów, wynik, którego szukałem to 739 elementów.

Na początku moje zapytanie brzmiało:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

a wykonanie zajęło 7,9 sekundy.

W końcu moje pytanie brzmi:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

i zajęło to 0,0256s

Dobry SQL, dobry.

Zacznij przeglądać plany wykonania, aby zobaczyć różnice w sposobie ich interpretacji przez serwer SQl. Możesz także użyć Profilera, aby faktycznie wielokrotnie uruchamiać zapytania i uzyskać różnicę.

Nie spodziewałbym się, że będą one tak strasznie różne, gdzie można uzyskać rzeczywisty, duży wzrost wydajności, używając złączeń zamiast podzapytań, gdy używasz skorelowanych podzapytań.

EXISTS jest często lepsze niż którekolwiek z tych dwóch, a kiedy mówisz o złączeniach lewostronnych, w których chcesz, aby wszystkie rekordy nie znajdowały się w tabeli łączenia po lewej stronie, to NIE ISTNIEJE jest często znacznie lepszym wyborem.

Wydajność zależy od ilości danych wykonywanych na ...

Jeśli jest mniej danych, około 20k. JOIN działa lepiej.

Jeśli dane są bardziej zbliżone do 100k +, wtedy IN działa lepiej.

Jeśli nie potrzebujesz danych z drugiej tabeli, IN jest dobre, ale zawsze lepiej jest wybrać EXISTS.

Wszystkie te kryteria przetestowałem i tabele mają odpowiednie indeksy.

Wydajność powinna być taka sama; o wiele ważniejsze jest zastosowanie odpowiednich indeksów i grupowania w tabelach (istnieje kilka dobrych zasobów na ten temat).

(Zredagowano w celu odzwierciedlenia zaktualizowanego pytania)

Te dwa zapytania mogą nie być semantycznie równoważne. Jeśli pracownik pracuje dla więcej niż jednego działu (jest to możliwe w przedsiębiorstwie, dla którego pracuję; co prawda oznaczałoby to, że twoja tabela nie jest w pełni znormalizowana), to pierwsze zapytanie zwróci zduplikowane wiersze, podczas gdy drugie zapytanie nie. Aby zapytania były równoważne w tym przypadku, DISTINCTsłowo kluczowe musiałoby zostać dodane do SELECTklauzuli, co może mieć wpływ na wydajność.

Zauważ, że istnieje praktyczna zasada projektowania, która mówi, że tabela powinna modelować jednostkę / klasę lub związek między jednostkami / klasami, ale nie oba. Dlatego proponuję stworzyć trzecią tabelę, powiedzmy OrgChart, do modelowania relacji między pracownikami a działami.

Wiem, że to stary post, ale myślę, że jest to bardzo ważny temat, szczególnie w dzisiejszych czasach, gdy mamy ponad 10 milionów rekordów i mówimy o terabajtach danych.

Zwrócę również uwagę na następujące spostrzeżenia. Mam około 45 milionów rekordów w mojej tabeli ([dane]) i około 300 rekordów w mojej tabeli [cats]. Mam obszerne indeksowanie dla wszystkich zapytań, o których będę mówić.

Rozważ przykład 1:

UPDATE d set category = c.categoryname
FROM [data] d
JOIN [cats] c on c.id = d.catid

w porównaniu z przykładem 2:

UPDATE d set category = (SELECT TOP(1) c.categoryname FROM [cats] c where c.id = d.catid)
FROM [data] d

Przykład 1 trwał około 23 minut. Przykład 2 zajął około 5 minut.

Więc doszedłbym do wniosku, że pod-zapytanie w tym przypadku jest znacznie szybsze. Oczywiście pamiętaj, że używam dysków SSD M.2 obsługujących I / O przy 1 GB / s (to bajty, a nie bity), więc moje indeksy też są naprawdę szybkie. Więc może to wpłynąć również na prędkość w twoich okolicznościach

Jeśli jest to jednorazowe czyszczenie danych, prawdopodobnie najlepiej zostawić je i zakończyć. Używam TOP (10000) i widzę, ile czasu to zajmuje, i mnożę przez liczbę rekordów, zanim trafię na duże zapytanie.

Jeśli optymalizujesz produkcyjne bazy danych, zdecydowanie sugerowałbym wstępne przetwarzanie danych, tj. Użycie wyzwalaczy lub pośrednika zadań do asynchronicznej aktualizacji rekordów, aby dostęp w czasie rzeczywistym pobierał dane statyczne.

Możesz skorzystać z planu wyjaśniania, aby uzyskać obiektywną odpowiedź.

W przypadku Twojego problemu filtr Exists prawdopodobnie działałby najszybciej.