Ajuste del rendimiento con vistas materializadas mediante un grupo de SQL dedicado en Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse es un almacenamiento relacional de escala empresarial en una base de lago de datos, con una arquitectura lista para el futuro, inteligencia artificial integrada y nuevas características. Si no está familiarizado con el almacenamiento de datos, comience con Fabric Data Warehouse. Las cargas de trabajo del grupo de SQL dedicadas pueden actualizarse a Fabric para acceder a nuevas funcionalidades en ciencia de datos, análisis en tiempo real e informes.

En un grupo de SQL dedicado, las vistas materializadas ofrecen una forma de reducir la necesidad de mantenimiento, permitiendo que las consultas analíticas complejas logren un rendimiento rápido sin necesidad de cambiar la consulta. En este artículo se describen las instrucciones generales sobre el uso de vistas materializadas.

Vistas materializadas frente a vistas estándar

El almacén SQL admite tanto vistas estándar como vistas materializadas. Ambas son tablas virtuales creadas con expresiones SELECT y presentadas a consultas como tablas lógicas. Las vistas revelan la complejidad del cálculo de datos común y agregan una capa de abstracción a los cambios de cálculo, por lo que no es necesario volver a escribir consultas.

Una vista estándar calcula sus datos cada vez que se usa la vista. No hay datos almacenados en el disco. Las personas suelen usar vistas estándar como una herramienta que ayuda a organizar los objetos lógicos y las consultas en una base de datos. Para usar una vista estándar, una consulta debe hacer referencia directa a ella.

Una vista materializada pre-calcula, almacena y mantiene sus datos en un grupo dedicado de SQL de la misma manera que una tabla. La recomputación no es necesaria cada vez que se usa una vista materializada. Por eso las consultas que usan todos o un subconjunto de los datos en vistas materializadas pueden obtener un rendimiento más rápido. Aún mejor, las consultas pueden usar una vista materializada sin hacer referencia directa a ella, por lo que no es necesario cambiar el código de la aplicación.

La mayoría de los requisitos de vista estándar se siguen aplicando a una vista materializada. Para obtener más información sobre la sintaxis de vista materializada y otros requisitos, consulte CREATE MATERIALIZED VIEW AS SELECT.

Comparación Ver Vista materializada
Ver definición Almacenado en el almacén de datos de Azure. Almacenado en el almacén de datos de Azure.
Ver contenido Se genera cada vez que se usa la vista. Preprocesados y almacenados en el almacén de datos de Azure al crear la vista. Se actualiza a medida que se agregan datos a las tablas subyacentes.
Actualización de datos Siempre actualizado Siempre actualizado
Velocidad de recuperación de los datos de la vista a partir de consultas complejas Lento Rápido
Almacenamiento adicional No Sí
Sintaxis CREAR VISTA CREAR VISTA MATERIALIZADA COMO SELECT

Ventajas de las vistas materializadas

Una vista materializada correctamente diseñada proporciona las siguientes ventajas:

  • Tiempo de ejecución reducido para consultas complejas con JOIN y funciones de agregado. Cuanto más compleja sea la consulta, mayor será la posibilidad de ahorrar tiempo de ejecución. La mayor ventaja se obtiene cuando el costo de cálculo de una consulta es alto y el conjunto de datos resultante es pequeño.

  • El optimizador de consultas del grupo de SQL dedicado puede usar automáticamente vistas materializadas implementadas para mejorar los planes de ejecución de consultas. Este proceso es transparente para los usuarios que proporcionan un rendimiento de consulta más rápido y no requiere consultas para hacer referencia directa a las vistas materializadas.

  • Se requiere poco mantenimiento para las vistas. Una vista materializada almacena los datos en dos lugares, un índice de almacén de columnas agrupado para los datos iniciales en el momento de creación de la vista y un almacén delta para los cambios incrementales de datos. Todos los cambios de datos de las tablas base se agregan automáticamente al almacén delta de forma sincrónica. Un proceso en segundo plano (motor de tupla) mueve periódicamente los datos del almacén incremental al índice de almacén de columnas de la vista. Este diseño permite consultar vistas materializadas para devolver los mismos datos que consultar directamente las tablas base.

  • Los datos de una vista materializada se pueden distribuir de forma diferente a las tablas base.

  • Los datos en vistas materializadas obtienen las mismas ventajas de alta disponibilidad y resistencia que los datos de tablas normales.

En comparación con otros proveedores de almacenamiento de datos, las vistas materializadas implementadas en el grupo de SQL dedicado también proporcionan las siguientes ventajas adicionales:

  • Actualización automática y sincrónica de datos con cambios de datos en tablas base. No se requiere ninguna acción del usuario.
  • Amplia compatibilidad con funciones agregadas. Consulte CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
  • Compatibilidad con la recomendación de vista materializada específica de la consulta. Consulte EXPLAIN (Transact-SQL).

Escenarios frecuentes

Las vistas materializadas se suelen usar en los siguientes escenarios:

Es necesario mejorar el rendimiento de las consultas analíticas complejas con datos de gran tamaño

Las consultas analíticas complejas suelen utilizar más funciones de agregación y uniones de tablas, lo que provoca más operaciones de proceso intensivo, como reorganizaciones y uniones en la ejecución de consultas. Por eso esas consultas tardan más tiempo en completarse, especialmente en tablas grandes.

Los usuarios pueden crear vistas materializadas para los datos devueltos a partir de los cálculos comunes de las consultas, por lo que no es necesario volver a calcular los datos cuando las consultas necesitan estos datos, lo que permite reducir el costo de proceso y una respuesta de consulta más rápida.

Necesitar un rendimiento más rápido sin cambios o con cambios mínimos en las consultas

Los cambios de esquema y consulta en los almacenes de datos se mantienen normalmente en un mínimo para admitir operaciones e informes de ETL normales. Las personas pueden utilizar vistas materializadas para optimizar el rendimiento de las consultas si los costos asociados con las vistas pueden ser compensados por la mejora en el rendimiento de las consultas.

En comparación con otras opciones de ajuste, como el escalado y la administración de estadísticas, es un cambio de producción mucho menos impactante para crear y mantener una vista materializada y su posible ganancia de rendimiento también es mayor.

  • La creación o el mantenimiento de vistas materializadas no afecta las consultas que se ejecutan sobre las tablas base.
  • El optimizador de consultas puede usar automáticamente las vistas materializadas desplegadas sin que la consulta refiera directamente a ellas. Esta funcionalidad reduce la necesidad de cambiar la consulta en el ajuste del rendimiento.

Necesita una estrategia de distribución de datos diferente para mejorar el rendimiento de las consultas

Azure Data Warehouse es un sistema de procesamiento paralelo masivo (MPP) distribuido y masivo.

Synapse SQL es un sistema de consultas distribuido que permite a las empresas implementar escenarios de almacenamiento de datos y virtualización de datos mediante experiencias estándar de T-SQL conocidas para los ingenieros de datos. También amplía las funcionalidades de SQL para abordar escenarios de streaming y aprendizaje automático. Los datos de una tabla de almacenamiento de datos se distribuyen entre 60 nodos mediante una de las tres estrategias de distribución (hash, round_robin o replicadas).

La distribución de datos se especifica en el momento de creación de la tabla y permanece sin cambios hasta que se quita la tabla. Puesto que la vista materializada es una tabla virtual en disco, posibilita las distribuciones de datos hash y round_robin. Los usuarios pueden elegir una distribución de datos diferente de las tablas base, pero óptima para el rendimiento de las consultas que usan con frecuencia las vistas.

Guía de diseño

Esta es la guía general sobre el uso de vistas materializadas para mejorar el rendimiento de las consultas:

Diseño de la carga de trabajo

Antes de empezar a crear vistas materializadas, es importante tener un conocimiento profundo de la carga de trabajo en términos de patrones de consulta, importancia, frecuencia y tamaño de los datos resultantes.

Los usuarios pueden ejecutar EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> para las vistas materializadas recomendadas por el optimizador de consultas. Dado que estas recomendaciones son específicas de la consulta, una vista materializada que beneficia a una sola consulta puede no ser óptima para otras consultas de la misma carga de trabajo.

Evalúe estas recomendaciones teniendo en cuenta sus necesidades de carga de trabajo. Las vistas materializadas ideales son aquellas que benefician el rendimiento de la carga de trabajo.

Tenga en cuenta el equilibrio entre las consultas más rápidas y el costo.

Para cada vista materializada, hay un costo de almacenamiento de datos y un costo para mantener la vista. A medida que cambian los datos en las tablas base, el tamaño de la vista materializada aumenta y su estructura física también cambia.

Para evitar la degradación del rendimiento de las consultas, el motor de almacenamiento de datos mantiene por separado cada vista materializada, lo que incluye el movimiento de filas del almacén delta a los segmentos de índice de almacén de columnas y la consolidación de los cambios de datos.

La carga de trabajo de mantenimiento se incrementa cuando aumentan tanto el número de vistas materializadas como los cambios en la tabla base. Los usuarios deben comprobar si el costo derivado de todas las vistas materializadas puede ser compensado por la ganancia de rendimiento de la consulta.

Puede ejecutar esta consulta para obtener la lista de vistas materializadas en una base de datos:

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;

Opciones para reducir el número de vistas materializadas:

  • Identifique los conjuntos de datos comunes usados con frecuencia por las consultas complejas de la carga de trabajo. Cree vistas materializadas para almacenar esos conjuntos de datos para que el optimizador pueda usarlos como bloques de creación al crear planes de ejecución.

  • Elimine las vistas materializadas que tienen poco uso o que ya no son necesarias. Una vista materializada deshabilitada no se mantiene, pero sigue incurriendo en el costo de almacenamiento.

  • Combine vistas materializadas creadas en las mismas tablas base o similares incluso si sus datos no se superponen. La combinación de vistas materializadas podría dar lugar a una vista más grande en tamaño que la suma de las vistas independientes, pero el costo de mantenimiento de la vista debería reducirse. Por ejemplo:

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

No todo el ajuste del rendimiento requiere el cambio de consulta

El optimizador de almacenamiento de datos puede usar automáticamente vistas materializadas implementadas para mejorar el rendimiento de las consultas. Este soporte se aplica de forma transparente a las consultas que no hacen referencia a las vistas y a las consultas que usan agregados no admitidos en la creación de vistas materializadas. No se necesita ningún cambio de la consulta. Puede comprobar el plan de ejecución estimado de una consulta para confirmar si se usa una vista materializada.

Supervisión de vistas materializadas

Una vista materializada se almacena en el almacenamiento de datos igual que una tabla con un índice de almacén de columnas agrupado (CCI). La lectura de los datos de una vista materializada incluye el examen del índice y la aplicación de cambios desde el almacén incremental. Cuando el número de filas del almacén incremental es demasiado alto, la resolución de una consulta a partir de una vista materializada puede tardar más que la consulta directa de las tablas base.

Para evitar la degradación del rendimiento de las consultas, se recomienda ejecutar DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD para supervisar la relación de sobrecarga (filas totales/filas de la vista base) de la vista. Si la relación de sobrecarga es demasiado alta, considere la posibilidad de volver a generar la vista materializada para que todas las filas del almacén incremental se muevan al índice de almacén de columnas.

Almacenamiento en caché de vistas materializadas y conjuntos de resultados

Estas dos características se presentan en un grupo de SQL dedicado aproximadamente al mismo tiempo para el ajuste del rendimiento de las consultas. El almacenamiento en caché del conjunto de resultados se usa para lograr una alta simultaneidad y tiempos de respuesta rápidos de consultas repetitivas en datos estáticos.

Para usar el resultado almacenado en caché, la forma de la consulta de solicitud de caché debe coincidir con la consulta que generó la memoria caché. Además, el resultado almacenado en caché debe aplicarse a toda la consulta.

Las vistas materializadas permiten cambios de datos en las tablas base. Los datos de vistas materializadas se pueden aplicar a una parte de una consulta. Esta compatibilidad permite usar las mismas vistas materializadas en diferentes consultas que comparten algunos cálculos para un rendimiento más rápido.

Ejemplo

En este ejemplo se usa una consulta similar a TPCDS que busca a los clientes que gastan más dinero a través del catálogo que en las tiendas. También identifica los clientes preferidos y su país o región de origen. La consulta implica seleccionar los primeros 100 registros del resultado de la instrucción UNION de tres instrucciones sub-SELECT que implican SUM() y 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');

Compruebe el plan de ejecución estimado de la consulta. Hay 18 combinaciones y 17 operaciones de unión que tardan más tiempo en ejecutarse.

Ahora vamos a crear una vista materializada para cada una de las tres instrucciones 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

Vuelva a comprobar el plan de ejecución de la consulta original. Ahora el número de uniones cambia de 17 a 5 y no hay más reorganización. Seleccione el icono Operación de filtro en el plan. Su lista de resultados muestra que los datos se leen de las vistas materializadas en lugar de las tablas base.

Lista_de_Salida_del_Plan_con_Vistas_Materializadas

Con vistas materializadas, la misma consulta se ejecuta mucho más rápido sin ningún cambio de código.

Pasos siguientes

Para obtener más sugerencias sobre desarrollo, consulte la información general sobre desarrollo de SQL de Synapse.

  • Last updated on