Christian Castelli
a- a+

Ottimizzare query SQL: velocizzare l'esecuzione

Ottimizzazione delle query SQL

L'ottimizzazione SQL prevede una trasformazione delle espressioni SQL che produca esattamente lo stesso risultato delle 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 in un solo piccolo articolo, per cui ci limiteremo a fare delle classificazioni delle ottimizzazioni possibili, in modo tale che siate preparati per sapere in che modo sia possibile ottimizzare un database.

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

Tutte le ottimizzazioni sono elencate in ordine crescente di intrusività.


0. Ottimizzazioni non intrusive

Riguardano l'aggiornamento e il potenziamento dell'hardware del SQL server e la modifica di alcuni paramentri del server. L'aggiunta di banchi di memoria o di un processore non creeranno danni e non necessiteranno di ri-testare i propri prodotti.

Il potenziamento a livello hardware potrebbe essere inefficace, poiché è possibile che il serber soffra di Locks, problemi IO o di CPU. Un modo semplice per determinare i punti critici del server è attraverso i tool di Lakeside.

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

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

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

Per quanto concerne le modifiche hardware riportate e applicando tutte le possibili raccomandazioni rintracciabili in Rete, è possibile guadagnare 20-30% in più delle prestazioni, anche meno se non c'è nulla di sbagliato nel vostro server. Perciò se le vostre query cominciano ad essere eseguite sempre più lentamente di ciò che vi aspettate, non avete altra scelta che cambiare il codice TSQL.

Rischi potenziali: nessuno.


1. Indici

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

Sicuramente ogni DBA farà una certa opposizione riguardo alla modifica del codice, specialmente a livello di produzione. In alcuni casi è possibile effettuare tali modifiche senza considerarle vere modifiche a livello di programmazione convenzionale.

In primo luogo, tutto quello che si può fare con gli indici può incidere su piano dell'esecuzione, garantendo lo stesso risultato (a meno che non ci siano dei bug in SQL Server, ma Microsoft passa molto più tempo a testare il loro prodotto rispetto a quello che passiamo noi a testare qualsiasi tipo di query a cui stiamo lavorando, per cui più avanti assumeremo che SQL Server non sia affetto da bug, cosa non vera ma che lo diventa se paragonata alla qualità del nostro codice).

E' possibile creare ed eliminare indici, cambiare la loro tipologia (a cluster o meno), includere o escludere colonne dagli indici. Su SQL Server 2005 vi è una nuova opzione di indicizzazione denominata 'INCLUDING'. Non provate ad indicizzare qualsiasi cosa: rallenterete gli aggiornamenti. Se SQL Server non fa uso degli indici, potrebbe esserci una ragione valida alla base.

Rischi potenziali: è una metodologia del tutto sicura? Si lo è per la maggior parte dei database. SQL Server aggiusterà automaticamente i piani di esecuzione in base ai nuovi indici. Comunque se il vostro database oltre ad usare codice DML usa anche codice DDL generando al volo tabelle ed indici, è possibile che alcune esecuzioni del tipo riportato qui sotto falliscano:

exec('drop index '+@indname)

Bisogna stare attenti dunque.


2. Hint e opzioni

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

Per cui controllate il piano delle esecuzioni e considerate le 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 che cambiare 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 è basata solo sul testo di una query, senza nemmeno conoscere la struttura del database. Ne consegue che le tipologie di modifiche effettuabili saranno limitate, sebbene non cambino il risultato finale in nessun caso. Per esempio, si consideri il 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 dei duplicati. 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 cui possiamo cambiare entrambe le colonne allo stesso tempo:

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

Inoltre possiamo unire insieme gli aggiornamenti in casi ancora più complicati:

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

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

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

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

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

E' quasi lo stesso caso visto in precedenza, per cui possiamo 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 possa essere una colonna calcolata che dipenda da Ref: ad esempio, ammettiamo che sia definita come StoredValue=Ref+1.

L'espressione originaria setta Ref a 0 e Amount a 1. La nostra query ottimizzata lavora in modo differente, poiché prima di tutto UPDATE calcola tutte le parti corrette e solo dopo le assegna alle colonne. Quindi la query setterà Ref a 0, ma Amount verrà modificata con il vecchio valore di Ref incrementato di 1.

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


4. Ottimizzazioni quando si è a conoscenza dello schema del database

Quando conosciamo lo schema del database e abbiamo una lista degli 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 la colonna Opt. Assumendo che sia stato creato un indice su tale colonna con un'alta selettività, possiamo lasciare inalterata la query. Ad ogni modo, se non vi è alcun indice su Opt, possiamo riscrivere la query nel seguente modo per 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-testare il codice.


5. Abbiamo accesso ai dati e conosciamo il loro significato

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 valori con selettività irregolare e trattarli propriamente. Si legga quest'articolo per saperne di più a riguardo della selettività irregolare.

Talvolta i nomi delle tabelle e delle colonne possono aiutarci, in modo tale da comprendere la situazione anche senza osservare i dati attualmente presenti. In quali casi secondi voi, SQL Server usa un indice e dove invece usa una scansione 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 della tabella, poiché la colonna Gender ha selettività estremamente bassa (circa il 50%). Possiamo inoltre aggiungere che Flag è un valore a bassa selettività in entrambi i casi (sia 'Y' che 'N') e probabilmente pochi altri. Ma sintatticamente, entrambe le query sono identiche.

Un altro esempio. Si assuja che Gender abbia il vincolo NOT NULL, l'espressione seguente aggiorna 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 grammaticale di Gender, che può essere 'M', 'F' o neutro! come si può vedere, l'unica differenza è il nome della tabella, quindi abbiamo dedotto il significato.

Possiamo basare le nostre ottimizzazioni in base a vari vincoli 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 anche la verifica che i vincoli non vengano violati ora (è possibile verificarlo effettuando delle query sui dati esistenti) e nel futuro (alcuni dati potrebbero essere assenti 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 recupero dei dati, per esempio, ma rallentano la loro modifica. Una buona tecnica è quella delle viste indicizzate. Le viste indicizzate possono accelerare le selezioni, ma rallentare gli aggiornamenti ,soprattutto quando questi sono intensivi.

Quindi prima di effettuare qualsiasi modifica, sarebbe bene sapere se i dati sono relativamente statici o meno, fare delle previsioni sulla frequenza degli aggiornamenti, ecc.

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



Ti potrebbe interessare anche

commenta la notizia

C'Ŕ 1 commento
Francesco
Hai dubbi su questo articolo?