Differenza tra CTE e SubQuery?

Da questo post Come utilizzare ROW_NUMBER nella seguente procedura?

Esistono due versioni di risposte in cui si utilizza un SubQuery e l’altro utilizza un CTE per risolvere lo stesso problema.

Ora, qual è il vantaggio dell’uso di una CTE (Common Table Expression) su come ub-query (quindi, più leggibile ciò che la query sta effettivamente facendo)

L’unico vantaggio dell’utilizzo di una CTE rispetto alla selezione secondaria è che posso effettivamente denominare la query secondaria. Ci sono altre differenze tra questi due quando un CTE viene usato come CTE semplice (non ricorsivo)?

Nella sub-query rispetto alle versioni CTE semplici (non ricorsive), sono probabilmente molto simili. Dovresti utilizzare il profiler e il piano di esecuzione effettivo per individuare eventuali differenze, e questo sarebbe specifico per la tua configurazione (quindi non possiamo dirti la risposta per intero).

In generale ; Un CTE può essere utilizzato in modo ricorsivo; una sotto-query non può. Questo li rende particolarmente adatti alle strutture arboree.

Il vantaggio principale dell’espressione di tabella comune (quando non lo si utilizza per le query ricorsive ) è l’incapsulamento, invece di dover dichiarare la sottoquery in ogni luogo in cui si desidera utilizzarlo, è ansible definirlo una volta, ma avere più riferimenti ad esso.

Tuttavia, questo non significa che sia eseguito una sola volta (come da precedenti iterazioni di questa risposta , grazie a tutti quelli che hanno commentato). La query ha sicuramente il potenziale per essere eseguita più volte se riferita più volte; l’ottimizzatore di query alla fine prende la decisione su come interpretare il CTE.

CTE sono i più utili per la ricorsione:

 WITH hier(cnt) AS ( SELECT 1 UNION ALL SELECT cnt + 1 FROM hier WHERE cnt < @n ) SELECT cnt FROM hier 

restituirà @n righe (fino a 101 ). Utile per calendari, set di caratteri fittizi, ecc.

Sono anche più leggibili (secondo me).

A parte questo, le CTE e le subqueries sono identiche.

Una differenza che non è stata menzionata è che un singolo CTE può essere referenziato nelle varie parti di un sindacato

A meno che non manchi qualcosa, puoi nominare CTE e sottoquery altrettanto facilmente.

Immagino che la principale differenza sia la leggibilità (trovo che il CTE sia più leggibile perché definisce la sottoquery in primo piano piuttosto che nel mezzo).

E se hai bisogno di fare qualcosa con la ricorsione, avrai un po ‘di problemi a farlo con una subquery;)

Aggiungendo alle risposte degli altri, se si ha una stessa subquery utilizzata più volte, è ansible sostituire tutte queste subquery con una CTE. Questo ti permette di riutilizzare meglio il tuo codice.

Una cosa che devi capire è che nelle vecchie versioni di SQL Server (molte persone hanno ancora bisogno di supportare i database di SQL Server 2000), le CTE non sono permesse e quindi la tabella derivata è la soluzione migliore.

Un fatto importante che nessuno ha menzionato è che (almeno nei postgres), le CTE sono delle recinzioni di ottimizzazione:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

Cioè, saranno trattati come una propria query atomica, piuttosto che ripiegati nell’intero piano di query. Mi manca l’esperienza per dare una spiegazione migliore, ma dovresti controllare la semantica per la versione di sql che stai usando; per utenti esperti, essere in grado di creare una recinzione di ottimizzazione può aiutare le prestazioni se si è esperti nel controllo del pianificatore di query; nel 99% dei casi, tuttavia, si dovrebbe evitare di provare a dire al pianificatore di query cosa fare, perché ciò che si pensa sarà più veloce è probabilmente peggio di quello che pensa sarà più veloce. 🙂

SUGGERIMENTO: (MAXRECURSION n)

è ansible limitare il numero di livelli di ricorsione consentiti per una specifica istruzione utilizzando l’hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola OPTION

Ad esempio, potresti provare:

 OPTION (MAXRECURSION 150) GO