Ejercicio: Mejora del rendimiento con vistas materializadas y almacenamiento en caché del conjunto de resultados

7 minutos

Tarea 1: Mejora del rendimiento con vistas materializadas

En Azure Synapse Studio, seleccione Develop (Desarrollar) en el menú de la izquierda.
Seleccione + y después + (Script SQL).

Conéctese a SQLPool01 y, después, ejecute la consulta siguiente para obtener un plan de ejecución estimado y observar el costo total y el número de operaciones:

EXPLAIN
SELECT * FROM
(
    SELECT
    FS.CustomerID
    ,P.Seasonality
    ,D.Year
    ,D.Quarter
    ,D.Month
    ,avg(FS.TotalAmount) as AvgTotalAmount
    ,avg(FS.ProfitAmount) as AvgProfitAmount
    ,sum(FS.TotalAmount) as TotalAmount
    ,sum(FS.ProfitAmount) as ProfitAmount
FROM
    wwi.SaleSmall FS
    JOIN wwi.Product P ON P.ProductId = FS.ProductId
    JOIN wwi.Date D ON FS.TransactionDateId = D.DateId
GROUP BY
    FS.CustomerID
    ,P.Seasonality
    ,D.Year
    ,D.Quarter
    ,D.Month
) T

El resultado debería ser similar al siguiente:

<?xml version="1.0" encoding="utf-8"?>
<dsql_query number_nodes="1" number_distributions="60" number_distributions_per_node="60">
    <sql>SELECT count(*) FROM
(
    SELECT
    FS.CustomerID
    ,P.Seasonality
    ,D.Year
    ,D.Quarter
    ,D.Month
    ,avg(FS.TotalAmount) as AvgTotalAmount
    ,avg(FS.ProfitAmount) as AvgProfitAmount
    ,sum(FS.TotalAmount) as TotalAmount
    ,sum(FS.ProfitAmount) as ProfitAmount
FROM
    wwi.SaleSmall FS
    JOIN wwi.Product P ON P.ProductId = FS.ProductId
    JOIN wwi.Date D ON FS.TransactionDateId = D.DateId
GROUP BY
    FS.CustomerID
    ,P.Seasonality
    ,D.Year
    ,D.Quarter
    ,D.Month
) T</sql>
    <dsql_operations total_cost="10.61376" total_number_operations="12">

Reemplace la consulta por lo siguiente para crear una vista materializada que pueda admitir la consulta anterior:

IF EXISTS(select * FROM sys.views where name = 'mvCustomerSales')
    DROP VIEW wwi_perf.mvCustomerSales
    GO

CREATE MATERIALIZED VIEW
    wwi_perf.mvCustomerSales
WITH
(
    DISTRIBUTION = HASH( CustomerId )
)
AS
SELECT
    S.CustomerId
    ,D.Year
    ,D.Quarter
    ,D.Month
    ,SUM(S.TotalAmount) as TotalAmount
    ,SUM(S.ProfitAmount) as TotalProfit
FROM
    [wwi_perf].[Sale_Partition02] S
    join [wwi].[Date] D on
        S.TransactionDateId = D.DateId
GROUP BY
    S.CustomerId
    ,D.Year
    ,D.Quarter
    ,D.Month
GO

Esta consulta tardará entre 30 y 120 segundos en completarse.

Primero se quita la vista si existe, ya que se ha creado en un laboratorio anterior.

Ejecute la consulta siguiente para comprobar que realmente alcanza la vista materializada creada.

EXPLAIN
SELECT * FROM
(
SELECT
FS.CustomerID
,P.Seasonality
,D.Year
,D.Quarter
,D.Month
,avg(FS.TotalAmount) as AvgTotalAmount
,avg(FS.ProfitAmount) as AvgProfitAmount
,sum(FS.TotalAmount) as TotalAmount
,sum(FS.ProfitAmount) as ProfitAmount
FROM
    wwi_pbi.SaleSmall FS
    JOIN wwi_pbi.Product P ON P.ProductId = FS.ProductId
    JOIN wwi_pbi.Date D ON FS.TransactionDateId = D.DateId
GROUP BY
    FS.CustomerID
    ,P.Seasonality
    ,D.Year
    ,D.Quarter
    ,D.Month
) T

Vuelva al informe de Power BI Desktop y, después, haga clic en Transformar datos.
En el editor de Power Query, abra la página de configuración del paso Origen (1) de la consulta. Expanda la sección Opciones avanzadas (2), pegue la siguiente consulta (3) para usar la nueva vista materializada y, después, haga clic en Aceptar (4).
```
SELECT [CustomerID]
,[Seasonality]
,[Year]
,[Quarter]
,[Month]
,[TotalAmount]
,[ProfitAmount]
,[cb]
FROM [wwi].[mvCustomerSales]
```
Seleccione Cerrar y aplicar en la esquina superior izquierda de la ventana del editor para aplicar la consulta y capturar el esquema inicial en la ventana del diseñador de Power BI.
Haga clic en el botón Actualizar situado encima del informe para enviar la consulta a la nueva vista materializada.

Observe que la actualización de datos solo tarda unos segundos, en comparación con antes.
Vuelva a comprobar la duración de la consulta en Synapse Studio, en el centro de supervisión (1), en SQL requests (Solicitudes SQL) (2). Tenga en cuenta que las consultas de Power BI que usan la nueva vista materializada se ejecutan mucho más rápido (duración ~ 10s) (3).

Tarea 2: Mejora del rendimiento con el almacenamiento en caché del conjunto de resultados

En Azure Synapse Studio, seleccione Develop (Desarrollar) en el menú de la izquierda.
Seleccione + y después + (Script SQL).
Conéctese a SQLPool01 y, después, ejecute la consulta siguiente para comprobar si el almacenamiento en caché del conjunto de resultados está activado en el grupo de SQL actual:
```
SELECT
    name
    ,is_result_set_caching_on
FROM
    sys.databases
```
Si se devuelve False para SQLPool01, ejecute la consulta siguiente para activarlo (debe ejecutarla en la base de datos master):
```
ALTER DATABASE [SQLPool01]
SET RESULT_SET_CACHING ON
```
Conéctese a SQLPool01 y use la base de datos maestra:

Importante

Las operaciones para crear la caché del conjunto de resultados y recuperar datos de la caché se producen en el nodo de control de una instancia de grupo de SQL de Synapse. Cuando se activa el almacenamiento en caché de conjuntos de resultados, la ejecución de consultas que devuelven un conjunto de resultados grande (por ejemplo, > 1 GB) puede provocar una limitación elevada del nodo de control y ralentizar la respuesta de consulta general en la instancia. Estas consultas se suelen usar durante la exploración de datos o las operaciones ETL. Para evitar el esfuerzo del nodo de control y los problemas de rendimiento, los usuarios deben desactivar el almacenamiento en caché de los conjuntos de resultados en la base de datos antes de ejecutar esos tipos de consultas.
A continuación, vuelva al informe de Power BI Desktop y presione el botón Actualizar para enviar de nuevo la consulta.
Una vez que se actualicen los datos, presione Actualizar una vez más para asegurarse de que se ha alcanzado la caché de conjuntos de resultados.
Vuelva a comprobar la duración de la consulta en Synapse Studio, en el centro de supervisión (1), en la página SQL requests (Solicitudes SQL) (2). Observe que ahora se ejecuta casi al instante (Duración = 0s) (4).

Continuar

Ejercicio: Mejora del rendimiento con vistas materializadas y almacenamiento en caché del conjunto de resultados

Tarea 1: Mejora del rendimiento con vistas materializadas

Tarea 2: Mejora del rendimiento con el almacenamiento en caché del conjunto de resultados

Comentarios