Ottimizzare le performance delle query SQL

Uno degli obiettivi degli sviluppatori quando si ha a che fare con un database è sicuramente l’ottimizzazione delle query SQL. Esistono infatti molti modi per recuperare lo stesso risultato da un database ma con tempi e prestazioni totalmente differenti. Ottimizzare la query diventa un’attività importante tanto quanto la configurazione ottimale del server. Infatti, anche se utilizzassimo il miglior server sul mercato, le sue prestazioni potrebbero essere influenzate negativamente anche solo da poche query impostate male. In effetti, per diminuire drasticamente le performance di un server, basta una singola query.

Le prestazioni complessive del database possono essere notevolmente migliorate mediante una corretta progettazione e l’ottimizzazione delle tabelle e query.

1. Progettazione delle tabelle

Ogni tabella deve avere una chiave primaria, che aiuta a recuperare, aggiornare ed eliminare i record dalla tabella. La chiave primaria deve essere la prima colonna della tabella, per maggiore comodità dello sviluppatore.

Normalizza le tue tabelle alla terza forma normale (3NF). Una tabella è nella terza forma normale (3NF) se è nella seconda forma normale (2NF) e se non contiene dipendenze transitive.  La normalizzazione è un processo per ridurre i dati ridondanti dal database e per garantire una certa qualità allo schema relazionare che si traduce in assenza di determinati difetti. Oltre al fatto che avendo meno dati ridondati, il motore SQL non è appesantito e accelera le sue prestazioni. Una tabella no normalizzata presenta ridondanze e potrebbe avere comportamenti poco desiderabili durante le operazioni su di essa.

Alcune consigli da seguire:

Considera la denormalizzazione delle tabelle dalla quarta o quinta forma normale alla terza forma normale.
L’aumento della normalizzazione alla quarta e alla quinta forma normale può comportare una riduzione delle prestazioni, ovvero quando è necessario eseguire più join su più tabelle. Potrebbe essere necessario de-normalizzare le tabelle per evitare un degrado delle prestazioni.

Considera il partizionamento orizzontale delle tabelle molto grandi.
Per tabelle di dati più piccole si utilizza meno spazio, per cui i motori SQL hanno meno lavoro da fare per eseguire le query.

Prova a ridurre il numero di colonne in una tabella.
Minore è il numero di colonne in una tabella, minore sarà lo spazio che la tabella utilizzerà e minore sarà l’overhead I / O per accedere ai dati della tabella.

Utilizza i vincoli anziché i trigger, le regole e le impostazioni predefinite ogni volta che è possibile.
I vincoli sono molto più efficienti dei trigger e aumentano le prestazioni. A differenza dei trigger, i vincoli sono più coerenti e affidabili.

Se è necessario archiviare oggetti binari di grandi dimensioni, utilizza il file system invece del tipo di dati BLOB.
Se si stanno archiviando oggetti binari di grandi dimensioni nella tabella, il motore SQL impegna molte risorse durante la lettura delle righe per la scansione dei dati. È meglio archiviare nel file system. Nel caso in cui sia necessario archiviare all’interno del database, utilizzare una tabella aggiuntiva, sulla quale non verrà eseguita alcuna scansione dei dati e non influirà sulle prestazioni del server.

Utilizza il tipo di dati corretto in base al valore che presumi di archiviare.
Ci sono vari tipi di dati che ci permettono di memorizzare i vari tipi di dati. L’utilizzo del tipo di dati appropriato in base al valore aumenterebbe le prestazioni del server.

• Per i dati interi da 0 a 255, usa il tipo di dati tinyint (un byte)
• Per i dati interi da -32.768 a 32.767, usa il tipo di dati smallint (due byte)
• Per i dati interi da -2.147.483.648 a 2.147.483.647, il tipo di dati useint (quattro byte)
• Utilizza il tipo di dati smallmoney invece del tipo di dati money, se devi memorizzare valori di dati monetari compresi tra 214.748,3648 e 214.748,3647 (quattro byte)
• Utilizzare il tipo di dati smalldatetime invece del tipo di dati datetime, se è necessario archiviare i dati di data e ora dal 1 gennaio 1900 al 6 giugno 2079, con una precisione al minuto (quattro byte)
• Se possibile, usa tipi varchar / nvarchar invece di colonne text o ntext.

2. Ottimizzazione delle query

Usa sempre la clausula WHERE nelle SELECT quando non è necessario che vengano restituite tutte le righe. 
Ciò contribuirà a restringere il campo dei risultati. In caso contrario il motore SQL eseguirà una scansione dell’intera tabella e sprecherà risorse con conseguente aumento della larghezza di banda della rete. Inoltre durante la scansione dell’intera tabella, questa verrebbe bloccata in accesso esclusivo che potrebbe non consentire ad altri utenti di accedere alla tabella.

Utilizza funzioni native SQL nelle query SQL solo se strettamente necessario.
Si è visto molte volte che gli sviluppatori usano codici come

SELECT * FROM OfferTable WHERE LOWER(UserName)='jshah' 

SELECT * FROM OfferTable WHERE UserName='jshah'

Entrambe le query fanno essenzialmente lo stesso lavoro ma la seconda è migliore e recupera le righe più velocemente della prima query, poichè nella prima vi è la chiamata alla funzione nativa LOWER. Ovviamente, non sempre sarà possibile utilizzare la prima query, infatti, se il database è impostato Case Sensitive, allora sarà necessario  necessario utilizzare la prima query.

Operatori logici.
Durante l’esecuzione di una query, gli operatori logici utilizzati nella clausola WHERE influiscono direttamente sulle prestazioni. Gli operatori mostrati di seguito sono inseriti in ordine decrescente di prestazioni, cioè l’operatore = è il più performante, mentre l’operatore <> è il meno performante.

=
>,>=,<, <=
LIKE
<>

Carattery Jolly.
A seconda dei casi, quando si usa l’operatore LIKE nella condizione WHERE della query potrebbe essere più indicato usare uno o più caratteri iniziali invece di usare il carattere jolly all’inizio o alle due estremità della stringa cercata.

SELECT * FROM Fornitore WHERE Nome LIKE 'm%'

SELECT * FROM Fornitore WHERE Nome LIKE '%m'

Nella prima query, verranno cercati i risultati con un indice ben preciso (m) e quindi il carico sul server verrà sensibilmente ridotto. Nella seconda query invece, non è possibile creare un indice durante l’esecuzione della query.

BETWEEN.
E’ sempre consigliabile utilizzare BETWEEN per ottenere risultati migliori, in casi come questo indicato sotto. La prima riga è ottimizzata rispetto alla seconda che utilizza l’operatore IN.

SELECT * FROM Fornitore WHERE ID_fornitore BETWEEN (300 AND 303)

SELECT * FROM Fornitore WHERE ID_fornitore IN (300,301,302,303)

SUBSTRING.
Evita di utilizzare la funzione SUBSTRING nella query. E’ chiaramente più performante utilizzare l’operatore LIKE anzichè ricercare il primo carattere di ogni stringa contenuta nella colonna Nome.

SELECT * FROM Fornitore WHERE Nome LIKE 'n%'

SELECT * FROM Fornitore WHERE SUBSTRING(Nome, 1, 1)='n'.

Operatore AND.
Le query che utilizzano WHERE con le condizioni collegate da operatori AND vengono valutate da sinistra a destra nell’ordine in cui sono scritte. Quindi, se l’espressione AND è falsa nella fase iniziale, l’istruzione / clausola terminerà immediatamente, il che si tradurrà in un risparmio di tempo di esecuzione.

UNION.
A volte è meglio combinare le query usando UNION ALL invece di usare molti operatori OR. Ad esempio, prendiamo in esame la query qui sotto:

SELECT ID_fornitore, Nome, Cognome FROM Fornitore WHERE Citta = 'Roma' or CAP = '80010' or Provincia = 'MI'

In questo particolare caso, il motore SQL dovrà disporre di indici su tutte e 3 le colonne. Potremmo allora usare UNION ALL per ottimizzarla, per quanto scomoda possa sembrare per lo sviluppatore:

SELECT ID_fornitore, Nome, Cognome FROM Fornitore WHERE Citta = 'Roma'
UNION ALL
SELECT ID_fornitore, Nome, Cognome FROM Fornitore WHERE CAP = '80010'
UNION ALL
SELECT ID_fornitore, Nome, Cognome FROM Fornitore WHERE Provincia = 'MI'

Entrambe le query forniranno gli stessi risultati, ma se è presente solo un indice su Città e nessun indice su CAP o su Provincia, la prima query non utilizzerà l’indice e verrà eseguita una scansione della tabella. Ma il secondo utilizzerà l’indice come parte della query.

L’aggregatore COUNT.
L’aggregatore COUNT è utilissimo in taluni casi, mentre può essere controproducente se utilizzato in altri. Ad esempio, non bisogna utilizzare l’aggregazione COUNT() in una sottoquery per eseguire un controllo di esistenza. La query qui sotto, ancorchè funzionante, è poco ottimizzata:

SELECT column_list FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)

SELECT column_list FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)

Invece, quando si utilizza EXISTS, SQL Server sa che si sta eseguendo un controllo di esistenza. Dopo aver trovato il primo valore corrispondente, restituisce TRUE e smette di cercare ulteriormente. Lo stesso concetto si applica anche con gli operatori IN o ANY.

Funzioni definite dall’utente.
Non chiamare ripetutamente stored procedure, trigger, funzioni e batch. Ad esempio, potresti aver bisogno della lunghezza di una variabile stringa in molti punti della tua procedura, ma non chiamare la funzione LEN ogni volta che è necessario; chiama invece la funzione LEN una volta e memorizzare il risultato in una variabile per un uso successivo

Cerca di evitare l’SQL dinamico.
A meno che non sia realmente richiesto, cerca di evitare l’uso dell’SQL dinamico perché è difficile eseguire il debug e trovare le soluzioni o la risoluzione dei problemi. Inoltre, quando l’utente inserisce l’input nell’SQL dinamico, esiste la possibilità di attacchi di tipo SQL injection.

Utilizzo di tabelle temporanee.
A meno che non sia necessario, riduci al minimo l’utilizzo di tabelle temporanee. Prova invece a usare le variabili di tabella. Nella maggior parte dei casi, le variabili di tabella risiedono all’interno della memoria, che è molto più veloce.

Invece della ricerca LIKE, usa la ricerca full text per cercare dati testuali.
Le ricerche full text sono sempre molto più preformanti delle ricerche LIKE. Ti consentirà di implementare criteri di ricerca complessi che non possono essere implementati utilizzando una ricerca con LIKE, come la ricerca su una singola parola o frase o potrebbe essere la ricerca su una parola o frase vicina o più vicina a un’altra parola o frase, o potrebbe essere cercando forme sinonime di una parola specifica.