Ottimizzazione delle Query


Home Page | Commenti | Articoli | Faq | Documenti | Ricerca | Archivio | Storie dalla Sala Macchine | Contribuire | Login/Register
A cura di Davide Bianchi Molto spesso l'efficienza di una applicazione, sia essa una pagina ASP/JSP/PHP o un vero programma, dipende dall'efficienza del sottostante database. Ma un database efficiente non serve a nulla se le query che facciamo sono il massimo dell'inefficienza. Come si puo' migliorare l'efficienza di una Query?

Come funziona il "motore del database" ?

Per poter utilizzare in modo efficiente il motore del database, dobbiamo sapere prima come funziona. Cioe' come le nostre query vengono elaborate e trasformate in una sequenza di record.

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.

Transformazione delle Query

Quando una query SQL e' inviata al "motore" di un database (RDBMS), questo effettua diversi passaggi per trasformare la query in una sequenza di record che rispondano a determinate caratteristiche.

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.

Selezione degli indici

Per ottimizzare una query, la maggior parte degli ottimizzatori verifica se nel database sono presenti degli indici utili per migliorare l'efficienza di accesso ai dati. Un indice e' considerato "utile" solo se inizia con le stesse colonne (campi) che sono contenute nella query. Questa deve essere una corrispondenza esatta.

Selezione delle Join

Quando gli indici sono stati scelti e tutte le clausole sono state associate ad un "costo di processo", l'ottimizzatore esegue la selezione delle Join. Questo e' un tentativo di scegliere il migliore ordine per combinare le varie clausole.

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.

Come ottimizzare la query?

Ok, adesso sappiamo tutto di come funziona il nostro motore "sotto al cofano", come usiamo queste informazioni a nostro vantaggio?

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) ... 
nella SQL, sia costruendo "ad hoc" le query, come spiegato di seguito.

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'
non risultera' nella selezione di un indice, anche se un indice e' disponibile per quel campo.

Una clausola del tipo


WHERE NOT campo = '...' o
WHERE CAMPO != '...'
Non verra' usato per la selezione di un indice, quindi evitare se possibile l'utilizzo di questo costrutto.

Un'espressione del tipo


WHERE CAMPO = 'valore'
e' l'optimum per la selezione se un indice unico e' disponibile. Un range

BETWEEN o
WHERE CAMPO >='..' AND CAMPO < '..'
viene subito dopo come termini di efficienza. Un'espressione aperta come

WHERE campo < 'valore'    o
WHERE campo > 'valore'
e' il peggio che si possa avere come uso degli indici.

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.

Progettare il database e l'applicazione

Una parte di ottimizzazione si fa' anche progettando BENE il database, prima ancora di fare una sola Select e' possibile lavorare per avere un database ottimizzato.

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.

Controllare se le cose funzionano

Utilizzando SET SHOWPLAN ON, si puo' controllare se la Query che abbiamo ottimizzato e' stata ottimizzata come noi avevamo previsto. In caso contrario, e' il caso di ripensare le nostre scelte e scegliere (magari) un diverso modo di ottimizzazione.

Bibliografia ed altre risorse sull'argomento

"An Introduction to Database Systems" - Addison/Wesley, 1990
"Fundamentals of Database Systems" - Benjamin/Cummings, 1989
"Designing Client-Server Applications for Enterprise Database Connectivity" - MSDN Library, Technical Articles.
"Microsoft SQL Server Network Integration Architecture." - MSDN Library, Technical Articles.
"Microsoft Open Data Services: Application Sourcebook." - MSDN Library, Technical Articles.
"Maximizing Performance Using Binary Columns and Bitwise Operations in Microsoft SQL Server for Windows NT." - MSDN Library, Technical Articles.
"Understanding the Microsoft SQL Server Optimizer." - Microsoft Networking Journal, January 1991.
"Backup and Recovery Guidelines for Microsoft SQL Server." - MSDN Library, Technical Articles.
"Optimization of Query Evaluation Algorithms." - ACM TODS, June 1979.


I commenti sono aggiunti quando e soprattutto se ho il tempo di guardarli e dopo aver eliminato le cagate, spam, tentativi di phishing et similia. Quindi non trattenete il respiro.

Nessun messaggio this document does not accept new posts

Precedente Successivo


Davide Bianchi, lavora come Unix/Linux System Administrator presso una societa' di "sicurezza informatica" (aka: $networkgestapo) di Haarlem. Contatti: mail: davide AT onlyforfun.net , Jabber: davideyeahsure AT gmail.com,


Volete contribuire? Leggete come!.
 
 

Il presente sito e' frutto del sudore della mia fronte (e delle mie dita), se siete interessati a ripubblicare uno degli articoli, documenti o qualunque altra cosa presente in questo sito per cortesia datemene comunicazione (o all'autore dell'articolo se non sono io), cosi' il giorno che faccio delle aggiunte potro' avvisarvi e magari mandarvi il testo aggiornato.


Questo sito era composto con VIM, ora e' composto con VIM ed il famosissimo CMS FdT.

Questo sito non e' ottimizzato per la visione con nessun browser particolare, ne' richiede l'uso di font particolari o risoluzioni speciali. Siete liberi di vederlo come vi pare e piace, o come disse qualcuno: "Finalmente uno dei POCHI siti che ancora funzionano con IE5 dentro Windows 3.1".

Web Interoperability Pleadge Support This Project
Powered By Gort