Come migliorare le performance di una query SQL

/ Performance dei database e temi più avanzati

In questo articolo sono mostrate tre tecniche per migliorare le performance del codice SQL e imparare a scrivere query più veloci e performanti.

 

Migliora le performance scrivendo condizioni Sargable

Le condizioni Sargable sono delle condizioni inserite nella Where di una query che permettono al motore di database di utilizzare efficientemente gli indici presenti sulle colonne oggetto del filtro. Vediamo una serie di esempi per spiegare meglio il concetto.

Queste due query sembrano completamente identiche. Entrambe contano il numero di fatture emesse nel 2020. Tuttavia l’utilizzo della funzione YEAR all’interno della seconda query non permette di utilizzare efficientemente l’indice creato sulla colonna DataFattura.

CREATE INDEX IX_DataFattura 
ON dbo.Fatture (DataFattura);

SELECT COUNT(*)
FROM   dbo.Fatture
WHERE DataFattura >= '20200101' 
  AND DataFattura < '20210101';

SELECT COUNT(*)
FROM   dbo.Fatture
WHERE  YEAR(DataFattura) = 2020;

Analizziamo il piano di esecuzione e osserviamo come nel primo caso sia utilizzato l’operatore Index seek che esegue una ricerca ottimizzata, mentre nel secondo sia utilizzato un Index scan che esegue una scansione completa dei dati.

Analisi performance SQL tramite index seek e scan .

Vediamo un altro esempio. Qui l’obiettivo è estrarre il numero di clienti il cui nome inizia con la lettera N. Ciò viene fatto tramite il costrutto LIKE nella prima query e la funzione LEFT nella seconda.

SELECT  COUNT(*)
FROM    dbo.Clienti
WHERE   Nome LIKE 'N%';

SELECT  COUNT(*)
FROM    dbo.Clienti
WHERE   LEFT(Nome, 1) = 'N';

Anche in questo caso l’utilizzo di LEFT impedirebbe l’utilizzo dell’operatore index seek, con potenziali impatti sulle performance della query.

 

Sostituire dove possibile Union con Union All

Union e Union all vengono utilizzate per concatenare il risultato di più query. La differenza tra i due costrutti consiste nel fatto che l’Union, oltre ad accodare i dati, rimuove anche i duplicati presenti nella prima query, nella seconda query e nel risultato finale concatenato. In alcuni casi, per costruzione della query, si è sicuri che non potranno mai generarsi dei duplicati, ad esempio perché la query contiene al suo interno un identificativo univoco.

Consideriamo questo esempio:

SELECT 
  IdCliente AS Id, 
  CONCAT(Nome, ' ', Cognome) 
         AS Denominazione,
  Telefono, 
  'Cliente' AS Tipologia
FROM dbo.Clienti
  UNION
SELECT 
   IdFornitore AS Id, 
   Denominazione, 
   Telefono, 
   'Fornitore' AS Tipologia
FROM dbo.Fornitori
   UNION
SELECT 
   IdCorriere AS Id, 
   Denominazione, 
   Telefono, 
   'Corriere' AS Tipologia
FROM dbo.Corrieri;

Ci aspettiamo che IdCliente , IdFornitore e IdCorriere siano chiavi primarie delle relative tabelle Clienti, Fornitori e Corrieri, quindi non possono esserci dei duplicati all’interno delle singole sottoquery. Lo stesso varrà anche dopo l’unione, in quanto la colonna Tipologia sarà valorizzata per costruzione con tre stringhe diverse.

In questo caso dunque è lecito sostituire UNION con UNION ALL, il risultato della query sarà equivalente e le performance miglioreranno, come testimoniato dai piani d’esecuzione.

migliorare performance con grouping sets

Vediamo infatti che i due operatori “merge union” sono stati sostituiti da un unico e più performante Concatenation. Costringere il database a effettuare comunque una ricerca di duplicati, anche sapendo a priori che non ne esistono, è un comportamento da evitare in quanto la rimozione dei duplicati è un’operazione che può essere molto costosa dal punto di vista delle performance.

Il tuo percorso completo di Analisi dei Dati

 

T-SQL avanzato: utilizzare la clausola Grouping Sets

Su SQL Server possiamo migliorare le performance di una query grazie a sintassi specifiche del T-SQL. In particolare in questo paragrafo parleremo del costrutto Grouping Sets. In questo articolo, invece, ho approfondito le window function.

Riportare in un unico output sia i dati aggregati su certe dimensioni e sia una riga contenente le somme totali su tutte le righe è un’esigenza molto comune in fase di reporting. Un classico approccio consiste nell’utilizzare l’Union All per unire una query in cui è presente una Group by con una query in cui viene calcolato il totale complessivo. Ecco un esempio.

SELECT 
   Nazione, 
   COUNT(*) AS numero
FROM  dbo.Clienti
GROUP BY Nazione
   UNION ALL
SELECT 
   'Totale' AS Nazione, 
   COUNT(*) AS numero
FROM  dbo.Clienti;

Questa query costringe il piano d’esecuzione a effettuare due volte la scansione della tabella Clienti. Ciò può essere evitato su Sql Server utilizzando il costrutto Grouping Sets dopo la Group by.

SELECT 
 CASE WHEN Grouping_id(Nazione) = 0 
        THEN Nazione
        ELSE 'Totale' 
 END AS Nazione,
 COUNT(*) AS Numero
FROM    Clienti
GROUP BY 
 GROUPING SETS ( (Nazione), () );

Nella SELECT viene utilizzata la funzione Grouping_id per distinguere il Null relativo alla somma totale dall’eventuale presenza di Null nella colonna Nazione.

Osservando il piano d’esecuzione vediamo come il numero di scansioni passa da due a uno.

migliorare performance con grouping sets

Se la prima sottoquery restituisce poche righe, potremmo eseguire un ulteriore test materializzando il suo risultato in una tabella temporanea e analizzare se in questo modo otteniamo performance migliori.

SELECT Nazione, 
   COUNT(*) AS Numero
INTO #ClientiPerNazione
FROM  dbo.Clienti
GROUP BY Nazione;
SELECT Nazione, 
   Numero
FROM #ClientiPerNazione
   UNION ALL
SELECT  'Totale' AS Nazione, 
   SUM(Numero) AS Numero
FROM #ClientiPerNazione;

In questo articolo trovi un altro modo di utilizzare efficientemente le tabelle temporanee.

 

Formazione sulle performance di SQL Server

Scroll to Top
Torna su