Übung: Verbessern der Leistung mit materialisierten Sichten und mithilfe des Zwischenspeicherns von Resultsets

7 Minuten

Aufgabe 1: Verbessern der Leistung mit materialisierten Sichten

Klicken Sie in Azure Synapse Studio im linken Menü auf Entwickeln.
Klicken Sie auf + und dann auf +.

Stellen Sie eine Verbindung zu SQLPool01 her, und führen Sie dann die folgende Abfrage aus, um einen geschätzten Ausführungsplan zu erhalten. Beobachten Sie dabei die Gesamtkosten und die Anzahl der Vorgänge:

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

Das Ergebnis sollte dem folgenden ähneln:

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

Ersetzen Sie die Abfrage durch Folgendes, um eine materialisierte Sicht zu erstellen, die die obige Abfrage unterstützt:

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

Die Ausführung dieser Abfrage dauert zwischen 30 und 120 Sekunden.

Die Sicht wurde in einem früheren Lab bereits erstellt, also können Sie sie hier zuerst löschen.

Führen Sie die folgende Abfrage aus, um zu überprüfen, ob die erstellte materialisierte Sicht tatsächlich berücksichtigt wird.

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

Wechseln Sie zurück zum Power BI Desktop-Bericht, und klicken Sie dann auf Daten transformieren.
Öffnen Sie im Power Query-Editor die Einstellungsseite des Schritts Quelle (1) in der Abfrage. Erweitern Sie den Bereich Erweiterte Optionen (2), fügen Sie die folgende Abfrage (3) ein, damit die neue materialisierte Sicht verwendet wird, und klicken Sie dann auf OK (4).
```
SELECT [CustomerID]
,[Seasonality]
,[Year]
,[Quarter]
,[Month]
,[TotalAmount]
,[ProfitAmount]
,[cb]
FROM [wwi].[mvCustomerSales]
```
Klicken Sie auf Schließen und anwenden in der oberen linken Ecke des Editorfensters, um die Abfrage anzuwenden und das ursprüngliche Schema im Designerfenster in Power BI abzurufen.
Klicken Sie oberhalb des Berichts auf die Schaltfläche Aktualisieren, um die Abfrage für die neue materialisierte Sicht zu übermitteln.

Beachten Sie, dass der Aktualisierungsvorgang für die Daten im Vergleich mit zuvor nun nur noch einige Sekunden benötigt.
Überprüfen Sie die Ausführungsdauer der Abfrage noch mal in Synapse Studio im Überwachungshub (1) unter SQL-Anforderungen (2). Beachten Sie, dass die Power BI-Abfragen, die die neue materialisierte Sicht verwenden, deutlich schneller ausgeführt werden (Dauer ~ 10 Sekunden) (3).

Aufgabe 2: Verbessern der Leistung mithilfe des Zwischenspeicherns von Resultsets

Klicken Sie in Azure Synapse Studio im linken Menü auf Entwickeln.
Klicken Sie auf + und dann auf +.
Stellen Sie eine Verbindung zu SQLPool01 her, und führen Sie dann die folgende Abfrage aus, um zu überprüfen, ob das Zwischenspeichern für Resultsets für den aktuellen SQL-Pool aktiviert ist:
```
SELECT
    name
    ,is_result_set_caching_on
FROM
    sys.databases
```
Wenn False für SQLPool01 zurückgegeben wird, führen Sie die folgende Abfrage aus, um das Zwischenspeichern für Resultsets zu aktivieren. (Die Ausführung muss für die Datenbank master erfolgen):
```
ALTER DATABASE [SQLPool01]
SET RESULT_SET_CACHING ON
```
Stellen Sie eine Verbindung zu SQLPool01 her, und verwenden Sie die Masterdatenbank:

Wichtig

Die Vorgänge zum Erstellen des Resultsetcaches und zum Abrufen von Daten aus dem Cache erfolgen im Steuerknoten einer Synapse SQL-Pool-Instanz. Wenn das Zwischenspeichern von Resultsets aktiviert ist (ON), können ausgeführte Abfragen, die ein großes Resultset zurückgeben (z. B. >1 GB), eine starke Drosselung in dem Kontrollknoten verursachen und die gesamte Abfrageantwort auf der Instanz verlangsamen. Diese Abfragen werden häufig beim Durchsuchen von Daten oder ETL-Vorgängen verwendet. Benutzer sollten das Zwischenspeichern von Resultsets auf der Datenbank deaktivieren, bevor sie diese Abfragetypen ausführen, um den Kontrollknoten nicht zu belasten und Leistungsprobleme zu verursachen.
Navigieren Sie als Nächstes zurück zum Power BI Desktop-Bericht, und klicken Sie auf die Schaltfläche Aktualisieren, um die Abfrage noch mal zu übermitteln.
Nachdem die Daten aktualisiert wurden, klicken Sie auf Refresh once more (Noch mal aktualisieren), um sicherzustellen, dass das Resultset aus dem Cache berücksichtigt wird.
Überprüfen Sie die Ausführungsdauer der Abfrage noch mal in Synapse Studio im Überwachungshub (1) auf der Seite „SQL-Anforderungen“ (2). Beachten Sie, dass die Ausführung beinahe unmittelbar erfolgt (Dauer = 0 Sekunden) (4).

Weiter

Aufgabe 1: Verbessern der Leistung mit materialisierten Sichten

Aufgabe 2: Verbessern der Leistung mithilfe des Zwischenspeicherns von Resultsets

Feedback