Prestazioni SQL su LEFT OUTER JOIN vs NOT EXISTS

Se voglio trovare un insieme di voci nella tabella A ma non nella tabella B, posso usare LEFT OUTER JOIN o NOT EXISTS. Ho sentito che SQL Server è orientato verso ANSI e in alcuni casi LEFT OUTER JOINs sono molto più efficienti di NOT EXISTS. ANSI JOIN funzionerà meglio in questo caso? e gli operatori di join sono più efficienti di NOT EXISTS in generale su SQL Server?

Il collegamento di Joe è un buon punto di partenza. Quassnoi copre anche questo.

In generale, se i tuoi campi sono correttamente indicizzati, OPPURE se prevedi di filtrare più record (es. Avere un sacco di righe EXIST nella sottoquery) NOT EXISTS funzionerà meglio.

EXISTS e NOT EXISTS entrambi cortocircuiti – non appena un record corrisponde ai criteri è incluso o filtrato e l’ottimizzatore passa al record successivo.

LEFT JOIN si unirà a TUTTI I RECORD indipendentemente dal fatto che corrispondano o meno, quindi filtrerà tutti i record non corrispondenti. Se le tue tabelle sono grandi e / o hai più criteri di JOIN , questo può richiedere molte risorse.

Normalmente cerco di usare NOT EXISTS e EXISTS dove ansible. Per SQL Server, IN e NOT IN sono semanticamente equivalenti e potrebbero essere più facili da scrivere. Questi sono tra gli unici operatori che troverai in SQL Server che sono garantiti a corto circuito.

La migliore discussione che ho letto su questo argomento per SQL Server è qui .

Personalmente, penso che questo abbia un grande vecchio, “Dipende”. Ho visto casi in cui ogni metodo ha sovraperformato l’altro.

La soluzione migliore è testare entrambi e vedere quali sono le prestazioni migliori. Se è una situazione in cui i tavoli saranno sempre piccoli e le prestazioni non sono così cruciali, mi piacerebbe andare con quello che è il più chiaro per te (di solito NOT EXISTS per la maggior parte delle persone) e andare avanti.

Questo post sul blog fornisce esempi di vari modi ( NON IN , ESTERNO DOMESTICO , LEFT OUTER JOIN , EXCEPT e NOT EXISTS ) per ottenere gli stessi risultati e dimostra che Not Exists (Left Anti Semi Join) è la migliore opzione sia nella cache fredda che nella cache calda scenari.

Mi sono chiesto come possiamo usare l’indice sul tavolo da cui stiamo cancellando in questi casi che l’OP descrive.

Diciamo che abbiamo:

  table EMPLOYEE (emp_id int, name varchar) and table EMPLOYEE_LOCATION (emp_id int, loc_id int) 

Nel mio esempio del mondo reale le mie tabelle sono molto più ampie e contengono 1 milione di righe +, ho semplificato lo schema ad esempio lo scopo.

Se voglio eliminare le righe da EMPLOYEE_LOCATION che non hanno emp_id corrispondente in EMPLOYEE, posso ovviamente usare la tecnica esterna sinistra o NOT IN, ma mi stavo chiedendo …

Se entrambe le tabelle hanno indici con la colonna principale di emp_id, allora varrebbe la pena di usarle?

Forse potrei estrarre gli emp_id di EMPLOYEE, gli emp_id di EMPLOYEE_LOCATION in una tabella temporanea e recuperare gli emp_id dalle tabelle temporanee che voglio eliminare.

Potrei quindi girare intorno a questi emp_id’s e effettivamente usare l’indice in questo modo:

 loop for each emp_id X to delete -- (this would be a cursor) DELETE EMPLOYEE_LOCATION WHERE emp_id = X 

So che c’è un sovraccarico con il cursore, ma nel mio esempio reale mi occupo di tabelle enormi, quindi penso che l’uso esplicito dell’indice sia auspicabile.

Rispondi su dba.stackexchange

Un’eccezione ho notato che i NOT EXISTS sono superiori (comunque marginalmente) a LEFT JOIN ... WHERE IS NULL è quando si usano Server collegati .

Dall’esame dei piani di esecuzione, sembra che l’operatore NOT EXISTS venga eseguito in modo loop annidato. Per cui viene eseguito su una base per riga (che suppongo abbia senso).

Esempio di piano di esecuzione che dimostra questo comportamento: inserisci la descrizione dell'immagine qui