Ottimizzazione delle query SQL: 15 tecniche per prestazioni migliori

6. Ottimizza le clausole WHERE

La clausola WHERE è fondamentale nelle query SQL perché ci permette di filtrare i dati in base a condizioni specifiche, assicurando che vengano restituite solo le righe rilevanti. Migliora l’efficienza delle query riducendo la quantità di dati elaborati, aspetto molto importante quando si lavora con dataset di grandi dimensioni. 

Una WHERE ben scritta può quindi essere un’alleata potente quando ottimizziamo le prestazioni di una query SQL. Vediamo alcuni modi per sfruttarla al meglio:

• Aggiungi condizioni di filtro adeguate il prima possibile. A volte avere una clausola WHERE è utile ma non basta. Bisogna fare attenzione a dove viene posizionata. Filtrare il maggior numero possibile di righe all’inizio della WHERE può aiutare a ottimizzare la query.
• Evita di usare funzioni sulle colonne nella clausola WHERE. Quando applichiamo una funzione a una colonna, il database deve applicarla a ogni riga della tabella prima di poter filtrare i risultati. Questo impedisce l’uso efficace degli indici.

Per esempio, invece di: 

SELECT * 
FROM employees WHERE 
YEAR(hire_date) = 2020;

Dovremmo usare: 

SELECT * 
FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

• Usa operatori appropriati. Dovremmo scegliere gli operatori più efficienti che soddisfano le nostre esigenze. Ad esempio, = è generalmente più veloce di LIKE e usare intervalli di date specifici è più veloce che usare funzioni come MONTH(order_date).

Quindi, ad esempio, invece di eseguire questa query:

SELECT * 
FROM orders 
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

Possiamo eseguire la seguente: 

SELECT * 
FROM orders 
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';

7. Ottimizza le sottoquery

In alcuni casi, mentre scriviamo una query, sentiamo il bisogno di applicare dinamicamente filtri, aggregazioni o join sui dati. Non vogliamo fare più query; preferiamo mantenerne una sola. 

Per questi casi possiamo usare le sottoquery. Le sottoquery in SQL sono query annidate all’interno di un’altra query, in genere nelle istruzioni SELECT, INSERT, UPDATE o DELETE. 

Le sottoquery possono essere potenti e veloci, ma possono anche causare problemi di prestazioni se non usate con attenzione. In generale, dovremmo minimizzare l’uso delle sottoquery e seguire alcune best practice:

• Sostituisci le sottoquery con join quando possibile. I join sono generalmente più veloci e più efficienti delle sottoquery.
• Usa le common table expressions (CTE) al loro posto.  Le CTE separano il nostro codice in alcune query più piccole anziché in una unica e grande, risultando molto più leggibili.

WITH SalesCTE AS ( 
             SELECT salesperson_id, SUM(sales_amount) AS total_sales 
             FROM sales GROUP BY salesperson_id ) 

SELECT salesperson_id, total_sales 
FROM SalesCTE WHERE total_sales > 5000;

• Usa sottoquery non correlate. Le sottoquery non correlate sono indipendenti dalla query esterna e possono essere eseguite una sola volta, mentre quelle correlate vengono eseguite per ogni riga della query esterna.

8. Usa EXISTS invece di IN per le sottoquery

Quando lavoriamo con sottoquery, spesso dobbiamo verificare se un valore esiste in un insieme di risultati. Possiamo farlo con IN o EXISTS, ma EXISTS è in genere più efficiente, soprattutto su dataset di grandi dimensioni.

La clausola IN legge l’intero risultato della sottoquery in memoria prima del confronto. Al contrario, EXISTS interrompe l’elaborazione della sottoquery non appena trova una corrispondenza. 

Ecco un esempio di come usare questa clausola:

SELECT * 
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'USA');

9. Limita l'uso di DISTINCT

Immagina di lavorare a un’analisi per inviare un’offerta promozionale a clienti di città uniche. Il database contiene più ordini degli stessi clienti. La prima cosa che ci viene in mente è usare la clausola DISTINCT.

Questa funzione è utile in certi casi ma può essere dispendiosa in termini di risorse, soprattutto su dataset grandi. Esistono alcune alternative a DISTINCT:

• Identificare e rimuovere i dati duplicati durante i processi di pulizia. Questo previene la comparsa di duplicati nel database all’origine.
• Usare GROUP BY al posto di DISTINCT quando possibile. GROUP BY può essere più efficiente, soprattutto se combinato con funzioni di aggregazione. 

Quindi, invece di eseguire:

SELECT DISTINCT city FROM customers;

Possiamo usare:

SELECT city FROM customers GROUP BY city;

• Usa funzioni finestra. Funzioni finestra come ROW_NUMBER possono aiutarci a identificare i duplicati e filtrarli senza usare DISTINCT.

10. Sfrutta le funzionalità specifiche del database

Quando lavoriamo con i dati, interagiamo tramite SQL attraverso un sistema di gestione del database (DBMS). Il DBMS elabora i comandi SQL, gestisce il database e garantisce integrità e sicurezza dei dati. I diversi sistemi di database offrono funzionalità uniche che possono aiutare a ottimizzare le query. 

Gli hint del database sono istruzioni speciali che possiamo aggiungere alle query per eseguirle in modo più efficiente. Sono uno strumento utile, ma vanno usati con cautela. 

Ad esempio, in MySQL, l’hint USE INDEX può forzare l’uso di un indice specifico:

 SELECT * FROM employees USE INDEX (idx_salary) WHERE salary > 50000;

In SQL Server, l’hint OPTION (LOOP JOIN) specifica il metodo di join: 

SELECT * 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id OPTION (LOOP JOIN); 

Questi hint sovrascrivono l’ottimizzazione predefinita delle query, migliorando le prestazioni in scenari specifici.

D’altra parte, partizionamento e sharding sono due tecniche per distribuire i dati nel cloud. 

• Con il partizionamento, dividiamo una grande tabella in più tabelle più piccole, ciascuna con la propria chiave di partizione. Le chiavi sono in genere basate sui timestamp di creazione delle righe o su valori interi. Quando eseguiamo una query su questa tabella, il server ci indirizza automaticamente alla partizione appropriata. 
• Lo sharding è molto simile, tranne per il fatto che invece di suddividere una grande tabella in tabelle più piccole, si divide un grande database in database più piccoli. Ognuno di questi database è su un server diverso. Invece di una chiave di partizione, una chiave di sharding reindirizza le query verso il database appropriato. Lo sharding aumenta la velocità di elaborazione perché il carico è suddiviso su server diversi. 

11. Monitora e ottimizza le statistiche del database

Mantenere aggiornate le statistiche del database è importante per fare in modo che l’ottimizzatore di query possa prendere decisioni informate e accurate sul modo più efficiente di eseguire le query. 

Le statistiche descrivono la distribuzione dei dati in una tabella (ad esempio, il numero di righe, la frequenza dei valori e la loro distribuzione tra le colonne) e l’ottimizzatore si basa su queste informazioni per stimare i costi di esecuzione. Se le statistiche non sono aggiornate, l’ottimizzatore può scegliere piani di esecuzione inefficienti, come usare gli indici sbagliati o optare per una scansione completa invece di una più efficiente scansione tramite indice, con conseguenti scarse prestazioni delle query.

I database spesso supportano aggiornamenti automatici per mantenere statistiche accurate. Ad esempio, in SQL Server, la configurazione predefinita aggiorna automaticamente le statistiche quando cambia una quantità significativa di dati. Allo stesso modo, PostgreSQL ha una funzionalità di auto-analyze, che aggiorna le statistiche dopo una soglia specificata di modifiche ai dati. 

Tuttavia, possiamo aggiornare manualmente le statistiche nei casi in cui gli aggiornamenti automatici non bastano o se è necessario un intervento manuale. In SQL Server, possiamo usare il comando UPDATE STATISTICS per aggiornare le statistiche di una specifica tabella o indice, mentre in PostgreSQL si può eseguire il comando ANALYZE per aggiornare le statistiche di una o più tabelle. 

-- Update statistics for all tables in the current database
ANALYZE;

-- Update statistics for a specific table
ANALYZE my_table;

12. Usa stored procedure

Una stored procedure è un insieme di comandi SQL che salviamo nel database, così non dobbiamo riscrivere ogni volta lo stesso SQL. Possiamo pensarla come a uno script riutilizzabile. 

Quando dobbiamo eseguire un certo compito, come aggiornare record o calcolare valori, richiamiamo la stored procedure. Può accettare input, eseguire operazioni come interrogare o modificare dati e persino restituire un risultato. Le stored procedure accelerano le operazioni poiché l’SQL è precompilato, rendendo il codice più pulito e facile da gestire. 

Possiamo creare una stored procedure in PostgreSQL come segue:

CREATE OR REPLACE PROCEDURE insert_employee(
    emp_id INT,
    emp_first_name VARCHAR,
    emp_last_name VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
    -- Insert a new employee into the employees table
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (emp_id, emp_first_name, emp_last_name);
END;
$;

-- call the procedure
CALL insert_employee(101, 'John', 'Doe');

13. Evita ordinamenti e raggruppamenti non necessari

Come professionisti dei dati, ci piace avere i dati ordinati e raggruppati per ricavarne insight più facilmente. In SQL usiamo spesso ORDER BY e GROUP BY.

Tuttavia, entrambe le clausole possono essere costose dal punto di vista computazionale, soprattutto con dataset grandi. Quando ordina o aggrega i dati, il motore del database spesso deve eseguire una scansione completa e poi organizzarli, identificare i gruppi e/o applicare funzioni di aggregazione, tipicamente usando algoritmi ad alta intensità di risorse. 

Per ottimizzare le query, possiamo seguire alcuni suggerimenti:

• Minimizza l’ordinamento. Dovremmo usare ORDER BY solo quando necessario. Se l’ordinamento non è essenziale, omettere questa clausola può ridurre drasticamente i tempi di elaborazione. 
• Usa gli indici. Quando possibile, assicuriamoci che le colonne coinvolte in ORDER BY e GROUP BY siano indicizzate. 
• Sposta l’ordinamento nel livello applicativo. Se possibile, spostiamo l’operazione di ordinamento al livello applicativo anziché nel database. 
• Pre-aggrega i dati. Per query complesse che coinvolgono GROUP BY, possiamo pre-aggregare i dati in una fase precedente o in una vista materializzata, in modo che il database non debba ricalcolare ogni volta le stesse aggregazioni.

14. Usa UNION ALL invece di UNION

Quando vogliamo combinare i risultati di più query in un unico elenco, possiamo usare le clausole UNION e UNION ALL. Entrambe combinano i risultati di due o più istruzioni SELECT con gli stessi nomi di colonna. Tuttavia, non sono identiche e la loro differenza le rende adatte a casi d’uso diversi.

La clausola UNION rimuove le righe duplicate, cosa che richiede più tempo di elaborazione. 

Figura: Union in SQL. Source immagine: DataCamp SQL-Join cheat sheet.

D’altra parte, UNION ALL combina i risultati ma mantiene tutte le righe, inclusi i duplicati. Quindi, se non serve rimuovere duplicati, è meglio usare UNION ALL per prestazioni migliori.

Figura: UNION ALL in SQL. Source immagine: DataCamp SQL-Join cheat sheet.

-- Potentially slower
SELECT product_id FROM products WHERE category = 'Electronics'
UNION
SELECT product_id FROM products WHERE category = 'Books';

-- Potentially faster
SELECT product_id FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_id FROM products WHERE category = 'Books';

15. Scomponi le query complesse

Lavorare con dataset di grandi dimensioni implica che spesso incontreremo query complesse difficili da comprendere e ottimizzare. Possiamo affrontare questi casi scomponendole in query più piccole e semplici. In questo modo è più facile individuare i colli di bottiglia e applicare tecniche di ottimizzazione.

Una delle strategie più usate per scomporre le query è ricorrere alle viste materializzate. Si tratta di risultati di query precomputati e memorizzati, accessibili rapidamente senza ricalcolare la query ogni volta che viene richiamata. Quando i dati sottostanti cambiano, la vista materializzata deve essere aggiornata manualmente o automaticamente.

Ecco un esempio di come creare e interrogare una vista materializzata:

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;

-- Query the materialized view
SELECT * FROM daily_sales;

Conclusione

In questo articolo abbiamo esplorato diverse strategie e best practice per ottimizzare le query SQL, dall’indicizzazione ai join, fino a sottoquery e funzionalità specifiche dei database. Applicando queste tecniche, puoi migliorare sensibilmente le prestazioni delle tue query e far funzionare i database in modo più efficiente.

Ricorda che ottimizzare le query SQL è un processo continuo. Man mano che i tuoi dati crescono e l’applicazione evolve, dovrai monitorare e ottimizzare costantemente le query per garantire prestazioni ottimali.

Per approfondire la tua conoscenza di SQL, ti invitiamo a esplorare le seguenti risorse su DataCamp:

• SQL Basics Cheat Sheet
• Improving Query Performance in SQL Server
• Improving Query Performance in PostgreSQL