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

Completado

Tarea 1: Mejora del rendimiento con vistas materializadas

1. En Azure Synapse Studio, seleccione Develop (Desarrollar) en el menú de la izquierda.

   

2. Seleccione + y después + (Script SQL).

   

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

4. 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">
   

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

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

7. Vuelva al informe de Power BI Desktop y, después, haga clic en Transformar datos.

   

8. 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]
   

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

   

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

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

1. En Azure Synapse Studio, seleccione Develop (Desarrollar) en el menú de la izquierda.

   

2. Seleccione + y después + (Script SQL).

   

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

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

5. A continuación, vuelva al informe de Power BI Desktop y presione el botón Actualizar para enviar de nuevo la consulta.

   

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

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