Dlaczego podzapytanie używa równoległości, a sprzężenie nie?

Dlaczego serwer SQL korzysta z równoległości podczas uruchamiania tego zapytania, które korzysta z podzapytania, ale nie korzysta z łączenia? Wersja dołączenia działa szeregowo i jej ukończenie zajmuje około 30 razy.

Dołącz do wersji: ~ 30secs

Wersja podzapytania: <1 sekunda

EDYCJA: Xml wersje planu zapytań:

DOŁĄCZ wersja

Wersja SUBQUERY

Jak już wspomniano w komentarzach, wygląda na to, że musisz zaktualizować statystyki.

Szacowana liczba wierszy wychodzących z połączenia między locationi testrunsjest bardzo różna między dwoma planami.

Dołącz oszacowania planu: 1

Szacunkowy plan zapytania podrzędnego: 8748

Rzeczywista liczba wierszy wychodzących ze złączenia wynosi 14 276.

Oczywiście nie ma absolutnie żadnego intuicyjnego sensu, że wersja łączenia powinna oszacować, że 3 wiersze powinny pochodzić locationi wygenerować jeden połączony wiersz, podczas gdy zapytanie podrzędne szacuje, że jeden z tych wierszy wytworzy 8748 z tego samego złączenia, ale mimo to byłem w stanie odtworzyć to.

Wydaje się, że dzieje się tak, jeśli histogramy nie są przenoszone podczas tworzenia statystyk. Wersja łączenia zakłada pojedynczy wiersz. A pojedyncze szukanie równości w zapytaniu podrzędnym zakłada takie same oszacowane wiersze, jak szukanie równości względem nieznanej zmiennej.

Kardynalność testów jest 26244. Zakładając, że jest zapełniony trzema odrębnymi identyfikatorami lokalizacji, poniższe zapytanie szacuje, że 8,748wiersze zostaną zwrócone ( 26244/3)

declare @i int

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = @i

Biorąc pod uwagę, że tabela locationszawiera tylko 3 wiersze, łatwo (jeśli nie przyjmujemy kluczy obcych) stworzyć sytuację, w której statystyki są tworzone, a następnie dane są zmieniane w sposób, który dramatycznie wpływa na rzeczywistą liczbę zwracanych wierszy, ale jest niewystarczający, aby wyłącz automatyczną aktualizację statystyk i ponownie skompiluj próg.

Ponieważ SQL Server pobiera liczbę wierszy wychodzących z tego złączenia, tak źle, wszystkie inne szacunki wierszy w planie łączenia są bardzo niedoszacowane. Oprócz tego, że otrzymujesz plan szeregowy, zapytanie otrzymuje również niewystarczającą ilość pamięci, a połączenia sortowania i mieszania przeniosą się na tempdb.

Jeden z możliwych scenariuszy, który odtwarza wiersze rzeczywiste i szacunkowe pokazane w planie, znajduje się poniżej.

CREATE TABLE location
  (
     id       INT CONSTRAINT locationpk PRIMARY KEY,
     location VARCHAR(MAX) /*From the separate filter think you are using max?*/
  )

/*Temporary ids these will be updated later*/
INSERT INTO location
VALUES      (101, 'Coventry'),
            (102, 'Nottingham'),
            (103, 'Derby')

CREATE TABLE testruns
  (
     location_id INT
  )

CREATE CLUSTERED INDEX IX ON testruns(location_id)

/*Add in 26244 rows of data split over three ids*/
INSERT INTO testruns
SELECT TOP (5984) 1
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (5984) 2
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (14276) 3
FROM   master..spt_values v1, master..spt_values v2

/*Create statistics. The location_id histograms don't intersect at all*/
UPDATE STATISTICS location(locationpk) WITH FULLSCAN;    
UPDATE STATISTICS testruns(IX) WITH FULLSCAN;

/* UPDATE location.id. Three row update is below recompile threshold*/
UPDATE location
SET    id = id - 100

Następnie uruchomienie następujących zapytań daje tę samą rozbieżność szacunkową i faktyczną

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = (SELECT id
                         FROM   location
                         WHERE  location = 'Derby')

SELECT *
FROM   testruns AS tr
       JOIN location loc
         ON tr.location_id = loc.id
WHERE  loc.location = ( 'Derby' )