Ottimizzazione delle prestazioni con viste materializzate

Le viste materializzate per i pool SQL dedicati in Azure Synapse offrono un metodo di manutenzione ridotto per query analitiche complesse per ottenere prestazioni rapide senza alcuna modifica delle query. Questo articolo illustra le linee guida generali sull'uso di viste materializzate.

Viste materializzate e viste standard

Il pool SQL dedicato in Azure Synapse supporta viste standard e materializzate. Entrambe sono tabelle virtuali create con espressioni SELECT e presentate alle query come tabelle logiche. Le viste incapsulano la complessità del calcolo dei dati comune e aggiungono un livello di astrazione alle modifiche di calcolo, quindi non è necessario riscrivere le query.

Una vista standard calcola i dati ogni volta che viene usata la vista. Non sono presenti dati archiviati su disco. Gli utenti usano in genere viste standard come strumento che consente di organizzare gli oggetti logici e le query in un pool SQL dedicato. Per usare una vista standard, una query deve farvi riferimento diretto.

Una vista materializzata pre-calcola, archivia e gestisce i dati in un pool SQL dedicato esattamente come una tabella. Non è necessaria alcuna ricompilazione ogni volta che viene usata una vista materializzata. Ecco perché le query che usano tutti o un sottoinsieme dei dati nelle viste materializzate possono ottenere prestazioni più veloci. Ancora meglio, le query possono usare una vista materializzata senza farvi riferimento diretto, quindi non è necessario modificare il codice dell'applicazione.

La maggior parte dei requisiti di una visualizzazione standard è ancora applicabile a una vista materializzata. Per informazioni dettagliate sulla sintassi della vista materializzata e altri requisiti, vedere CREATE MATERIALIZED VIEW AS SELECT

Vantaggi dell'uso di viste materializzate

Una visualizzazione materializzata progettata correttamente offre i vantaggi seguenti:

• Ridurre il tempo di esecuzione per query complesse con JOIN e funzioni di aggregazione. Più complessa è la query, maggiore è il potenziale per il risparmio di tempo di esecuzione. Il vantaggio maggiore è ottenuto quando il costo di calcolo di una query è elevato e il set di dati risultante è ridotto.
• L'utilità di ottimizzazione nel pool SQL dedicato può usare automaticamente le viste materializzate distribuite per migliorare i piani di esecuzione delle query. Questo processo è trasparente per gli utenti, offre prestazioni di query più veloci e non richiede che le query facciano riferimento diretto alle viste materializzate.
• Richiedono una manutenzione ridotta delle visualizzazioni. Tutte le modifiche incrementali ai dati delle tabelle di base vengono aggiunte automaticamente alle viste materializzate in modo sincrono, ovvero le tabelle di base e le viste materializzate vengono aggiornate nella stessa transazione. Questa progettazione consente di eseguire query sulle viste materializzate per restituire gli stessi dati delle query direttamente sulle tabelle di base.
• I dati in una vista materializzata possono essere distribuiti in modo diverso dalle tabelle di base.
• I dati nelle viste materializzate ottengono gli stessi vantaggi di disponibilità elevata e resilienza dei dati nelle tabelle normali.

Le viste materializzate implementate nel pool SQL dedicato offrono anche i vantaggi seguenti:

Rispetto ad altri provider di data warehouse, le viste materializzate implementate nel pool SQL dedicato offrono anche i vantaggi seguenti:

• Ampio supporto per le funzioni di aggregazione. Vedere CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• Supporto delle raccomandazioni delle viste materializzate specifiche per le query. Vedere EXPLAIN (Transact-SQL).
• Aggiornamento automatico e sincrono dei dati con modifiche ai dati nelle tabelle di base. Non è richiesta alcuna azione da parte dell'utente.

Scenari comuni

Le viste materializzate vengono in genere usate negli scenari seguenti:

Necessità di migliorare le prestazioni di query analitiche complesse su dati di grandi dimensioni

Le query analitiche complesse utilizzano generalmente più funzioni di aggregazione e join di tabelle, provocando operazioni con elevato carico computazionale, come la riorganizzazione dei dati e i join nell'esecuzione delle query. Ecco perché il completamento di query analitiche complesse richiede più tempo, soprattutto nelle tabelle di grandi dimensioni.

Gli utenti possono creare viste materializzate per i dati restituiti dai calcoli comuni delle query, quindi non è necessaria alcuna ricompilazione quando questi dati sono necessari per le query, consentendo un costo di calcolo inferiore e una risposta di query più rapida.

Ottenere prestazioni più veloci senza o con modifiche minime alle query

Le modifiche dello schema e delle query nei pool SQL dedicati vengono in genere mantenute al minimo per supportare le normali operazioni ETL e la creazione di report. Gli utenti possono usare viste materializzate per l'ottimizzazione delle prestazioni delle query, se il costo addebitato dalle viste può essere compensato dal miglioramento delle prestazioni delle query.

Rispetto ad altre opzioni di ottimizzazione, ad esempio il ridimensionamento e la gestione delle statistiche, creare e gestire una vista materializzata rappresentano una modifica di produzione meno incisiva, che inoltre offre un potenziale di miglioramento delle prestazioni superiore.

• La creazione o la gestione di viste materializzate non influisce sulle query in esecuzione sulle tabelle di base.
• Query Optimizer può usare automaticamente le viste materializzate distribuite senza un riferimento diretto alla vista in una query. Questa funzionalità riduce la necessità di modificare le query nell'ottimizzazione delle prestazioni.

È necessaria una strategia di distribuzione dei dati diversa per ottenere prestazioni di query più veloci

Il pool SQL dedicato è un sistema di elaborazione query distribuito. I dati in una tabella SQL vengono distribuiti fino a 60 nodi usando una delle tre strategie di distribuzione (hash, round_robin o replicate).

La distribuzione dei dati viene specificata al momento della creazione della tabella e rimane invariata fino a quando la tabella non viene eliminata. La vista materializzata, essendo una tabella virtuale su disco, supporta le distribuzioni hash e round robin dei dati. Gli utenti possono scegliere una distribuzione dei dati diversa dalle tabelle di base, ma ottimale per le prestazioni delle query che usano le viste.

Indicazioni per la progettazione

Ecco le indicazioni generali sull'uso di viste materializzate per migliorare le prestazioni delle query:

Progetto per il tuo workload

Prima di iniziare a creare viste materializzate, è importante avere una conoscenza approfondita del carico di lavoro in termini di modelli di query, importanza, frequenza e dimensioni dei dati risultanti.

Gli utenti possono eseguire EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> per le viste materializzate consigliate dal Query Optimizer. Poiché queste raccomandazioni sono specifiche delle query, una vista materializzata che offre vantaggi a una singola query potrebbe non essere ottimale per altre query nello stesso carico di lavoro.

Valutare queste raccomandazioni tenendo conto delle esigenze del carico di lavoro. Le viste materializzate ideali sono quelle che traggono vantaggio dalle prestazioni del carico di lavoro.

Tenere presente il compromesso tra query più veloci e il costo

Per ogni vista materializzata, è previsto un costo di archiviazione dei dati e un costo per la gestione della vista. Man mano che i dati cambiano nelle tabelle di base, le dimensioni della vista materializzata aumentano e cambia anche la struttura fisica. Per evitare una riduzione delle prestazioni delle query, ogni vista materializzata viene mantenuta separatamente dal motore SQL.

Il carico di lavoro di manutenzione aumenta quando aumenta il numero di viste materializzate e modifiche alla tabella di base. Gli utenti devono verificare se i costi sostenuti da tutte le viste materializzate possono essere compensati dal miglioramento delle prestazioni della query.

È possibile eseguire questa query per generare un elenco di viste materializzate in un pool SQL dedicato:

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

Opzioni per ridurre il numero di viste materializzate:

• Identificare i set di dati comuni usati di frequente dalle query complesse nel carico di lavoro. Creare viste materializzate per archiviare tali set di dati in modo che l'ottimizzatore possa usarle come blocchi predefiniti durante la creazione di piani di esecuzione.

• Eliminare le viste materializzate con un utilizzo ridotto o che non sono più necessarie. Una vista materializzata disabilitata non richiede manutenzione, ma comporta comunque un costo di archiviazione.

• Combinare viste materializzate create nelle stesse tabelle di base o simili anche se i dati non si sovrappongono. La combinazione di viste materializzate può comportare una visualizzazione più grande rispetto alla somma delle visualizzazioni separate, ma il costo di manutenzione della visualizzazione dovrebbe ridurre. Per esempio:

-- Query 1 would benefit from having a materialized view created with this SELECT statement

SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement

SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single materialized view of this form

SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

Non tutte le prestazioni di ottimizzazione richiedono modifiche alle query

Query Optimizer SQL può usare automaticamente le viste materializzate distribuite per migliorare le prestazioni delle query. Questo supporto viene applicato in modo trasparente alle query che non fanno riferimento alle viste e alle query che usano aggregazioni non supportate nella creazione di viste materializzate. Non è necessaria alcuna modifica della query. È possibile controllare il piano di esecuzione stimato di una query per verificare se viene usata una vista materializzata.

Monitorare le viste materializzate

Una vista materializzata viene archiviata nel pool SQL dedicato esattamente come una tabella con un indice columnstore cluster (CCI). La lettura dei dati da una vista materializzata include l'analisi dei segmenti di indice CCI e l'applicazione di eventuali modifiche incrementali dalle tabelle di base. Quando il numero di modifiche incrementali è troppo elevato, la risoluzione di una query da una vista materializzata può richiedere più tempo rispetto all'esecuzione diretta di query sulle tabelle di base.

Per evitare una riduzione delle prestazioni delle query, è consigliabile eseguire DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD per monitorare il overhead_ratio della visualizzazione (total_rows/max(1, base_view_row)). Se il rapporto di overhead è troppo elevato, gli utenti dovrebbero ricompilare la vista materializzata con REBUILD.

Visualizzazione materializzata e memorizzazione nella cache dei set di risultati

Queste due funzionalità nel pool SQL dedicato vengono usate per l'ottimizzazione delle prestazioni delle query. La memorizzazione nella cache dei set di risultati viene usata per ottenere una concorrenza elevata e una risposta rapida da query ripetitive su dati statici.

Per usare il risultato memorizzato nella cache, la forma della query che richiede la cache deve corrispondere alla query che ha prodotto la cache. Inoltre, il risultato memorizzato nella cache deve essere applicato all'intera query.

Le viste materializzate consentono modifiche ai dati nelle tabelle di base. I dati nelle viste materializzate possono essere applicati a una parte di una query. Questo supporto consente di usare le stesse viste materializzate da query diverse che condividono alcuni calcoli per ottenere prestazioni più veloci.

Esempio

Questo esempio usa una query simile a TPCDS che trova i clienti che spendono più denaro tramite catalogo rispetto ai negozi, identificano i clienti preferiti e il paese/area geografica di origine. La query prevede la selezione dei primi 100 record risultanti dall'esecuzione di un'istruzione UNION su tre istruzioni sub-SELECT che includono operazioni SUM() e GROUP BY.

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

Verifica il piano di esecuzione stimato della query. Sono presenti 18 operazioni di shuffle e 17 join, la cui esecuzione richiede più tempo. Ora verrà creata una vista materializzata per ciascuna delle tre dichiarazioni sub-SELECT.

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

Controllare di nuovo il piano di esecuzione della query originale. Ora il numero di join passa da 17 a 5 e non c'è nessuna riorganizzazione. Selezionare l'icona dell'operazione di filtro nel piano e l'elenco di output mostra che i dati vengono letti dalle viste materializzate anziché dalle tabelle di base.

Con le viste materializzate, la stessa query viene eseguita più velocemente senza alcuna modifica del codice.

Passaggi successivi

Per altri suggerimenti sullo sviluppo, vedere Panoramica dello sviluppo di pool SQL dedicati.