Criteri di filtro SQL nei criteri di join o clausola where che è più efficiente

Ho una query relativamente semplice che unisce due tabelle. I criteri “Dove” possono essere espressi nei criteri di join o come clausola where. Mi chiedo quale sia più efficiente.

La query consiste nel trovare le vendite massime per un venditore dall’inizio del tempo fino alla promozione.

Caso 1

select salesman.salesmanid, max(sales.quantity) from salesman inner join sales on salesman.salesmanid =sales.salesmanid and sales.salesdate < salesman.promotiondate group by salesman.salesmanid 

Caso 2

 select salesman.salesmanid, max(sales.quantity) from salesman inner join sales on salesman.salesmanid =sales.salesmanid where sales.salesdate < salesman.promotiondate group by salesman.salesmanid 

Nota: il caso 1 manca del tutto una clausola where

RDBMS è Sql Server 2005

MODIFICA Se la seconda parte dei criteri di join o la clausola where era sales.salesdate <qualche data fissa, quindi non è in effetti alcun criterio per unire le due tabelle, questo cambia la risposta.

Non utilizzerei le prestazioni come fattore decisivo qui – e onestamente, non penso che ci sia alcuna differenza di prestazioni misurabili tra questi due casi, davvero.

Userei sempre il caso n. 2: perché? Perché secondo me, dovresti solo inserire i criteri effettivi che stabiliscono il JOIN tra le due tabelle nella clausola JOIN – tutto il resto appartiene alla clausola WHERE.

È solo questione di mantenere le cose pulite e mettere le cose a cui appartengono, IMO.

Ovviamente, ci sono casi con LEFT OUTER JOINs in cui il posizionamento dei criteri fa la differenza in termini di risultati che vengono restituiti – ovviamente questi casi sarebbero esclusi dalla mia raccomandazione.

Marc

È ansible eseguire lo stimatore del piano di esecuzione e il profiler SQL per vedere come si sovrappongono.

Tuttavia, sono semanticamente uguali sotto il cappuccio secondo questo MVP di SQL Server:

http://www.eggheadcafe.com/conversation.aspx?messageid=29145383&threadid=29145379

Preferisco avere dei criteri codificati nel join. Rende l’SQL molto più leggibile e portatile.

Leggibilità: è ansible vedere esattamente quali dati si otterranno perché tutti i criteri della tabella sono scritti proprio lì nel join. In grandi dichiarazioni, i criteri possono essere sepolti all’interno di altre 50 espressioni ed è facilmente trascurato.

Portabilità: puoi semplicemente copiare un blocco dalla clausola FROM e incollarlo da qualche altra parte. Ciò fornisce i join e tutti i criteri che è necessario seguire. Se si utilizzano sempre questi criteri quando si uniscono queste due tabelle, inserirle nel join è la più logica.

Per esempio:

 FROM table1 t1 JOIN table2 t2_ABC ON t1.c1 = t2_ABC.c1 AND t2_ABC.c2 = 'ABC' 

Se è necessario ottenere una seconda colonna dalla tabella 2, basta copiare quel blocco in Blocco note, cercare / ripetere “ABC” e presto e tutto il nuovo blocco di codice pronto per essere incollato nuovamente.

Aggiuntivo: È anche più facile cambiare tra un join interno ed esterno senza doversi preoccupare di alcun criterio che potrebbe essere presente nella clausola WHERE.

Riservo la clausola WHERE esclusivamente per i criteri di run-time ove ansible.

Per quanto riguarda l’efficienza: se ti riferisci alla velocità di esecuzione, allora come tutti gli altri hanno affermato, è ridondante. Se ti riferisci a un debugging e riutilizzo più semplice, allora preferisco l’opzione 1.

Non penso che troverai una risposta finita per questo che si applica a tutti i casi. I 2 non sono sempre intercambiabili – dal momento che per alcune query (alcuni join di sinistra) si ottengono risultati diversi posizionando i criteri nella riga WHERE vs FROM.

Nel tuo caso, dovresti valutare entrambe queste query. In SSMS, è ansible visualizzare i piani di esecuzione stimati e effettivi di entrambe queste query: sarebbe un buon primo passo per determinare quale sia più ottimale. È anche ansible visualizzare l’ora e l’I / O per ciascuna (impostare il tempo delle statistiche su, impostare le statistiche su on) – e ciò fornirà anche le informazioni per prendere una decisione.

Nel caso delle domande nella tua domanda – Scommetto che entrambi verranno con lo stesso piano di query – quindi in questo caso potrebbe non avere importanza, ma in altri potrebbe potenzialmente produrre piani diversi.

Prova questo per vedere la differenza tra i 2 …

 SET STATISTICS IO ON SET STATISTICS TIME ON select salesman.salesmanid, max(sales.quantity) from salesmaninner join sales on salesman.salesmanid =sales.salesmanid and sales.salesdate < salesman.promotiondate group by salesman.salesmanid select salesman.salesmanid, max(sales.quantity) from salesmaninner join sales on salesman.salesmanid = sales.salesmanid where sales.salesdate < salesman.promotiondate group by salesman.salesmanid SET STATISTICS TIME OFF SET STATISTICS IO OFF 

Una cosa che voglio dire alla fine, come ho comunicato, prima che .. Entrambi i modi possono dare le stesse prestazioni o utilizzare i criteri alla clausola Where può essere poco più veloce come si trova in alcune risposte ..

Ma ho identificato una differenza, puoi usarla per i tuoi bisogni logici …

  1. L’utilizzo dei criteri nella clausola ON non filtrerà / salterà le righe per selezionare invece le colonne di join risulterebbero nulle in base alle condizioni

  2. L’utilizzo dei criteri nella clausola Where può filtrare / saltare le righe nell’intero risultato

Può sembrare irriverente, ma la risposta è qualsiasi query per la quale l’analizzatore di query produce il piano più efficiente.

A mio parere, sembrano equivalenti, quindi l’analizzatore di query potrebbe produrre piani identici, ma è necessario testarlo.

Né è più efficiente, utilizzando il metodo WHERE è considerato il vecchio modo di farlo ( http://msdn.microsoft.com/en-us/library/ms190014.aspx ). Puoi guardare il piano di esecuzione e vedere che fanno la stessa cosa.

Acquisire familiarità con il piano di esecuzione stimato in SQL Management Studio !! Come altri hanno già detto, sei alla mercé dell’analizzatore, non importa quello che fai, fidati delle sue stime. Direi che i due che hai fornito avrebbero prodotto lo stesso identico piano.

Se si tratta di un tentativo di cambiare una cultura di sviluppo, scegli quella che ti offre un piano migliore; per quelli che sono identici, segui la cultura

Ho commentato questo su altri post di “efficienza” come questo (è sia sincero che sarcastico) – se è qui che risiedono i tuoi colli di bottiglia, allora il cinque contro te e il tuo team.

Il caso 1 (i criteri nel JOIN) è migliore per l’incapsulamento e un incapsulamento aumentato è di solito una buona cosa: riduzione delle omissioni di copia / incolla in un’altra query, diminuzione dei bug se successivamente convertiti in SINISTRA SINISTRA e aumento della leggibilità (cose correlate insieme e meno ” rumore “nella clausola WHERE). In questo caso, la clausola WHERE acquisisce solo i criteri della tabella principale oi criteri che si estendono su più tabelle.