Otimização de desempenho com vistas materializadas

Vistas materializadas para pools SQL dedicados no Azure Synapse fornecem um método de baixa manutenção para que consultas analíticas complexas alcancem um desempenho rápido sem necessidade de alteração das consultas. Este artigo discute a orientação geral sobre o uso de visões materializadas.

Vistas materializadas vs. vistas padrão

O pool SQL dedicado no Azure Synapse dá suporte a exibições padrão e materializadas. Ambas são tabelas virtuais criadas com expressões SELECT e apresentadas a consultas como tabelas lógicas. As visualizações encapsulam a complexidade da computação de dados comum e adicionam uma camada de abstração às alterações de computação, para que não haja necessidade de reescrever consultas.

Um modo de exibição padrão calcula seus dados sempre que o modo de exibição é usado. Não há dados armazenados no disco. As pessoas normalmente usam modos de exibição padrão como uma ferramenta que ajuda a organizar os objetos lógicos e consultas em um pool SQL dedicado. Para usar um modo de exibição padrão, uma consulta precisa fazer referência direta a ele.

Uma exibição materializada pré-calcula, armazena e mantém seus dados em um pool SQL dedicado, assim como uma tabela. Não há necessidade de recomputação cada vez que uma visão materializada é usada. É por isso que consultas que usam todos ou um subconjunto dos dados em exibições materializadas podem obter um desempenho mais rápido. Melhor ainda, as consultas podem usar uma exibição materializada sem fazer referência direta a ela, portanto, não há necessidade de alterar o código do aplicativo.

A maioria dos requisitos de uma visualização padrão ainda se aplica a uma visão materializada. Para obter detalhes sobre a sintaxe da exibição materializada e outros requisitos, consulte CREATE MATERIALIZED VIEW AS SELECT

Benefícios do uso de visualizações materializadas

Uma visão materializada adequadamente projetada oferece os seguintes benefícios:

• Reduza o tempo de execução de consultas complexas com JOINs e funções agregadas. Quanto mais complexa for a consulta, maior será o potencial de economia de tempo de execução. O maior benefício é obtido quando o custo de computação de uma consulta é alto e o conjunto de dados resultante é pequeno.
• O otimizador no pool SQL dedicado pode usar automaticamente vistas materializadas implantadas para melhorar os planos de execução das consultas. Esse processo é transparente para os usuários, proporcionando um desempenho de consulta mais rápido e não requer consultas para fazer referência direta às exibições materializadas.
• Requer pouca manutenção no interface visual. Todas as alterações de dados incrementais das tabelas base são adicionadas automaticamente às visualizações materializadas de forma síncrona, o que significa que tanto as tabelas base quanto as exibições materializadas são atualizadas na mesma transação. Esse design permite consultar exibições materializadas para retornar os mesmos dados que consultar diretamente as tabelas base.
• Os dados em uma visão materializada podem ser distribuídos de forma diferente das tabelas base.
• Os dados em visualizações materializadas obtêm os mesmos benefícios de alta disponibilidade e resiliência que os dados em tabelas regulares.

As visualizações materializadas implementadas no pool SQL dedicado também oferecem os seguintes benefícios:

Em comparação com outros provedores de data warehouse, as exibições materializadas implementadas no pool SQL dedicado também oferecem os seguintes benefícios:

• Amplo suporte a funções agregadas. Consulte CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• O suporte para recomendações de visualizações materializadas específicas para consultas. Consulte EXPLAIN (Transact-SQL).
• Atualização automática e síncrona de dados com alterações de dados em tabelas base. Nenhuma ação do usuário é necessária.

Cenários comuns

As visões materializadas são normalmente usadas nos seguintes cenários:

Necessidade de melhorar o desempenho de consultas analíticas complexas em comparação com dados grandes em tamanho

Consultas analíticas complexas tendem a utilizar um maior número de funções agregadas e uniões de tabelas, resultando em operações mais intensivas em termos de computação, como redistribuições e uniões na execução das consultas. É por isso que consultas analíticas complexas levam mais tempo para serem concluídas, especialmente em tabelas grandes.

Os usuários podem criar visualizações materializadas para os dados retornados de cálculos comuns de consultas, portanto, não há necessidade de recomputação quando esses dados são necessários para consultas, permitindo menor custo de computação e resposta mais rápida à consulta.

Precisa de um desempenho mais rápido com nenhuma ou mínima alteração de consulta

As alterações de esquema e consulta em pools SQL dedicados geralmente são mantidas em um mínimo para dar suporte a operações e relatórios regulares de ETL. As pessoas podem usar exibições materializadas para ajuste de desempenho de consulta, se o custo incorrido pelas exibições puder ser compensado pelo ganho no desempenho da consulta.

Em comparação com outras opções de ajuste, como escalonamento e gerenciamento de estatísticas, criar e manter uma visão materializada é uma mudança de produção menos impactante e seu potencial de ganho de desempenho também é maior.

• Criar ou manter vistas materializadas não afeta as consultas que são executadas contra as tabelas base.
• O otimizador de consultas pode usar automaticamente as vistas materializadas desplegadas sem referência direta às vistas numa consulta. Este recurso reduz a necessidade de alteração de consulta na otimização de desempenho.

Precisa de uma estratégia de distribuição de dados diferente para um desempenho de consulta mais rápido

O pool SQL dedicado é um sistema de processamento de consultas distribuído. Os dados em uma tabela SQL são distribuídos até 60 nós usando uma das três estratégias de distribuição (hash, round_robin ou replicada).

A distribuição de dados é especificada no momento da criação da tabela e permanece inalterada até que a tabela seja descartada. A visão materializada, sendo uma tabela virtual em disco, suporta distribuições de dados do tipo hash e round_robin. Os usuários podem escolher uma distribuição de dados diferente das tabelas base, mas ideal para o desempenho de consultas que usam as exibições.

Orientação de design

Aqui está a orientação geral sobre como usar exibições materializadas para melhorar o desempenho da consulta:

Projete para sua carga de trabalho

Antes de começar a criar exibições materializadas, é importante ter uma compreensão profunda da sua carga de trabalho em termos de padrões de consulta, importância, frequência e tamanho dos dados resultantes.

Os usuários podem executar EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> para as exibições materializadas recomendadas pelo otimizador de consulta. Como essas recomendações são específicas da consulta, uma exibição materializada que beneficie uma única consulta pode não ser ideal para outras consultas na mesma carga de trabalho.

Avalie essas recomendações com suas necessidades de carga de trabalho em mente. As visões materializadas ideais são aquelas que beneficiam o desempenho da carga de trabalho.

Esteja ciente do equilíbrio entre consultas mais rápidas e o custo

Para cada visualização materializada, há um custo de armazenamento de dados e um custo para manter a exibição. À medida que os dados mudam nas tabelas base, o tamanho da visualização materializada aumenta e sua estrutura física também muda. Para evitar a degradação do desempenho da consulta, cada exibição materializada é mantida separadamente pelo mecanismo SQL.

A carga de trabalho de manutenção fica maior quando o número de visualizações materializadas e alterações na tabela base aumenta. Os usuários devem verificar se o custo incorrido de todas as exibições materializadas pode ser compensado pelo ganho de desempenho da consulta.

Você pode executar essa consulta para gerar uma lista de exibições materializadas em um pool SQL dedicado:

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;

Opções para reduzir o número de visualizações materializadas:

• Identifique conjuntos de dados comuns usados com freqüência pelas consultas complexas em sua carga de trabalho. Crie exibições materializadas para armazenar esses conjuntos de dados para que o otimizador possa usá-los como blocos de construção ao criar planos de execução.

• Solte as visualizações materializadas que têm baixo uso ou não são mais necessárias. Uma visão materializada desativada não é mantida, mas ainda gera custos de armazenamento.

• Combine visões materializadas criadas nas mesmas tabelas base ou em tabelas base semelhantes, mesmo quando os seus dados não se sobreponham. A combinação de visualizações materializadas pode resultar em uma exibição maior em tamanho do que a soma das exibições separadas, no entanto, o custo de manutenção da exibição deve reduzir. Por exemplo:

-- 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

Nem todo ajuste de desempenho requer alteração de consulta

O otimizador de consulta SQL pode usar automaticamente exibições materializadas implantadas para melhorar o desempenho da consulta. Esse suporte é aplicado de forma transparente a consultas que não fazem referência a vistas e consultas que usam agregações sem suporte na criação de vistas materializadas. Nenhuma alteração de consulta é necessária. Você pode verificar o plano de execução estimado de uma consulta para confirmar se uma vista materializada é usada.

Monitorizar visões materializadas

Uma vista materializada é armazenada no pool SQL dedicado tal como uma tabela com um índice columnstore clusterizado (CCI). A leitura de dados de uma visão materializada inclui a análise dos segmentos do índice CCI e a aplicação das alterações incrementais das tabelas base. Quando o número de alterações incrementais é muito alto, resolver uma consulta a partir de uma exibição materializada pode levar mais tempo do que consultar diretamente as tabelas base.

Para evitar a degradação do desempenho da consulta, é uma boa prática executar o comando DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD para monitorizar o overhead_ratio da vista (total_rows / max(1, base_view_row)). Os usuários devem RECONSTRUIR a visão materializada se sua overhead_ratio for muito alta.

Vista materializada e armazenamento em cache de conjuntos de resultados

Esses dois recursos no pool SQL dedicado são usados para ajuste de desempenho de consulta. O cache do conjunto de resultados é usado para obter alta simultaneidade e resposta rápida de consultas repetitivas em relação a dados estáticos.

Para usar o resultado armazenado em cache, o formulário da consulta de solicitação de cache deve corresponder à consulta que produziu o cache. Além disso, o resultado armazenado em cache deve ser aplicado a toda a consulta.

As visualizações materializadas permitem alterações de dados nas tabelas base. Os dados em visualizações materializadas podem ser aplicados a uma parte de uma consulta. Esse suporte permite que as mesmas visualizações materializadas sejam usadas por diferentes consultas que compartilham alguma computação para um desempenho mais rápido.

Exemplo

Este exemplo usa uma consulta semelhante a TPCDS que localiza clientes que gastam mais dinheiro via catálogo do que em lojas, identifica os clientes preferenciais e seu país/região de origem. A consulta envolve a seleção dos 100 primeiros registros da união de três instruções de sub-seleção envolvendo a função 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');

Verifique o plano de execução estimado da consulta. São 18 operações de misturas e 17 junções, que levam mais tempo para serem executadas. Agora vamos criar uma exibição materializada para cada uma das três instruções 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

Verifique novamente o plano de execução da consulta original. Agora, o número de uniões muda de 17 para 5 e não há shuffle. Selecione o ícone da operação de filtro no plano, a Lista de Saída mostra que os dados são lidos a partir das visualizações materializadas em vez das tabelas base.

Com visualizações materializadas, a mesma consulta é executada mais rapidamente sem uma alteração de código.

Próximos passos

Para obter mais dicas de desenvolvimento, consulte Visão geral do desenvolvimento do pool SQL dedicado.