CTE vs. tabella temporanea: Qual è la differenza?

SQL offre due opzioni per gestire in modo efficiente le query complesse: le CTE e le tabelle temporanee. Continuate a leggere per valutare i pro e i contro delle CTE e delle tabelle temporanee.

In SQL (Structured Query Language), un'espressione di tabella comune (CTE) consente di definire una sotto-query e di assegnarle un nome per poterla utilizzare all'interno di una query principale. Una tabella temporanea è una tabella di database creata esplicitamente, come qualsiasi altra tabella, ma con la particolarità che scompare quando si chiude la connessione al database in cui è stata creata. Quindi, sì, le CTE e le tabelle temporanee hanno alcune cose in comune. Ma c'è dell'altro nel dibattito tra CTE e tabelle temporanee.

Per approfondire questo argomento, potete seguire il nostro corso Query ricorsive . Imparerete a utilizzare le CTE per elaborare strutture di dati complesse come alberi e grafici. Con i suoi 114 esercizi, questo corso vi guiderà alla padronanza delle query SQL più complesse. Il corso prevede anche un quiz finale per verificare le conoscenze acquisite.

Le CTE e le tabelle temporanee hanno scopi comuni. Entrambe generano risultati intermedi per una query senza lasciare oggetti permanenti nel database; ciò consente di risparmiare spazio di archiviazione. Ma ci sono importanti differenze tra loro, per cui è utile sapere quando usare l'una o l'altra. Cominciamo con l'esaminare la sintassi della CTE.

Sintassi della CTE

Di seguito vedremo un caso d'uso in cui una CTE SQL è preferibile a una query ricorsiva.

Una CTE SQL inizia con una clausola WITH, seguita da un nome e dalla definizione della sua sottoquery. Dopo di che, c'è un'istruzione SQL ordinaria (di solito una SELECT) che interroga i risultati della CTE come una normale tabella. Nella sua forma più semplice, una CTE ha questo aspetto:

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

Per maggiori dettagli sulla sintassi delle CTE, leggere Cos'è un'espressione di tabella comune in SQL.

Sintassi delle tabelle temporanee

Per le tabelle temporanee, la sintassi varia leggermente a seconda del sistema di gestione dei database relazionali (RDBMS). La tabella seguente mostra come creare tabelle temporanee nei RDBMS più diffusi:

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

Per saperne di più sulla creazione di tutti i tipi di tabelle in SQL, seguite il nostro corso Le basi della creazione di tabelle in SQL. I suoi 80 esercizi vi aiuteranno a padroneggiare il comando CREATE TABLE e tutte le sue opzioni. Inizierete con la creazione di una tabella nella sua forma più elementare, per poi passare alla definizione di chiavi primarie e straniere, colonne NULL/NOT NULL e alla modifica della struttura di tabelle esistenti.

Ora che abbiamo visto la sintassi delle CTE e delle tabelle temporanee, esaminiamo il funzionamento di ciascuna di esse.

Risolvere lo stesso problema con le CTE e le tabelle temporanee

Proviamo un esempio di problema che può essere risolto utilizzando una CTE e una tabella temporanea.

Si hanno due tabelle: una tabella dettagliata Sales e una tabella Countries tabella con informazioni specifiche sul paese. Si desidera elencare i totali delle vendite per paese e le informazioni dettagliate sul paese. Per ottenere questo risultato, è possibile utilizzare un'espressione di tabella comune o una tabella temporanea. Analizzeremo entrambi i metodi per vedere come possono essere utilizzati efficacemente per questo compito.

La soluzione basata su CTE utilizza una CTE chiamata CountryTotals con una sottoquery che calcola i totali raggruppati per CountryCode. Ha anche una query principale che combina questi totali con i dati di ciascun Paese:

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Si possono trovare molti altri esempi di espressioni di tabelle comuni in queste CTE spiegate con esempi.

Per ottenere lo stesso risultato utilizzando una tabella temporanea, occorre prima creare la tabella temporanea con le colonne CountryCode e TotalSales. Il seguente esempio di codice utilizza la sintassi di MySQL per farlo:

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

Le colonne che definiscono la struttura della tabella temporanea sono le stesse incluse nella definizione CTE di cui sopra. Per creare la tabella temporanea, il tipo di dati di ogni colonna deve corrispondere a quello restituito dalla colonna corrispondente della query che si utilizzerà per riempirla di dati. Questa query è mostrata di seguito:

Si noti che SELECT posto dopo INSERT è simile a quello che definisce la sottoquery nella CTE originale.

Ora che i totali delle vendite per paese sono nella tabella temporanea, è possibile unirla alla tabella Countries per ottenere il risultato finale:

SELECT
  c.*,
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Anche questa query è identica alla query CTE principale (quella al di fuori della clausola WITH ), poiché la tabella temporanea ha lo stesso nome della CTE.

È importante notare che non è sempre possibile sostituire le tabelle temporanee con le CTE. È possibile farlo quando le righe della tabella temporanea provengono da un unico SELECT. Ma se i dati vengono modificati da UPDATE, DELETE o altri comandi INSERT dopo essere stati caricati nella tabella temporanea, non sarà possibile sostituire la tabella temporanea con una CTE.

Quando utilizzare una CTE rispetto a una tabella temporanea

Anche nelle situazioni in cui è possibile sostituire una CTE con una tabella temporanea (o viceversa), non è sempre una buona pratica farlo. I casi d'uso delle CTE e quelli delle tabelle temporanee sono diversi; ci sono situazioni in cui è preferibile una CTE e situazioni in cui è preferibile una tabella temporanea.

Esaminiamo i casi d'uso delle CTE e delle tabelle temporanee, iniziando dalle CTE.

Casi d'uso delle CTE

Le CTE non generano oggetti persistenti nel database. In altre parole, la CTE vive solo finché la query che la contiene viene eseguita. Una volta terminata l'esecuzione della query, la CTE scompare senza lasciare traccia. Inoltre, la CTE e la query che la consuma sono contenute nella stessa istruzione SQL. Queste qualità rendono la CTE preferibile per migliorare la leggibilità e l'organizzazione degli script SQL, cioè per rendere le query più comprensibili all'uomo.

Piccoli insiemi di dati

Quando il risultato di una sottoquery produce un numero relativamente piccolo di righe (ad esempio 10 o 20 mila righe), una CTE è il modo ideale per implementarla. Poiché non utilizza la memoria persistente, non genera operazioni di I/O su disco. Pertanto, le query vengono risolte molto più velocemente.

Viste interne

Non è possibile creare, riempire di dati o interrogare una tabella temporanea all'interno di una vista. È invece possibile includere una CTE all'interno di una vista e utilizzarla ovunque come se fosse una tabella di sola lettura.

Rifattorizzazione

Nella rifattorizzazione delle query di database, le CTE sono un ottimo alleato. Nelle query lunghe e complesse, le CTE consentono di definire risultati intermedi (cioè sottoquery denominate) che possono essere riutilizzati più volte all'interno della stessa query.

Inoltre, la complessità di una lunga query può essere gradualmente ridotta con le CTE annidate, fino a semplificarla a una SELECT di poche righe.

Migliorare le prestazioni del database

In alcuni RDBMS (ad esempio MS SQL Server), le CTE consentono di creare piani di esecuzione più efficienti rispetto alle tabelle temporanee. Ciò può migliorare notevolmente le prestazioni del database, riducendo i tempi di esecuzione delle query.

Il vantaggio in termini di prestazioni dipende dall'ottimizzatore dell'RDBMS, il che significa che l'uso delle CTE non sempre si traduce in prestazioni migliori. Tuttavia, è un fattore da considerare quando si sceglie tra una CTE e una tabella temporanea per la gestione delle subquery.

Ricorsione

Un'area in cui l'uso delle CTE è obbligatorio è quella delle query ricorsive. In SQL, una CTE ricorsiva è in grado di invocare se stessa per implementare processi ricorsivi, come l'attraversamento di strutture di dati gerarchiche. In altre parole, la ricorsione richiede le CTE.

Consentire la ricorsione in SQL evita la necessità di utilizzare altri linguaggi di programmazione; ciò favorisce una maggiore efficienza nelle query che devono invocare se stesse. Per saperne di più, consultate il nostro articolo Cos'è una CTE ricorsiva in SQL?

Ora che sapete in quali casi è conveniente utilizzare una CTE, potete migliorare le vostre conoscenze rivedendo le domande più comuni dei colloqui sulle CTE in SQL e allenandovi con questi esercizi sulle CTE.

Casi di utilizzo delle tabelle temporanee

Il vantaggio principale di CTE, ovvero non memorizzare i dati in modo persistente, può essere uno svantaggio. In alcuni casi d'uso, è preferibile avere i dati memorizzati in tabelle (anche temporanee). Vediamo le situazioni più tipiche.

Riutilizzo di insiemi di dati

Il riutilizzo del codice CTE è limitato a una singola query. I dati memorizzati in una tabella temporanea, invece, possono essere utilizzati più volte in query diverse. Il requisito fondamentale è che queste query siano eseguite all'interno della stessa connessione al database.

Processi ETL

Quando si implementano processi ETL (Extract, Transform, Load) utilizzando script SQL, spesso è necessario generare risultati intermedi e applicare trasformazioni successive a un singolo insieme di dati. Solo al termine di questo processo è possibile scaricare i dati in tabelle permanenti. In queste situazioni, è molto utile tenere a disposizione tabelle temporanee per tutto il processo di trasformazione.

Insiemi di dati di grandi dimensioni

Quando si lavora su insiemi di dati temporanei di grande volume (ad esempio, milioni di record), le tabelle temporanee sono sempre preferibili alle CTE. Se si tenta di eseguire una CTE che include una sottoquery di grande volume, è molto probabile che il motore di database cerchi di memorizzare i risultati temporanei nella memoria del server. Questo esaurirebbe la capacità disponibile o richiederebbe alcune risorse del sistema operativo per scaricare la memoria su disco. In breve, le prestazioni del database sarebbero scarse. Pertanto, se si sa in anticipo che i risultati di una sottoquery saranno molto grandi, è meglio usare le tabelle temporanee invece delle CTE.

Generare indici

Come qualsiasi altra tabella di un database, le tabelle temporanee supportano la creazione di indici per accelerare l'esecuzione delle query. Quando i risultati intermedi richiedono un numero elevato di righe (e devono essere utilizzati ripetutamente), è opportuno memorizzarli in una tabella temporanea con uno o più indici. La creazione degli indici può richiedere un po' di tempo, ma questo tempo viene più che recuperato se si devono eseguire più query sullo stesso set di dati.

In genere, i candidati migliori per l'indicizzazione sono le colonne utilizzate in JOINs, WHERE, o GROUP BY.

Conclusione del dibattito tra CTE e tabelle temporanee

La scelta tra CTE e tabelle temporanee non è sempre facile. Abbiamo discusso la sintassi e i casi d'uso tipici di entrambe le soluzioni.

Ricordate di esplorare il nostro Query ricorsive che vi insegnerà a usare le comuni espressioni di tabella. Il corso è rivolto ad analisti di database, studenti e sviluppatori alle prime armi che desiderano approfondire le proprie conoscenze di SQL. Per seguire il corso è sufficiente un computer con una connessione a Internet e un browser web.

Ora è il momento di mettere in campo la vostra saggezza! Se sapete quando usare le CTE o le tabelle temporanee, otterrete sempre la massima efficienza quando lavorate con i dati temporanei.