SQL łączy Vs SQL Subqueries (wydajność)?

Wydaje mi się, że to pytanie" stare, ale złote". Odpowiedź brzmi: "to zależy!". Spektakle są tak delikatnym tematem, że byłoby zbyt głupio powiedzieć: "nigdy nie używaj subqueries, zawsze Dołącz". W poniższych linkach znajdziesz kilka podstawowych najlepszych praktyk, które uznałem za bardzo pomocne: Tutaj 1 Tutaj 2 Tutaj 3

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

Moje zapytanie na początku było to:

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 
)

Moje zapytanie w końcu jest takie:

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
)

And it taken 0.0256 s

Dobry SQL, dobry.

Zacznij patrzeć na plany wykonawcze, aby zobaczyć różnice w tym, jak serwer SQl je zinterpretuje. Możesz również użyć profilera do wielokrotnego uruchamiania zapytań i uzyskiwania różnic.

Nie spodziewałbym się, że będą tak strasznie różne, gdzie można uzyskać prawdziwy, duży wzrost wydajności przy użyciu łączy zamiast subqueries jest kiedy używasz skorelowanych subqueries.

EXISTS is czesto better than either of these two and when you are talking left joins where chcesz, aby wszystkie rekordy Nie w lewej tabeli łączenia, to nie istnieje jest często znacznie lepszym wyborem.

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

Uwaga Istnieje zasada projektowania, która Stany tabela powinna modelować podmiot/klasę lub związek między podmiotami / klasami, ale nie oba. Dlatego proponuję stworzyć trzecią tabelę, powiedzmy OrgChart, aby modelować relacje między pracownikami a działami.

Wydajność zależy od ilości danych, na których wykonujesz...

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

Jeśli dane są bardziej jak 100k+ to w działa lepiej.

Jeśli nie potrzebujesz danych z drugiej tabeli, IN jest dobre, ale jest alwys lepiej przejść do EXISTS.

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

Wydajność powinna być taka sama; o wiele ważniejsze jest, aby odpowiednie indeksy i klastry były stosowane na Twoich tabelach(istnieją dobre zasoby na ten temat).

(edytowane w celu odzwierciedlenia zaktualizowanego pytania)

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

Będę również ważyć z następujących obserwacji. Mam około 45m rekordów w tabeli ([dane]), a około 300 rekordów w tabeli [koty]. Mam obszerne indeksowanie dla wszystkich zapytań, o których mam mówić.

Rozważ Przykład 1:

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

Kontra Przykład 2:

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

Przykład 1 około 23 min do uruchomienia. Przykład 2 zajął około 5 minut.

Wnioskuję więc, że zapytanie podrzędne w tym przypadku jest znacznie szybsze. Oczywiście należy pamiętać, że używam dysków SSD M. 2 zdolnych do i / O @ 1Gb / s (to bajty, a nie bity), więc moje indeksy są bardzo szybkie. Może to również wpłynąć na prędkość w twojej sytuacji

Jeśli jest to jednorazowe czyszczenie danych, prawdopodobnie najlepiej po prostu zostawić go uruchomić i zakończyć. Używam TOP(10000) i zobaczyć jak długo to trwa i pomnożyć przez liczbę rekordów przed Trafiłem na wielkie pytanie.

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

Możesz użyć planu wyjaśnienia, aby uzyskać obiektywną odpowiedź.

Dla Twojego problemu, Filtr Exists prawdopodobnie wykonałby najszybszy.