• Articolo

Progettazione di viste indicizzate

Le viste sono denominate anche tabelle virtuali perché la forma generica del set di risultati restituito da una vista è quella di una tabella con righe e colonne e i riferimenti alle viste inclusi nelle istruzioni SQL sono simili a quelli delle tabelle. Il set di risultati di una vista standard non viene archiviato in modo permanente nel database. Ogni volta che una query fa riferimento a una vista standard, SQL Server sostituisce internamente la definizione della vista nella query in modo da creare una query modificata che fa riferimento unicamente alle tabelle di base. La query risultante viene quindi eseguita normalmente. Per ulteriori informazioni, vedere Risoluzione delle viste.

Nel caso di una vista standard, l'overhead generato dalla creazione dinamica del set di risultati di ogni query che fa riferimento a una vista può essere notevole, specialmente con le viste che prevedono un'elaborazione complessa di un numero elevato di righe, ad esempio l'aggregazione di grandi quantità di dati o l'unione in join di numerose righe. Se le query includono spesso riferimenti a questo tipo di viste, è possibile migliorarne le prestazioni creando un indice cluster univoco nella vista. In questo caso, il set di risultati viene archiviato nel database allo stesso modo di una tabella con un indice cluster.

Un altro vantaggio della creazione di un indice in una vista è rappresentato dal fatto che Query Optimizer inizia a utilizzare l'indice della vista nelle query che non specificano direttamente il nome della vista nella clausola FROM. Le query esistenti possono trarre vantaggio dalla maggiore efficienza determinata dal fatto che i dati vengono recuperati dalla vista indicizzata senza che sia necessario riscriverle. Per ulteriori informazioni, vedere Risoluzione di indici nelle viste.

Man mano che vengono apportate modifiche ai dati delle tabelle di base, vengono modificati anche i dati archiviati nella vista indicizzata. Il requisito di unicità dell'indice cluster della vista migliora l'efficienza con la quale SQL Server trova le righe dell'indice che sono interessate dalle modifiche apportate ai dati.

Rispetto alle versioni precedenti, sono stati apportati miglioramenti alle funzionalità di Query Optimizer che consentono di sfruttare le viste indicizzate durante l'elaborazione delle query quando sia la query che la definizione della vista contengono gli elementi corrispondenti illustrati di seguito:

  • Espressioni scalari. Ad esempio, si consideri la query seguente con un'espressione scalare nel predicato:

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    In questo caso Query Optimizer è in grado di trovare un indice creato corrispondente per la vista:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    È inoltre possibile utilizzare meccanismi simili per trovare le corrispondenze di espressioni scalari che includono funzioni definite dall'utente.

  • Funzioni di aggregazione scalari. Ad esempio, la query seguente contiene una funzione di aggregazione scalare nell'elenco SELECT:

    SELECT COUNT_BIG (*) FROM dbo.TableT

    È possibile trovare un indice creato corrispondente per la vista:

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

Durante la selezione di un piano della query, Query Optimizer prenderà inoltre in considerazione i fattori seguenti:

  • La presenza o meno di un intervallo di valori definito in un predicato della query che rientra in un intervallo definito in una vista indicizzata. Si consideri, ad esempio, un indice creato per la vista seguente:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    Si consideri ora la query seguente:

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    Query Optimizer farà corrispondere la query con la vista V1 perché l'intervallo compreso tra 3 e 7 definito nella query rientra nell'intervallo tra 1 e 10 definito nella vista indicizzata.

  • Il livello di equivalenza tra un'espressione definita in una query e un'espressione definita in una vista indicizzata. Per far corrispondere le espressioni, SQL Server prende in considerazione i relativi riferimenti alle colonne, i valori letterali, gli operatori logici AND, OR, NOT, BETWEEN e IN, e gli operatori di confronto =, <>, >, <, >= e <=. Gli operatori aritmetici, ad esempio + e %, e i parametri non vengono considerati.

    Ad esempio, Query Optimizer farà corrispondere la query seguente:

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    con un indice creato per la vista seguente:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

Tenere presente che, come accade per tutti gli indici, SQL Server sceglierà di utilizzare una vista indicizzata nel piano di query solo se Query Optimizer ha stabilito se ciò può risultare utile o meno.

Le viste indicizzate possono essere create con qualsiasi versione di SQL Server 2008. In SQL Server 2008 Enterprise Edition la vista indicizzata viene automaticamente presa in considerazione in Query Optimizer. Per utilizzare le viste indicizzate in tutte le altre edizioni, è necessario utilizzare l'hint di tabella NOEXPAND.

Linee guida per la progettazione di una vista indicizzata

Le prestazioni delle viste indicizzate sono ottimali se i dati sottostanti vengono modificati di rado. Le operazioni di manutenzione richieste da una vista indicizzata possono infatti essere maggiori di quelle richieste da un indice di tabella. Se i dati sottostanti vengono modificati di frequente, i costi di manutenzione dei dati possono annullare i vantaggi a livello di prestazioni offerti dall'utilizzo della vista indicizzata. Se i dati sottostanti vengono aggiornati periodicamente ma vengono trattati come dati di sola lettura nei periodi che intercorrono tra gli aggiornamenti, è consigliabile eliminare le viste indicizzate prima di eseguire gli aggiornamenti e ricostruirle successivamente. Ciò consente di migliorare le prestazioni degli aggiornamenti.

Le viste indicizzate consentono di ottimizzare le prestazioni dei tipi di query seguenti:

  • Join e aggregazioni che elaborano un numero elevato di righe.

  • Operazioni join e di aggregazione eseguite di frequente da un numero elevato di query.

    Ad esempio, in un database OLTP (Online Transaction Processing) in cui vengono registrate le scorte, si potrebbe presumere che le tabelle ProductMaster, ProductVendor e VendorMaster vengano unite in join da numerose query. È possibile che ogni query che esegue il join non elabori un numero di righe molto elevato, tuttavia l'elaborazione di join globale associata a migliaia di query di questo tipo può essere notevole. È improbabile che queste relazioni vengano aggiornate di frequente, pertanto è possibile ottimizzare le prestazioni globali del sistema definendo una vista indicizzata in cui vengono archiviati i risultati uniti in join.

  • Carichi di lavoro del supporto decisionale.

    I sistemi di analisi sono caratterizzati dall'archiviazione di dati di riepilogo aggregati che vengono aggiornati di rado. Le query per il supporto decisionale sono inoltre caratterizzate dall'aggregazione dei dati e dall'unione in join di un numero elevato di righe. I sistemi di supporto decisionale a volte includono inoltre tabelle di grandi dimensioni con un numero elevato di colonne oppure colonne di grandi dimensioni o entrambe.. Le query che fanno riferimento a un subset limitato di tali colonne possono trarre vantaggio da una vista indicizzata che include solo le colonne della query o da un superset limitato di tali colonne. La creazione di viste indicizzate limitate che contengono un subset delle colonne di una singola tabella è denominata anche partizionamento verticale, perché suddivide le tabelle in senso verticale. Si considerino, ad esempio, la tabella e la vista indicizzata seguenti:

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    Per rispondere alla query seguente è sufficiente utilizzare v_abc:

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    L'utilizzo di v_abc per la vista occupa un numero minore di pagine rispetto all'utilizzo di wide_tbl per la tabella. È pertanto consigliabile utilizzarlo come percorso di accesso per risolvere la query precedente.

    Per suddividere in senso verticale un'intera tabella, anziché un subset della tabella, è consigliabile utilizzare un indice non cluster nella tabella che utilizza una clausola INCLUDE per includere solo le colonne desiderate, anziché una vista indicizzata. Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

Le viste indicizzate in genere non ottimizzano le prestazioni dei tipi di query seguenti:

  • Sistemi OLTP in cui vengono eseguite numerose operazioni di scrittura.

  • Database in cui vengono eseguiti aggiornamenti frequenti.

  • Query che non prevedono aggregazioni o join.

  • Aggregazioni di dati con un livello elevato di cardinalità per la chiave GROUP BY. Un livello elevato di cardinalità implica che la chiave contiene numerosi valori diversi. Una chiave univoca ha il livello di cardinalità più elevato possibile perché a ogni chiave corrisponde un valore diverso. Le viste indicizzate ottimizzano le prestazioni perché riducono il numero di righe alle quali deve accedere una query. Se il numero di righe nel set dei risultati della vista è quasi uguale a quello della tabella di base, i vantaggi per le prestazioni offerti dall'utilizzo della vista sono minimi. Si consideri, ad esempio, la query seguente per una tabella che include 1.000 righe:

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    Se la cardinalità della chiave della tabella è 100, una vista indicizzata creata utilizzando il risultato della query includerà solo 100 righe. Per le query che utilizzano la vista, la quantità di operazioni di lettura necessarie sarà pari a un decimo delle operazioni di lettura per la tabella di base. Se la chiave è univoca, la cardinalità della chiave sarà 1000 e il set di risultati della vista restituirà 1000 righe. Se la dimensione delle righe della vista e della tabella di base ExampleTable è uguale, l'utilizzo di una vista indicizzata non offrirà alcun vantaggio per le prestazioni rispetto alla lettura diretta della tabella di base.

  • Join in espansione. Si tratta di viste con set di risultati di dimensioni maggiori rispetto ai dati originali delle tabelle di base.

Combinazione di viste indicizzate tramite l'utilizzo di query

Le restrizioni valide per i tipi di viste che è possibile indicizzare potrebbero impedire di progettare una vista in grado di risolvere un problema specifico, tuttavia è possibile progettare più viste indicizzate di dimensioni minori che consentono di velocizzare parti del processo.

Si considerino gli esempi seguenti:

  • Una query eseguita di frequente aggrega i dati in un database, quindi aggrega i dati in un altro database e infine unisce in join i risultati. Una vista indicizzata non può fare riferimento a tabelle di più database, pertanto non è possibile progettare una singola vista che esegue l'intero processo. È tuttavia possibile creare in ogni database una vista indicizzata che esegue l'aggregazione per il database specifico. Se in Query Optimizer è possibile far corrispondere le viste indicizzate con query esistenti, il processo di aggregazione verrà velocizzato senza che sia necessario riscrivere le query esistenti. L'elaborazione dei join non sarà più rapida, tuttavia la query sarà più veloce perché utilizza le aggregazioni archiviate nelle viste indicizzate.

  • Una query eseguita di frequente aggrega i dati di numerose tabelle e quindi utilizza UNION per combinare i risultati. L'utilizzo di UNION non è consentito in una vista indicizzata. Anche in questo caso è possibile progettare le viste in modo che ognuna di esse esegua operazioni di aggregazione. È quindi possibile utilizzare Query Optimizer per selezionare le viste indicizzate e velocizzare le query senza che sia necessario riscriverle. L'elaborazione di UNION non è stata migliorata, ma è stata migliorata l'elaborazione dei singoli processi di aggregazione.

Progettare viste indicizzate che sono in grado di eseguire più operazioni. Query Optimizer può utilizzare una vista indicizzata anche se non è specificata nella clausola FROM e pertanto una vista indicizzata ben progettata può velocizzare l'elaborazione di numerose query.

Si consideri, ad esempio, la creazione di un indice nella vista seguente:

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

Questa vista può non solo eseguire le query che fanno direttamente riferimento alle colonne della vista, ma consente inoltre di eseguire query sulla tabella di base che contengono espressioni quali SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) e AVG(Colx). Queste query saranno più rapide perché devono solo recuperare un numero limitato di righe della vista anziché leggere tutte le righe delle tabelle di base.

Allo stesso modo, per eseguire query che eseguono l'aggregazione su diversi intervalli maggiori di 1 giorno, ad esempio di 7, 30 o 90 giorni, è possibile utilizzare una vista indicizzata che aggrega i dati e i gruppi per giorno.