INNER JOIN vs LEFT JOIN prestazioni in SQL Server

Ho creato un comando SQL che usa INNER JOIN per 9 tabelle, comunque questo comando richiede molto tempo (più di cinque minuti). Quindi la mia gente mi suggerisce di cambiare INNER JOIN in LEFT JOIN perché la performance di LEFT JOIN è migliore, a prima vista, nonostante ciò che so. Dopo che ho cambiato, la velocità della query è notevolmente migliorata.

Vorrei sapere perché LEFT JOIN è più veloce di INNER JOIN?

Il mio comando SQL appare come segue: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D e così via

Aggiornamento: questo è breve del mio schema.

 FROM sidisaleshdrmly a -- NOT HAVE PK AND FK INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK ON a.CompanyCd = b.CompanyCd AND a.SPRNo = b.SPRNo AND a.SuffixNo = b.SuffixNo AND a.dnno = b.dnno INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine ON a.CompanyCd = h.CompanyCd AND a.sprno = h.AcctSPRNo INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix ON c.CompanyCd = h.CompanyCd AND c.FSlipNo = h.FSlipNo AND c.FSlipSuffix = h.FSlipSuffix INNER JOIN coMappingExpParty d -- NO PK AND FK ON c.CompanyCd = d.CompanyCd AND c.CountryCd = d.CountryCd INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd ON b.CompanyCd = e.CompanyCd AND b.ProductSalesCd = e.ProductSalesCd LEFT JOIN coUOM i -- PK = UOMId ON h.UOMId = i.UOMId INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd ON a.CompanyCd = j.CompanyCd AND b.BFStatus = j.BFStatus AND b.ProductSalesCd = j.ProductSalesCd INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd ON e.ProductGroup1Cd = g1.ProductGroup1Cd INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd ON e.ProductGroup1Cd = g2.ProductGroup1Cd 

A LEFT JOIN è assolutamente più veloce di un INNER JOIN . In effetti, è più lento; per definizione, un join esterno ( LEFT JOIN o RIGHT JOIN ) deve fare tutto il lavoro di un INNER JOIN più il lavoro extra di null-estendere i risultati. Ci si aspetta inoltre che restituisca più righe, aumentando ulteriormente il tempo di esecuzione totale semplicemente a causa delle dimensioni maggiori del set di risultati.

(E anche se un LEFT JOIN fosse più veloce in situazioni specifiche a causa di una confluenza di fattori difficile da immaginare, non è funzionalmente equivalente a un INNER JOIN , quindi non puoi semplicemente sostituire tutti i casi di uno con l’altro!)

Molto probabilmente i tuoi problemi di prestazioni si trovano altrove, come non avere una chiave candidata o una chiave straniera indicizzata correttamente. 9 tavoli è un bel po ‘di adesione, quindi il rallentamento potrebbe essere praticamente ovunque. Se pubblichi il tuo schema, potremmo essere in grado di fornire maggiori dettagli.


Modificare:

Riflettendo ulteriormente su questo, potrei pensare a una circostanza in cui un LEFT JOIN potrebbe essere più veloce di un INNER JOIN , e cioè quando:

  • Alcuni dei tavoli sono molto piccoli (ad esempio, sotto le 10 righe);
  • Le tabelle non hanno indici sufficienti per coprire la query.

Considera questo esempio:

 CREATE TABLE #Test1 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Test1 (ID, Name) VALUES (1, 'One') INSERT #Test1 (ID, Name) VALUES (2, 'Two') INSERT #Test1 (ID, Name) VALUES (3, 'Three') INSERT #Test1 (ID, Name) VALUES (4, 'Four') INSERT #Test1 (ID, Name) VALUES (5, 'Five') CREATE TABLE #Test2 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Test2 (ID, Name) VALUES (1, 'One') INSERT #Test2 (ID, Name) VALUES (2, 'Two') INSERT #Test2 (ID, Name) VALUES (3, 'Three') INSERT #Test2 (ID, Name) VALUES (4, 'Four') INSERT #Test2 (ID, Name) VALUES (5, 'Five') SELECT * FROM #Test1 t1 INNER JOIN #Test2 t2 ON t2.Name = t1.Name SELECT * FROM #Test1 t1 LEFT JOIN #Test2 t2 ON t2.Name = t1.Name DROP TABLE #Test1 DROP TABLE #Test2 

Se lo esegui e vedi il piano di esecuzione, vedrai che la query INNER JOIN costa di più rispetto a LEFT JOIN , perché soddisfa i due criteri sopra riportati. È perché SQL Server vuole eseguire una corrispondenza hash per il INNER JOIN , ma fa i cicli nidificati per il LEFT JOIN ; il primo è normalmente molto più veloce, ma poiché il numero di righe è così piccolo e non c’è un indice da utilizzare, l’operazione di hashing risulta essere la parte più costosa della query.

Puoi vedere lo stesso effetto scrivendo un programma nel tuo linguaggio di programmazione preferito per eseguire un gran numero di ricerche su una lista con 5 elementi, contro una tabella hash con 5 elementi. A causa delle dimensioni, la versione della tabella hash è in realtà più lenta. Ma aumentalo a 50 elementi, o 5000 elementi, e la versione lista rallenta a passo d’uomo, perché è O (N) contro O (1) per la tabella hash.

Ma cambia questa query per essere nella colonna ID invece di Name e vedrai una storia molto diversa. In questo caso, esegue cicli nidificati per entrambe le query, ma la versione INNER JOIN è in grado di sostituire una delle scansioni di indice cluster con una ricerca, il che significa che sarà letteralmente un ordine di grandezza più veloce con un numero elevato di righe.

Quindi la conclusione è più o meno quella di cui ho parlato sopra diversi paragrafi; questo è quasi certamente un problema di indicizzazione o di indicizzazione, eventualmente combinato con uno o più tavoli molto piccoli. Quelle sono le uniche circostanze in cui SQL Server potrebbe a volte scegliere un piano di esecuzione peggiore per un INNER JOIN di un LEFT JOIN .

C’è uno scenario importante che può portare a un join esterno più veloce di un join interno che non è stato ancora discusso.

Quando si utilizza un join esterno, l’ottimizzatore è sempre libero di eliminare la tabella unita esterna dal piano di esecuzione se le colonne di join sono il PK della tabella esterna e nessuna delle colonne viene selezionata dalla tabella esterna. Ad esempio SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY e B.KEY è il PK per B. Sia Oracle (credo che stavo usando la release 10) e Sql Server (ho usato 2008 R2) prune tabella B dal piano di esecuzione.

Lo stesso non è necessariamente vero per un join interno: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY può o non può richiedere B nel piano di esecuzione a seconda dei vincoli esistenti.

Se A.KEY è una chiave esterna nullable che fa riferimento a B.KEY, l’ottimizzatore non può rilasciare B dal piano perché deve confermare che esiste una riga B per ogni riga A.

Se A.KEY è una chiave esterna obbligatoria che fa riferimento a B.KEY, l’ottimizzatore è libero di rilasciare B dal piano perché i vincoli garantiscono l’esistenza della riga. Ma solo perché l’ottimizzatore può rilasciare la tabella dal piano, non significa che lo farà. SQL Server 2008 R2 NON elimina B dal piano. Oracle 10 fa cadere B dal piano. In questo caso è facile vedere come il join esterno eseguirà il join interno su SQL Server.

Questo è un esempio banale e non pratico per una query autonoma. Perché aderire a un tavolo se non è necessario?

Ma questa potrebbe essere una considerazione progettuale molto importante quando si progettano le viste. Spesso viene creata una vista “fai-tutto” che unisce tutto ciò che un utente potrebbe aver bisogno di relazionarsi con un tavolo centrale. (Soprattutto se ci sono utenti ingenui che fanno query ad-hoc che non capiscono il modello relazionale) La vista può includere tutte le colonne pertinenti da molte tabelle. Ma gli utenti finali potrebbero solo accedere alle colonne da un sottoinsieme delle tabelle all’interno della vista. Se le tabelle sono unite con join esterni, l’ottimizzatore può (e fa) eliminare le tabelle non necessarie dal piano.

È fondamentale assicurarsi che la vista che utilizza i join esterni fornisca i risultati corretti. Come ha detto Aaronaught, non puoi sostituire ciecamente OUTER JOIN per INNER JOIN e aspettarti gli stessi risultati. Ma ci sono momentjs in cui può essere utile per motivi di prestazioni quando si usano le viste.

Un’ultima nota: non ho verificato l’impatto sulle prestazioni alla luce di quanto sopra, ma in teoria sembra che si dovrebbe essere in grado di sostituire in modo sicuro un INNER JOIN con un OUTER JOIN se si aggiunge anche la condizione IS NOT NULL alla clausola where.

Se tutto funziona come dovrebbe, non dovrebbe, MA sappiamo tutti che non funziona come dovrebbe, specialmente quando si tratta di Query Optimizer, cache del piano di query e statistiche.

Innanzitutto suggerirei di ribuild l’indice e le statistiche, quindi di svuotare la cache del piano di query solo per assicurarmi che non stia rovinando tutto. Tuttavia ho avuto problemi anche quando è stato fatto.

Ho riscontrato alcuni casi in cui un join sinistro è stato più veloce di un join interno.

Il motivo sottostante è questo: se hai due tabelle e ti unisci a una colonna con un indice (su entrambe le tabelle). Il join interno produrrà lo stesso risultato, indipendentemente dal fatto che si esegua un loop sulle voci dell’indice sulla tabella uno e corrisponda all’indice sulla tabella due, come se si stesse facendo l’opposto: scorrere le voci nell’indice sulla tabella due e confrontarle con l’indice nella prima tabella. Il problema è quando si hanno statistiche fuorvianti, Query Optimizer userà le statistiche dell’indice per trovare la tabella con le voci meno corrispondenti (in base agli altri criteri). Se hai due tabelle con 1 milione ciascuna, nella tabella 1 hai 10 righe corrispondenti e nella tabella due hai 100000 righe corrispondenti. Il modo migliore sarebbe quello di fare una scansione dell’indice sul tavolo uno e corrispondente 10 volte nella tabella due. Il contrario sarebbe una scansione dell’indice che scorre su 100000 righe e tenta di corrispondere a 100000 volte e solo 10 successi. Pertanto, se le statistiche non sono corrette, l’ottimizzatore potrebbe scegliere la tabella e l’indice errati su cui eseguire il loopover.

Se l’ottimizzatore sceglie di ottimizzare il join sinistro nell’ordine in cui è scritto, funzionerà meglio del join interno.

MA, l’ottimizzatore può anche ottimizzare un join sinistro in modo sub-ottimale come un join semi sinistro. Per fare in modo che scelga quello che vuoi, puoi usare il suggerimento forza dell’ordine.

Prova entrambe le query (quella con join interno e sinistro) con OPTION (FORCE ORDER) alla fine e pubblica i risultati. OPTION (FORCE ORDER) è un suggerimento di query che forza l’ottimizzatore a creare il piano di esecuzione con l’ordine di join fornito nella query.

Se INNER JOIN inizia ad essere veloce come LEFT JOIN , è perché:

  • In una query composta interamente da INNER JOIN s, l’ordine di join non ha importanza. Questo dà la libertà per Query Optimizer di ordinare i join come meglio crede, quindi il problema potrebbe dipendere dall’ottimizzatore.
  • Con LEFT JOIN , non è il caso, perché la modifica dell’ordine di join altera i risultati della query. Ciò significa che il motore deve seguire l’ordine di join fornito sulla query, che potrebbe essere migliore di quello ottimizzato.

Non so se questo risponde alla tua domanda, ma una volta ero in un progetto che conteneva query molto complesse che facevano calcoli, che incasinavano completamente l’ottimizzatore. Abbiamo avuto casi in cui un FORCE ORDER avrebbe ridotto il tempo di esecuzione di una query da 5 minuti a 10 secondi.

Hanno fatto un certo numero di confronti tra i giunti esterni esterni e interni e non sono stato in grado di trovare una differenza consistente. Ci sono molte variabili. Sto lavorando su un database di reporting con migliaia di tabelle, molte con un gran numero di campi, molte modifiche nel tempo (versioni del fornitore e stream di lavoro locale). Non è ansible creare tutte le combinazioni di indici di copertura per soddisfare le esigenze di una così ampia varietà di query e gestire i dati storici. Hanno visto le query interne uccidere le prestazioni del server perché due grandi tabelle (da milioni a decine di milioni di righe) sono interne, sia estraendo un numero elevato di campi che senza indice di copertura.

Il problema più grande però, non sembra più interessante nelle discussioni precedenti. Forse il tuo database è ben progettato con trigger e un’elaborazione delle transazioni ben progettata per garantire buoni dati. Il mio spesso ha valori NULL in cui non sono previsti. Sì, le definizioni della tabella potrebbero imporre no-Null ma questa non è un’opzione nel mio ambiente.

Quindi la domanda è … progettate la vostra query solo per la velocità, una priorità più alta per l’elaborazione delle transazioni che esegue lo stesso codice migliaia di volte al minuto. O vai per l’accuratezza che fornirà un join esterno sinistro. Ricorda che i join interni devono trovare corrispondenze su entrambi i lati, quindi un NULL inatteso non solo rimuoverà i dati dalle due tabelle ma probabilmente intere righe di informazioni. E succede così bene, nessun messaggio di errore.

Puoi essere molto veloce come ottenere il 90% dei dati necessari e non scoprire che i join interni hanno rimosso silenziosamente le informazioni. A volte i join interiori possono essere più veloci, ma non credo che nessuno possa fare quella supposizione a meno che non abbiano rivisto il piano di esecuzione. La velocità è importante, ma la precisione è più importante.

È più probabile che i problemi relativi alle prestazioni siano dovuti al numero di join che stai facendo e al fatto che le colonne su cui stai partecipando abbiano indici o meno.

Nel peggiore dei casi si potrebbero facilmente eseguire 9 scansioni di tabelle complete per ogni join.

I join esterni possono offrire prestazioni superiori se utilizzati nelle visualizzazioni.

Supponiamo che tu abbia una query che coinvolge una vista e tale vista è composta da 10 tabelle unite insieme. Supponiamo che la tua query accada solo usando colonne da 3 di quei 10 tavoli.

Se queste 10 tabelle sono state unite internamente, allora Query Optimizer dovrebbe unirle tutte anche se la query non ha bisogno di 7 su 10 delle tabelle. Questo perché gli inner join stessi possono filtrare i dati, rendendoli essenziali per il calcolo.

Se invece queste 10 tabelle erano state unificate in esterno , allora il Query Optimizer si sarebbe unito solo a quelli che erano necessari: 3 su 10 di essi in questo caso. Questo perché i join stessi non filtrano più i dati e quindi i join non utilizzati possono essere saltati.

Fonte: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poo-little-misunderstood-views/

Ho trovato qualcosa di interessante nel server SQL quando si verifica se i join interni sono più veloci dei join di sinistra.

Se non si includono gli elementi della tabella unita sinistra, nell’istruzione select, il join sinistro sarà più veloce della stessa query con inner join.

Se si include la tabella unita sinistra nell’istruzione select, il join interno con la stessa query era uguale o più veloce del join sinistro.