Articolo di SQL Magazine 7 – SQL Server: turbina le tue query con le viste indicizzate

admin 0 Comments Articles

Articolo di SQL Magazine 7 – SQL Server: turbina le tue query con le viste indicizzate

Prendi la tua domanda Segna come completata Annota

Lo scopo di questo articolo è di introdurre il concetto di viste indicizzate di SQL Server e mostrare come implementare e usare questo tipo di vista per ottimizzare le query.

Concetto di vista

Le viste sono anche conosciute come “tabelle virtuali”, poiché presentano un’alternativa all’uso delle tabelle per accedere ai dati. Una vista non è altro che un’istruzione SELECT incapsulata in un oggetto. La sintassi per creare una vista è come mostrato nel listato 1.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

Vedi un esempio di creazione e uso della vista nel listato 1.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

Tra i vantaggi dell’uso delle viste, possiamo menzionare :

• Semplificazione del codice: si possono scrivere SELECT complesse una volta, incapsularle nella vista e attivarle da essa, come se fosse una tabella qualsiasi;
• Problemi di sicurezza: supponiamo di avere informazioni riservate in alcune tabelle e, quindi, si vuole che solo alcuni utenti abbiano accesso ad esse. Tuttavia, alcune colonne di queste tabelle devono essere accessibili a tutti gli utenti. Un modo efficace per risolvere questo problema è creare una vista e nascondere le colonne sensibili. In questo modo, si possono sopprimere i diritti di accesso alla tabella originale e rilasciare l’accesso alla vista;
• Possibilità di ottimizzare le query implementando le viste indicizzate.

Viste indicizzate in pratica

Le viste incapsulano le istruzioni SELECT, il che significa che ogni volta che vengono attivate, vengono eseguite le istruzioni SELECT ad esse associate. Le viste non creano depositi per i dati che restituiscono (come fa la tabella). Bene, sarebbe bello se potessimo “materializzare” in una tabella il risultato del comando SELECT trovato nella vista, creando degli indici per facilitare l’accesso ad esso. Bene, le viste indicizzate fanno proprio questo. L’esecuzione di una SELECT in una vista indicizzata ha lo stesso effetto dell’esecuzione di una select in una tabella convenzionale.

Lo scopo principale delle viste indicizzate è quello di aumentare le prestazioni, e il vantaggio di SQL Server è quello di permettere ai piani di esecuzione di considerare la vista indicizzata come un mezzo di accesso ai dati, anche se il nome della vista non era esplicito nella query. Questo è possibile nell’edizione Enterprise di SQL Server 2000, dove l’ottimizzatore di comandi può selezionare i dati direttamente nella vista indicizzata (invece di selezionare i dati grezzi esistenti nella tabella), come vedremo in seguito.

Creazione di una vista indicizzata passo dopo passo

1. Configurando l’ambiente, il primo passo è quello di impostare lo stato di alcuni parametri nella sessione in cui si vuole creare e utilizzare la vista, perché essendo la vista indicizzata “materializzata” in una tabella, nulla può interferire con il suo risultato. Immaginate, per esempio, il seguente scenario:
2. Una certa impostazione, che influisce sul risultato di una SELECT (per esempio concat_null_yelds_null), è impostata prima della creazione della vista indicizzata;
3. La vista indicizzata è creata; notate che il risultato della vista sarà ‘materializzato’ sul disco secondo l’impostazione impostata nel passo precedente;
4. Poi l’impostazione è disattivata e la vista indicizzata è eseguita dall’utente. Poiché la vista è stata materializzata, otterremo un risultato che non è coerente con la configurazione corrente.

Per esempio, il listato 2 mostra la differenza nel risultato di un comando quando la proprietà concat_null_yields_null viene modificata.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Immaginate cosa accadrebbe se la vista indicizzata fosse creata con la proprietà concat_null_yields_null abilitata, ma la sessione corrente fosse con tale proprietà disabilitata – la stessa SELECT porterebbe a risultati diversi!

Questo problema è stato risolto in modo semplice – per creare e utilizzare le viste indicizzate, è obbligatorio configurare l’ambiente secondo una lista di valori predefiniti. In questo modo, è impossibile ottenere risultati diversi, perché la vista semplicemente non funzionerà se una qualsiasi delle impostazioni è impostata su un valore diverso da quello predefinito.

La tabella 1 mostra queste impostazioni e i loro rispettivi valori predefiniti.

(*) L’id è usato nel comando sp_configure. Per controllare cosa fa ogni impostazione, leggi la sezione “Impostazioni necessarie per le viste indicizzate”. Ci sono due modi per cambiare il valore di una configurazione:

• Direttamente nella sessione: eseguire il comando set ON | OFF
• Modificare il default del server esistente: eseguire sp_Configure ‘user options’, . Il numero id di ogni configurazione può essere visto nella tabella 1.
  Nota: AritHabort ha id 64 e Quoted_Identifier ha id 256. Per collegare, per esempio, Quoted_Identifier + AritHabort, eseguiremmo sp_cofigure, passando come parametro il risultato di 64+256 (=320): sp_configure ‘user options’, 320. Per un elenco completo degli id assegnati ad ogni configurazione, andate a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Per confermare lo stato di ogni parametro nella tabella 1, usate la funzione SessionProperty(‘nome del parametro’) o il comando DBCC UserOptions.

In questo modo, impostate tutti i parametri secondo la colonna ‘stato richiesto per le viste indicizzate’ nella tabella 1 – se questo non viene fatto, SQL Server non vi permetterà di creare/eseguire la vista indicizzata.

Creazione della vista indicizzata

Creiamo una vista per totalizzare l’importo giornaliero venduto nella tabella Order Details, situata nel database NorthWind. Vedere il listato 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Abbiamo bisogno di osservare alcune particolarità quando creiamo viste indicizzate:

• La vista deve essere deterministica. Una vista sarà deterministica se usiamo solo funzioni deterministiche nel suo codice. Lo stesso comando SELECT eseguito ripetutamente su una vista indicizzata (considerando una base statica) non può dare risultati diversi. Le funzioni deterministiche assicurano che il risultato di una funzione rimanga invariato, non importa quante volte venga eseguita. La funzione DatePart, per esempio, è deterministica, poiché restituisce sempre lo stesso risultato per una data specifica. La funzione getdate() restituisce un valore diverso ogni volta che viene eseguita. Per la lista completa delle funzioni deterministiche di SQL Server 2000, andate a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Verificate che non ci siano restrizioni di sintassi. Le clausole, le funzioni e i tipi di query elencati di seguito non possono integrare lo schema di una vista indicizzata:
  • MIN, MAX,TOP
  • VARIANZA,STDEV,AVG
  • COUNT(*)
  • SUM su colonne che permettono valori nulli
  • DISTINCT
  • Funzione ROWSET
  • Tabelle derivate, self joins, subqueries, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext e image
  • COMPUTE e COMPUTE BY
  • HAVING, CUBE e ROLLUP

È necessario creare le viste indicizzate con SchemaBinding. Per mantenere il contenuto della vista coerente, non puoi cambiare la struttura delle tabelle che hanno dato origine alla vista. Per evitare questo tipo di problema, è obbligatorio usare SchemaBinding quando si creano viste indicizzate, perché questa opzione non permette di cambiare la struttura della tabella senza prima cancellare la vista.

Per usare la clausola GROUP BY, è obbligatorio includere la funzione COUNT_BIG(*). La funzione count_big(*) fa la stessa cosa di count(*), ma restituisce un valore di tipo bigint (8 byte).

Informa sempre il proprietario degli oggetti referenziati nella vista indicizzata. Usate select * from dbo.Orders invece di select * from Orders, poiché è possibile avere tabelle con lo stesso nome ma proprietari diversi. Poiché l’opzione schemabinding è obbligatoria, SQL Server ha bisogno della specifica esatta dell’oggetto per frenare l’alterazione dello schema.

Creazione di un indice cluster nella vista (materializzazione)

La vista creata nel punto 2 non si comporta ancora come una vista indicizzata, poiché il risultato del comando select non è stato materializzato in una tabella. Si può confermare questa affermazione eseguendo il comando sp_spaceused in Query Analyzer, che restituisce il numero di righe e lo spazio utilizzato dalle tabelle (Listato 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Listato 4. Utilizzo del comando sp_spaceused su una vista

Osservate nel Listato 5 che l’elaborazione della vista è puramente logica, tanto che il valore di Physical Reads è zero. Notate i valori registrati in Logical Reads e Physical Reads (1672+0+4+0=1676) – useremo questi valori nei nostri confronti futuri.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Listing 5. Total I/O associated with the view BEFORE creating the cluster index

Per controllare se la vista può essere indicizzata (materializzata), cioè se è stata creata secondo gli standard e le impostazioni richieste per le viste indicizzate, il risultato della seguente SELECT dovrebbe essere uguale a 1.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Confermando i prerequisiti, possiamo ora creare l’indice. La sintassi ha lo stesso formato usato quando si creano indici in tabelle convenzionali:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Nota che l’indice cluster è richiesto perché genera pagine di dati. È possibile creare indici non cluster nelle viste indicizzate solo dopo aver creato l’indice cluster.

Ora la SELECT trovata nella vista è stata materializzata. che può essere dimostrata con il comando sp_spaceused in Query Analyzer (Listato 6).

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Listato 6 . Usare il comando sp_spaceused su una vista indicizzata

Utilizzare le viste indicizzate

Un modo per accedere a una vista indicizzata (così come a una vista convenzionale) è fare riferimento al suo nome nel comando SELECT:

select * from vi_vendas_mes

Confrontate il volume di pagine spostate nel listato 5 (1672+4=1676) con quello del listato 7 (2+0=2). La differenza è abbastanza significativa – la creazione della vista indicizzata ha ridotto l’I/O totale richiesto di 1674 pagine.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Elenco 7. I/O totale associato alla vista dopo la creazione del cluster di indici

Guardiamo un altro esempio. La figura 1 mostra il piano di esecuzione di una query. Confermare che la vista è stata selezionata anche se non era presente nella riga SELECT.

Durante la costruzione del piano di esecuzione della query, l’ottimizzatore ha trovato che c’erano già dati pre-sommarizzati per la query in vi_sales_mes e ha scelto di selezionare i dati direttamente nella vista indicizzata.

Nota che la query eseguita nella figura 1 è identica a quella trovata nella vista vi_sales_mes. Tuttavia, l’accesso alla vista da parte dell’ottimizzatore è indipendente dalla somiglianza tra la query eseguita e la query della vista. La selezione della vista indicizzata da parte del processore di query tiene conto solo del costo-beneficio. In questo modo, le query eseguite non devono necessariamente essere identiche alla vista (osservare la figura 3).

Tuttavia, è necessario seguire alcune regole perché la vista indicizzata sia considerata dall’ottimizzatore di query:

• Il join presente nella vista deve “essere contenuto” nella query: se la query esegue un join tra le tabelle A e B, e la vista esegue un join tra A e C, la vista non sarà attivata nel piano di esecuzione. Tuttavia, se la query esegue un join tra A,B e C, la vista può essere attivata.
• Le condizioni impostate nella query devono concordare con le condizioni nella vista: nella SELECT in Figura 2, la vista indicizzata vi_sales_mes non sarà considerata perché la clausola where non era presente nel codice della vista, che ha causato il calcolo delle righe con Quantità <= 5 nel join.

D’altra parte, se la query ha la condizione where dove sum(Quantity) > 5, la vista vi_sales_mes sarà considerata nel piano di esecuzione, perché la condizione della query è un sottoinsieme della SELECT presente nella vista.

Le colonne con funzioni di aggregazione nella query devono “essere contenute” nella definizione della vista: se la vista restituisce la colonna qtde=sum(Quantity) e la query ha una colonna vlr_unit=sum(UnitPrice), la vista non sarà considerata.

La figura 3 mostra un comando SELECT che permette di provare l’intelligenza dell’ottimizzatore di comandi – il calcolo AVG(Quantity) è stato sostituito dalla divisione tra SUM(Quantity) / Count_Big(*), rappresentata dall’icona Compute Scalar. Il predicato dove sum(Quantity) > 1500 (rappresentato dall’icona Filter) è anche considerato.

Considerazioni generali sull’uso delle viste indicizzate:

• Le viste indicizzate possono essere create in qualsiasi versione di SQL Server 2000. Tuttavia, solo nella versione Enterprise Edition saranno selezionati automaticamente dall’ottimizzatore di query.
• Nelle versioni diverse da Enterprise, è necessario utilizzare il suggerimento NoExpand per accedere alla vista indicizzata come una tabella convenzionale. Se NoExpand non è impiegato, la vista indicizzata sarà considerata una vista “normale”.
  Nota: il suggerimento Expand fa il contrario di NoExpand: tratta la vista indicizzata come una vista “normale”, costringendo SQL Server ad eseguire l’istruzione SELECT durante la fase di esecuzione.
• La manutenzione di una vista indicizzata è automatica (come per gli indici); non richiede alcuna sincronizzazione aggiuntiva. Per la sua stessa caratteristica (di solito memorizza dati pre-sommarizzati), il suo aggiornamento tende ad essere un po’ più lento di quello degli indici convenzionali.
• L’uso delle viste indicizzate nelle basi OLTP richiede cautela, perché anche se presentano grandi prestazioni nelle query, causano overhead nei processi che modificano le tabelle collegate nella vista. In situazioni in cui sono richieste alte prestazioni di scrittura, con aggiornamenti frequenti, la creazione di viste indicizzate non è raccomandata.
• Come fa con gli indici, l’ottimizzatore analizzerà il codice della vista nelle versioni Enterprise come parte del processo di scelta del miglior piano di esecuzione per una query. Tuttavia, se ci sono molte viste indicizzate suscettibili di esecuzione per la stessa query, ci può essere un aumento sostanziale di questo tempo di scelta, poiché tutte le viste saranno analizzate. Perciò, usate il buon senso quando implementate le viste.

Impostazioni richieste per le viste indicizzate

ANSI_NULLS

Definisce come vengono eseguiti i confronti con valori nulli (listato 8).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO

Listato 8. Esempi di ansi_null

ANSI_PADDING

Determina come le colonne char, varchar, binary e varbinary dovrebbero essere memorizzate quando il loro contenuto è più piccolo della dimensione definita nella struttura della tabella. Il default di SQL Server 2000 è di mantenere ansi_padding on (=ON); in questa condizione si applicano le seguenti regole:

• Quando si aggiornano colonne di char, gli spazi bianchi saranno aggiunti alla fine della stringa se è più piccola della dimensione definita nella struttura della colonna. La stessa regola si applica alle colonne binarie (in questo caso, lo spazio è riempito da una sequenza di zeri)
• Le colonne varbinarie o vararchar non seguono la regola precedente: mantengono sempre la loro dimensione originale.

ARITHABORT

Quando abilitato, termina l’esecuzione della query quando incontra una divisione per zero o qualche tipo di overflow.

QUOTED_IDENTIFIER

Quando abilitato, permette l’uso di doppi apici per specificare nomi di tabelle, colonne ecc. – In questo modo, quei nomi possono avere spazi e o caratteri speciali.

CONCAT_NULL_YELDS_NULL

Controlla il risultato della concatenazione di stringhe con valori nulli. Se abilitato, determina che questo join deve restituire un valore nullo; altrimenti, restituirà la stringa stessa.

ANSI_WARNINGS

Se abilitato, determina la generazione di messaggi di errore quando:

1. si usano funzioni di riepilogo e si trovano valori nulli nell’intervallo della query;
2. si trovano divisioni per zero o overflow aritmetico.

NUMERIC_ROUNDABORT

Controlla come SQL Server dovrebbe procedere quando incontra una perdita di precisione numerica nelle operazioni aritmetiche. Se il parametro è abilitato e una variabile con precisione di due cifre decimali riceve un valore con tre cifre decimali, l’operazione sarà interrotta. Se il parametro è disabilitato, il valore sarà troncato a due cifre decimali.

Conclusione

Quando si tratta di ottimizzare le query, le viste indicizzate sono una buona scelta per sfruttare le prestazioni. Quindi, valutate accuratamente le query che hanno a che fare con le sintesi e che vengono eseguite con una certa frequenza e andate a creare delle viste indicizzate. Il risultato vale la pena!

.