• Programmazione » Database » SQL Server - Guida, manuale pratico e tutorial per database Microsoft

Ottimizzazione delle query SQL

L'ottimizzazione SQL prevede una trasformazione delleespressioni SQL che produca esattamente lo stesso risultatodelle query di partenza, ma solitamente in tempi piùbrevi. Questo processo richiede creatività e intuito,è quasi un'arte più che una scienza.

Ovviamente è impossibile esaurire l'argomento inun solo piccolo articolo, per cui ci limiteremo a fare delleclassificazioni delle ottimizzazioni possibili, in modo taleche siate preparati per sapere in che modo sia possibileottimizzare un database.

Il risultato ottenuto dal codice ottimizzato deve essereidentico al codice non ottimizzato. Le ottimizzazioni possonocomportare delle difficoltà e dei potenziali rischi,per cui alla fine della descrizione di ciascuna tipologia diottimizzazione, troverete delle note riguardo ai potenzialirischi nello sfruttare l'ottimizzazione descritta.

Tutte le ottimizzazioni sono elencate in ordine crescente diintrusività.

0. Ottimizzazioni non intrusive

Riguardano l'aggiornamento e il potenziamentodell'hardware del SQL server e la modifica di alcuniparamentri del server. L'aggiunta di banchi di memoria odi un processore non creeranno danni e non necessiteranno diri-testare i propri prodotti.

Il potenziamento a livello hardware potrebbe essereinefficace, poiché è possibile che il serbersoffra di Locks, problemi IO o di CPU. Un modo semplice perdeterminare i punti critici del server è attraverso i tool di Lakeside.

E' possibile accelerare il proprio IO mettendo LOG e DATIsu dispositivi differenti, separando gli indici dai dati, masfortunatamente, è molto difficile applicare taliconsigli al giorno d'oggi. Un drive di tipo RAID-5è abbastanza comune per tutti i piccoli server.

Per server di dimensioni maggiori sempre più aziendeusano array di dischi come quelli SAN EMC, molto buoni persistemi OLAP ma non adatti per sistemi OLTP.

La modifica dei parametri di SQL server èanch'essa piuttosto sicura, perché non va adintaccare il codice delle vostre applicazioni.Sfortunatamente non esiste un parametro del tipo'TURBO=TRUE', quindi bisogna sapersi destreggiarenella configurazione dei parametri del server, anche se nellamaggior parte dei casi tali server hanno meccanismi diautoregolazione della performance.

Per quanto concerne le modifiche hardware riportate eapplicando tutte le possibili raccomandazioni rintracciabiliin Rete, è possibile guadagnare 20-30% in piùdelle prestazioni, anche meno se non c'è nulla disbagliato nel vostro server. Perciò se le vostre querycominciano ad essere eseguite sempre più lentamente diciò che vi aspettate, non avete altra scelta checambiare il codice TSQL.

Rischi potenziali: nessuno.

1. Indici

L'ottimizzazione a questo livello comporta la creazione emodifica degli indici del database senza toccare il codice.

Sicuramente ogni DBA farà una certa opposizioneriguardo alla modifica del codice, specialmente a livello diproduzione. In alcuni casi è possibile effettuare talimodifiche senza considerarle vere modifiche a livello diprogrammazione convenzionale.

In primo luogo, tutto quello che si può fare con gliindici può incidere su piano dell'esecuzione,garantendo lo stesso risultato (a meno che non ci siano deibug in SQL Server, ma Microsoft passa molto più tempoa testare il loro prodotto rispetto a quello che passiamo noia testare qualsiasi tipo di query a cui stiamo lavorando, percui più avanti assumeremo che SQL Server non siaaffetto da bug, cosa non vera ma che lo diventa se paragonataalla qualità del nostro codice).

E' possibile creare ed eliminare indici, cambiare la lorotipologia (a cluster o meno), includere o escludere colonnedagli indici. Su SQL Server 2005 vi è una nuovaopzione di indicizzazione denominata 'INCLUDING'. Nonprovate ad indicizzare qualsiasi cosa: rallenterete gliaggiornamenti. Se SQL Server non fa uso degli indici,potrebbe esserci una ragione valida alla base.

Rischi potenziali: è una metodologiadel tutto sicura? Si lo è per la maggior parte deidatabase. SQL Server aggiusterà automaticamente ipiani di esecuzione in base ai nuovi indici. Comunque se ilvostro database oltre ad usare codice DML usa anche codiceDDL generando al volo tabelle ed indici, è possibileche alcune esecuzioni del tipo riportato qui sottofalliscano:

exec('drop index '+@indname)

Bisogna stare attenti dunque.

2. Hint e opzioni

Per usare gli hint, dovreste modificare il codice SQLesistente. Ne consegue che tale modifica dovrebbe comportarela ri-esecuzione dei test per tutto il sistema. Ma conl'assunzione che SQL Server non è affetto da bug,una query con degli hint produrrà esattamente glistessi risultati senza l'uso degli hint.

Per cui controllate il piano delle esecuzioni e consideratele seguenti espressioni:

• (index=...) -  state attenti, solitamente quando SQL Server ignora un indice c'è una buona ragione di fondo;
• OPTION (HASH JOIN) -  provate anche altri tipi di join;
• OPTION (FORCE ORDER);
• OPTION (FAST 1) - le tre opzioni fin qui riportate sono buoni candidati;
• (NOEXPAND) per le viste indicizzate;
• L'opzione WITH RECOMPILE per una stored procedure.

ed alcune altre espressioni...      

Ma se questo non vi aiuta, non c'è altro modo checambiare il codice.

Rischi potenziali:

• alcune modifiche potrebbero avere un impatto catastrofico sulla performance;
• alcune opzioni potrebbero ritornare un errore del tipo: 'Query processor could not produce a query plan because of the hints defined in this query.'
• alcune query potrebbero fallire su alcuni set di dati, controllare OPTION (ROBUST PLAN) per ulteriori dettagli.

3. Ottimizzazione del codice

L'ottimizzazione più primitiva è basatasolo sul testo di una query, senza nemmeno conoscere lastruttura del database. Ne consegue che le tipologie dimodifiche effettuabili saranno limitate, sebbene non cambinoil risultato finale in nessun caso. Per esempio, si consideriil codice seguente:

select 'Low',A,B from T  union  select 'High',A1,B1 from T1

In questo caso stiamo unendo due dataset e ci sbarazziamo deiduplicati. Ad ogni modo, non ci sono duplicati poichéla prima colonna è differente per le 2 sottoquery.Quindi possiamo riscrivere la query precedente come segue:

select 'Low',A,B from T  union all  select 'High',A1,B1 from T1

Un altro esempio:

update T set Flag='Y'update T set Ref=0

In entrambe le query modifichiamo tutti i record, per cuipossiamo cambiare entrambe le colonne allo stesso tempo:

update T set Flag='Y',Ref=0

Inoltre possiamo unire insieme gli aggiornamenti in casiancora più complicati:

update T set Flag='Y'  update T set Ref=0 where Comment like '%copied%'

entrambi gli aggiornamenti effettuano la scansionedell'intera tabella, per cui effettuiamo entrambi gliaggiornamenti durante lo stesso passo:

update T set Flag='Y', Ref = case when Comment like '%copied%'  then 0  else Ref -- keep the sameend

Una trappola: questo tipo di ottimizzazione può esserpericolosa. Per esempio,

update T set Ref=0  update T set Amount=StoredValue

E' quasi lo stesso caso visto in precedenza, per cuipossiamo riscrivere la query come segue:

update T set Ref=0,Amount=StoredValue

Corretto? Si, questa trasformazione è corretta nel 99%dei casi, ma non sempre. C'èl'eventualità che StoredValue possaessere una colonna calcolata che dipenda da Ref:ad esempio, ammettiamo che sia definita comeStoredValue=Ref+1.

L'espressione originaria setta Ref a 0 eAmount a 1. La nostra query ottimizzata lavorain modo differente, poiché prima di tutto UPDATEcalcola tutte le parti corrette e solo dopo le assegna allecolonne. Quindi la query setterà Ref a 0,ma Amount verrà modificata con il vecchiovalore di Ref incrementato di 1.

Rischi potenziali: come dimostrato poco fa,si dovrebbe testare il codice dopo ogni modifica. Tutti ilivelli superiori sono intrusivi e richiedono una verificadel codice.

4. Ottimizzazioni quando si è a conoscenza delloschema del database

Quando conosciamo lo schema del database e abbiamo una listadegli indici, possiamo effettuare modifiche piùsostanziose. Per esempio osserviamo il seguente codice:

update T set Flag='A' where Opt=1  update T set Flag='B' where Opt=2

Possiamo controllare se c'è un indice per lacolonna Opt. Assumendo che sia stato creato un indice su talecolonna con un'alta selettività, possiamo lasciareinalterata la query. Ad ogni modo, se non vi è alcunindice su Opt, possiamo riscrivere la query nel seguente modoper eseguire un update durante la stessa scansione:

update T  set Flag=case when Opt=1 then 'A' else 'B' end  where Opt in (1,2)

Rischi potenziali: si richiede di ri-testareil codice.

5. Abbiamo accesso ai dati e conosciamo il lorosignificato

Negli esempi visti fin'ora, potremmo determinare se Optè una colonna ad alta selettività. Per farlo,dovremmo confrontare i risultati di due query:

select count(*) from T  select count(distinct Opt) from T

Ciò che conta è che possiamo trovare dei valoricon selettività irregolare e trattarli propriamente.Si legga quest'articolo per sapernedi più a riguardo della selettività irregolare.

Talvolta i nomi delle tabelle e delle colonne possonoaiutarci, in modo tale da comprendere la situazione anchesenza osservare i dati attualmente presenti. In quali casisecondi voi, SQL Server usa un indice e dove invece usa unascansione intera della tabella?

update Patients set Flag='Y' where Gender='F'  update Sales set Flag='N' where Id=13848

Nel primo caso SQL Server percorre la scansione dellatabella, poiché la colonna Gender haselettività estremamente bassa (circa il 50%).Possiamo inoltre aggiungere che Flag è unvalore a bassa selettività in entrambi i casi (sia'Y' che 'N') eprobabilmente pochi altri. Ma sintatticamente, entrambe lequery sono identiche.

Un altro esempio. Si assuja che Gender abbia ilvincolo NOT NULL, l'espressione seguenteaggiorna tutti i record?

update Patients set Flag=0 where Gender in ('M','F')

Si. Ora cambiamo il nome della tabella:

update Nouns set Flag=0 where Gender in ('M','F')

Stiamo aggiornando un nome, che ha una categoria grammaticaledi Gender, che può essere 'M','F' o neutro! come si può vedere,l'unica differenza è il nome della tabella, quindiabbiamo dedotto il significato.

Possiamo basare le nostre ottimizzazioni in base a varivincoli reali quali:

• l'età è un intero positivo minore di 255;
• la massa è positiva;
• l'intero InternalId è correlato alla data di un documento;
• SSN è unico.

Rischi potenziali: si richiede non solo il testing ma anchela verifica che i vincoli non vengano violati ora (èpossibile verificarlo effettuando delle query sui datiesistenti) e nel futuro (alcuni dati potrebbero essereassenti al momento). Scoprirete che:

• il valore speciale -1 viene usato per l'età come flag particolare;
• la massa può esser negativa poiché talvolta si considera la differenza fra la massa attuale e quella che ci si aspetta;
• InternalId è correlato alla data dei documenti ad eccezione di quei documenti importati da un altro database un anno fa;
• SSN è unico ad eccezione del valore 'N/A', usato per i lavoratori clandestini.

Conclusioni e considerazioni finali

Alcune soluzioni hanno due aspetti: accelerano il recuperodei dati, per esempio, ma rallentano la loro modifica. Unabuona tecnica è quella delle viste indicizzate. Leviste indicizzate possono accelerare le selezioni, marallentare gli aggiornamenti ,soprattutto quando questi sonointensivi.

Quindi prima di effettuare qualsiasi modifica, sarebbe benesapere se i dati sono relativamente statici o meno, faredelle previsioni sulla frequenza degli aggiornamenti, ecc.

Per fare tali analisi sui dati che si ha a disposizione, unbuon tool è SQL Trace Analyzer.