Ottimizzazione delle Query

Questo articolo presenta i passi necessari per la "trasformazione ed elaborazione delle Query", inoltre verranno discussi i vari metodi di ottimizzazione in uso.

Vi sono molti testi sul soggetto, e quasi tutti sono in completo disaccordo sul metodo esatto di come applicare una ottimizzazione, molti sono in disaccordo anche su cosa e' una "ottimizzazione".

Attenzione: le informazioni su cui questo articolo si basa sono largamente basate sul motore di elaborazione di MS Sql Server, altri database potrebbero utilizzare metodi di trasformazione diversi e quindi impiegare processi di ottimizzazione molto diversi. Riferirsi alla documentazione del produttore del database in caso di problemi o dubbi.

I passaggi possono essere diversi per query che NON ritornano risultati (query di comando), noi ci concentreremo solo sulle query che ritornano dei risultati (query di interrogazione), dato che sono queste che piu' spesso richiedono ottimizzazione.

Parsing

Una volta che la query viene ricevuta dal motore del database, il primo passaggio e' il "parsing", cioe' la separazione della query nelle sue componenti. Questo processo ha due funzioni principali:

1. verificare la correttezza della query
2. identificare tutte le parti che compongono la query

Ogni singolo "pezzo" della query e' identificato e memorizzato in una struttura interna al motore di database, solitamente nella forma di un albero (query tree).

Un albero e' una rappresentazione che puo' essere facilmente manipolata dal sistema interno, aggiungendo, rimuovendo o spostando le sue componenti.

Standardizzazione

Uno dei punti di forza di un database relazionale e' l'abilita' di accettare query da utenti che non hanno una elevata comprensione della sottostante struttura del database, come risultato, una query puo' essere molto complessa, il sistema deve essere in grado di "risolvere" svariate combinazioni di istruzioni ed identificare i risultati corretti.

Il processo di "standardizzazione" e' di trasformare la query in un formato piu' comprensibile al motore stesso. Questo si effettua mediante una serie di manipolazioni sull'albero di query costruito precedentemente. Durante questo processo, vengono rimosse tutte le clausole eventualmente ridondanti e l'intero albero viene riarrangiato. L'albero risultante viene passato all'ottimizzazione.

Ottimizzazione della query

Lo scopo del processo di ottimizzazione della query e' produrre un "piano di esecuzione" il piu' possibile efficiente, basandosi su quanto e' specificato dall'albero della query.

Un "ottimizzatore" teoricamente puo' produrre un piano di esecuzione "ottimale" per ogni query, in realta' questo finira' per produrre un piano solamente accettabile per la maggioranza delle query. Questo perche' il numero di combinazioni possibili in una Join aumenta geometricamente e nello stesso modo aumenta la complessita' della query.

Senza l'utilizzo di tecniche di "pruning" o altri metodi euristici per limitare il numero di combinazioni valutate, il tempo richiesto per ottenere una reale ottimizzazione della query risulta assolutamente inaccettabile. In molti casi l'ottimizzatore sceglie una query meno efficiente (o totalmente inefficiente) perche' la selezione di una query piu' efficiente richiede piu' tempo che l'esecuzione della query inefficiente.

I vari database utilizzano di solito differenti tecniche di ottimizzazione per ottenere una certa efficienza nel piano di esecuzione.

Ottimizzazione euristica
Questo e' un meccanismo basato su regole specifiche per produrre un piano di esecuzione efficiente. Dato che la query ricevuta e' una struttura definita, ogni nodo dell'albero viene mappato direttamente in una espressione algebrica-relazionale. La funzione euristica e' quindi applicata per ridurre l'espressione ai suoi termini di base, ottenendo quindi una rappresentazione piu' efficiente.

Utilizzando un'espressione algebrica si assicura anche che nessuna delle necessarie informazioni richieste per esaminare i dati verra' persa durante il processo.

Ottimizzazione sintattica
Questo tipo di ottimizzazione si appoggia pesantemente sulla comprensione dell'utente sia del database sottostante, sia della distribuzione dei dati tra le varie tabelle. In soldoni, si fa' affidamento sul fatto che l'utente ha gia' fatto delle scelte in base alle proprie conoscenze. L'ottimizzatore cerca di migliorare l'efficienza della query scegliendo gli indici appositi tra quelli disponibili per ogni singola tabella.

Questo tipo di ottimizzazione e' estremamente efficiente quando si accede a dati in un'ambiente sostanzialmente statico e quando l'utente sa quello che sta' facendo. Ovviamente, se il database ed i suoi contenuti cambiano in maniera molto varia o se l'utente non sa esattamente cosa richiedere (la query non e' gia' ottimizzata di suo), i risultati possono essere pessimi.

Ottimizzazione "Cost-Based"
Per eseguire questo tipo di ottimizzazione, l'ottimizzatore richiede informazioni specifiche relative alle informazioni del database stesso. Queste informazioni sono strettamente dipendenti dal sistema e possono includere cose come la dimensione dei files, la struttura degli stessi, la disponibilita' di indici, la percentuale di record da recuperare da ogni tabella etc. etc.

Dato che lo scopo di ogni ottimizzazione e' quello di ridurre al minimo il numero di record estratti ed il tempo di estrazione, l'ottimizzazione cost-based utilizza le informazioni sulla struttura del database e la distribuzione dei dati per assegnare un "costo" stimato, in termini di tempo e numero di record da estrarre da ogni tabella, numero di accessi etc. per ogni operazione.

Valutando la somma totale di questi "costi" e' possibile selezionare la sequenza piu' efficiente di estrazione dei dati.

Ovviamente, i "costi" assegnati saranno piu' o meno validi a seconda delle informazioni che il sistema ha/mantiene sulla composizione delle tabelle, dei files etc. etc. Mantenere aggiornate queste informazioni occupa tempo e risorse, quindi ogni sistema memorizza un blocco di informazioni e poi lo aggiorna (ricostruendolo) di tanto in tanto. Se sul database vengono effettuate molte operazioni che coinvolgono la distruzione totale e la ricostruzione da zero di svariate tabelle, l'efficienza di questo metodo di ottimizzazione e' seriamente compromessa.

Ottimizzazione semantica
Questo tipo di ottimizzazione non e' ancora entrata nel novero delle ottimizzazioni "standard", ma e' oggetto di ricerche. Questo metodo si basa sulla conoscenza della struttura del sottostante database per ignorare o eliminare parti della query che non ritornerebbero risultato o non ritornerebbero risultati utili.

L'ottimizzatore confronta vari ordinamenti delle clausole, quindi seleziona quello con il minor tempo di processo stimato.

La maggior parte dei database (compreso SQL Server), utilizza una ottimizzazione cost-based. Perche' ? Perche' e' molto piu' semplice da implementare piuttosto che una Euristica.

Ovviamente, fornendo al "motore" delle query che vengano interpretate correttamente e che diano meno dubbi possibile su quello che il motore deve fare, inoltre si puo' agire anche sulla struttura del database, riducendo al minimo i problemi che esso puo' incontrare.

Indici

Obbligare il motore a scegliere gli indici giusti (o a scegliere gli indici in assoluto invece di passare tutta la tabella come fa' a volte). Questo si fa' sia forzando l'uso di un certo indice mediante la clausola

SELECT .. FROM table WITH (INDEX n) ... 

In alcuni casi (tabelle di piccole dimensioni) e' opportuno forzare il motore ad effettuare un TableScan.

Clausole WHERE

Un'espressione del tipo

WHERE campo >= valore * 12 + 3000   o
WHERE SUBSTRING( campo, 1, 1) = 'C'

Una clausola del tipo

WHERE NOT campo = '...' o
WHERE CAMPO != '...'

Un'espressione del tipo

WHERE CAMPO = 'valore'

BETWEEN o
WHERE CAMPO >='..' AND CAMPO < '..'

WHERE campo < 'valore'    o
WHERE campo > 'valore'

Clausole da evitare

Evitare dove possibile clausole OR o IN, in quanto questo tipo di "disgiunzioni" portano quasi sicuramente alla creazione di una tabella temporanea per risolvere la clausola, quindi un elevato input/output del database stesso. Inoltre, se per risolvere una clausola OR viene richiesto un TableScan (nessun uso di indice), lo stesso verra' usato per tutta la query.

Tabelle temporanee

Una tabella temporanea e' creata per risolvere i problemi dati da

• GROUP BY
• ORDER BY se nessun indice e' disponibile o se l'ordine e' in conflitto con il SORT ORDER del server stesso
• SELECT INTO
• DISTINCT
• Subquery

Se nessun indice e' disponibile per una Join, verra' costruita una tabella temporanea per la piu' piccola delle due tabelle della Join, ed un indice clustered verra' costruito sulla tabella temporanea.

Evitare (se possibile) aggiornamenti multipli sulla stessa "pagina", questo avviene quasi sempre sulle tabelle non-indicizzate, in quanto tutte le aggiunte sono fatte sull'ultima pagina "aperta".

Evitare transazioni che richiedano l'intervento dell'utente, questo richiede il mantenimento di Lock per tutta la durata della transazione, aumentando la possibilita' che si abbiano degli accessi concorrenti.

Usare Stored Procedures e View per accedere ai dati dove possibile, le SP e le Views sono compilate all'interno del server, quindi il "piano di esecuzione" e' gia' memorizzato e non deve essere interpretato ogni volta.

Creare gli indici "giusti" sulle varie tabelle. Ogni indice deve essere mantenuto, quindi evitare di creare indici su tutti i campi ma creare solo quelli essenziali. Ricordarsi che se la sequenza dei campi non corrisponde ESATTAMENTE a quanto richiesto, l'indice non viene usato.

Evitare (dove possibile) le OUTER JOIN (LEFT JOIN,RIGHT JOIN), soprattutto su tabelle in cui i campi di Join possono contenere NULL.