Prestaties optimaliseren met gematerialiseerde views

Gerealiseerde weergaven voor toegewezen SQL-pools in Azure Synapse bieden een methode met weinig onderhoud voor complexe analytische query's om snelle prestaties te krijgen zonder querywijziging. In dit artikel worden de algemene richtlijnen voor het gebruik van materialized views besproken.

Gematerealiseerde weergaven versus standaardweergaven

Toegewezen SQL-pool in Azure Synapse biedt ondersteuning voor standaardweergaven en gerealiseerde weergaven. Beide zijn virtuele tabellen die zijn gemaakt met SELECT-expressies en worden weergegeven voor query's als logische tabellen. Weergaven kapselen de complexiteit van algemene gegevensberekeningen in en voegen een abstractielaag toe aan rekenwijzigingen, zodat u geen query's hoeft te herschrijven.

Een standaardweergave berekent de gegevens telkens wanneer de weergave wordt gebruikt. Er zijn geen gegevens opgeslagen op schijf. Mensen gebruiken doorgaans standaardweergaven als hulpprogramma waarmee de logische objecten en query's in een toegewezen SQL-pool kunnen worden ingedeeld. Als u een standaardweergave wilt gebruiken, moet een query er rechtstreeks naar verwijzen.

Een gerealiseerde weergave berekent, bewaart en onderhoudt de gegevens in een toegewezen SQL-pool net als een tabel. Er is geen hercomputatie nodig telkens wanneer een gerealiseerde weergave wordt gebruikt. Daarom kunnen query's die gebruikmaken van alle of een subset van de gegevens in gerealiseerde weergaven sneller presteren. Nog beter, query's kunnen een gerealiseerde weergave gebruiken zonder er direct naar te verwijzen, dus u hoeft geen toepassingscode te wijzigen.

De meeste vereisten voor een standaardweergave zijn nog steeds van toepassing op een gerealiseerde weergave. Voor meer informatie over de gerealiseerde weergavesyntaxis en andere vereisten raadpleegt u CREATE MATERIALIZED VIEW AS SELECT

Voordelen van het gebruik van gematerialiseerde weergaven

Een goed ontworpen materiële weergave biedt de volgende voordelen:

• Verminder de uitvoeringstijd voor complexe query's met JOIN's en statistische functies. Hoe complexer de query, hoe groter de kans om tijd te besparen bij de uitvoering. Het meeste voordeel wordt behaald wanneer de rekenkosten van een query hoog zijn en de resulterende gegevensset klein is.
• De optimizer in een toegewezen SQL-pool kan automatisch geïmplementeerde gerealiseerde weergaven gebruiken om queryuitvoeringsplannen te verbeteren. Dit proces is transparant voor gebruikers doordat het zorgt voor snellere queryprestaties en het is niet nodig om in query's direct te verwijzen naar de gerealiseerde weergaven.
• Weinig onderhoud vereisen voor de weergaven. Alle incrementele gegevenswijzigingen van de basistabellen worden automatisch op synchrone wijze toegevoegd aan de gerealiseerde weergaven, wat betekent dat zowel de basistabellen als de gerealiseerde weergaven in dezelfde transactie worden bijgewerkt. Met dit ontwerp kunnen de gerealiseerde weergaven dezelfde gegevens teruggeven als wanneer de basistabellen direct worden opgevraagd.
• De gegevens in een gerealiseerde weergave kunnen anders worden verdeeld dan de basistabellen.
• Gegevens in gerealiseerde weergaven krijgen dezelfde voordelen van hoge beschikbaarheid en tolerantie als gegevens in normale tabellen.

De gematerialiseerde weergaven die zijn geïmplementeerd in een speciale SQL-pool zijn ook voordelig op de volgende manieren:

In vergelijking met andere datawarehouseproviders bieden de gerealiseerde weergaven die zijn geïmplementeerd in een toegewezen SQL-pool ook de volgende voordelen:

• Brede ondersteuning voor statistische functies. Zie CREATE GEMATERIALISEERD OVERZICHT ALS SELECT (Transact-SQL).
• De ondersteuning voor queryspecifieke gerealiseerde weergaveaanbeveling. Zie EXPLAIN (Transact-SQL).
• Automatisch en synchroon vernieuwen van gegevens met gegevenswijzigingen in basistabellen. De gebruiker hoeft verder niets te doen.

Algemene scenario's

Materiële weergaven worden doorgaans gebruikt in de volgende scenario's:

De prestaties van complexe analytische query's op grote gegevens moeten worden verbeterd

Complexe analytische query's maken doorgaans gebruik van meer statistische functies en tabeldeelnames, wat leidt tot meer rekenintensieve bewerkingen, zoals willekeurige bewerkingen en joins in de uitvoering van query's. Daarom duurt het langer om complexe analytische query's uit te voeren, met name voor grote tabellen.

Gebruikers kunnen gerealiseerde weergaven maken voor de gegevens die worden geretourneerd door algemene berekeningen van query's. Er is dus geen hercomputatie nodig wanneer deze gegevens nodig zijn voor query's, waardoor lagere rekenkosten en snellere queryreacties mogelijk zijn.

Snellere prestaties nodig met minimale of geen querywijzigingen

Schema- en querywijzigingen in toegewezen SQL-pools worden doorgaans tot een minimum beperkt om normale ETL-bewerkingen en -rapportage te ondersteunen. Mensen kunnen materiële weergaven gebruiken voor het verbeteren van de queryprestaties, als de kosten die door de weergaven worden gemaakt, kunnen worden gecompenseerd door de winst in queryprestaties.

In vergelijking met andere afstemmingsopties, zoals schalen en statistiekenbeheer, is het een minder impactvolle productiewijziging om een gerealiseerde weergave te maken en te onderhouden en de potentiële prestatiewinst is ook hoger.

• Het aanmaken of onderhouden van materiële weergaven heeft geen invloed op de query's die worden uitgevoerd op de basistabellen.
• De query-optimalisator kan automatisch de geïmplementeerde gematerialiseerde weergaven gebruiken zonder directe weergavereferentie in een query. Deze mogelijkheid vermindert de noodzaak van querywijziging bij het afstemmen van prestaties.

Verschillende strategie voor gegevensdistributie nodig voor snellere queryprestaties

Toegewezen SQL-pool is een gedistribueerd systeem voor queryverwerking. Gegevens in een SQL-tabel worden maximaal 60 knooppunten gedistribueerd met behulp van een van de drie distributiestrategieën (hash, round_robin of gerepliceerd).

De gegevensdistributie wordt opgegeven tijdens het maken van de tabel en blijft ongewijzigd totdat de tabel wordt verwijderd. Gerealiseerde weergave, een virtuele tabel op schijf, ondersteunt hash- en round_robin-gegevensdistributies. Gebruikers kunnen een gegevensdistributie kiezen die verschilt van de basistabellen, maar die optimaal is voor de prestaties van query's die gebruikmaken van de weergaven.

Ontwerprichtlijnen

Hieronder de algemene richtlijnen voor het gebruik van gematerialiseerde views om de queryprestaties te verbeteren.

Ontwerpen voor uw workload

Voordat u gerealiseerde weergaven gaat maken, is het belangrijk dat u een goed beeld hebt van uw workload in termen van querypatronen, urgentie, frequentie en de grootte van resulterende gegevens.

Gebruikers kunnen EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> uitvoeren voor de gematerialiseerde weergaven die worden aanbevolen door de query-optimizer. Omdat deze aanbevelingen queryspecifiek zijn, is een gerealiseerde weergave die voordelen biedt voor één query mogelijk niet optimaal voor andere query's in dezelfde workload.

Evalueer deze aanbevelingen met uw workloadbehoeften in gedachten. De ideale gerealiseerde weergaven zijn weergaven die ten goede komen aan de prestaties van de workload.

Let op de afweging tussen snellere query's en de kosten

Voor elke gerealiseerde weergave zijn er kosten voor gegevensopslag en kosten voor het onderhouden van de weergave. Naarmate gegevens in basistabellen veranderen, neemt de grootte van de gerealiseerde weergave toe en verandert de fysieke structuur ook. Om te voorkomen dat queryprestaties afnemen, wordt elke gerealiseerde weergave afzonderlijk onderhouden door de SQL-engine.

De onderhoudswerkbelasting neemt toe wanneer het aantal gematerialiseerde weergaven en wijzigingen in de basistabel toeneemt. Gebruikers moeten controleren of de kosten van alle materiaalweergaven kunnen worden gecompenseerd door de prestatiewinst van de query.

U kunt deze query uitvoeren om een lijst met gerealiseerde weergaven te genereren in een toegewezen SQL-pool:

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;

Opties voor het verminderen van het aantal gematerialiseerde weergaven.

• Algemene gegevenssets identificeren die vaak worden gebruikt door de complexe query's in uw workload. Maak gerealiseerde weergaven om deze gegevenssets op te slaan, zodat de optimizer deze als bouwstenen kan gebruiken bij het maken van uitvoeringsplannen.

• Verwijder de materiële weergaven die weinig worden gebruikt of die niet meer nodig zijn. Een uitgeschakelde gerealiseerde weergave wordt niet onderhouden, maar er worden nog steeds opslagkosten in rekening gebracht.

• Combineer gerealiseerde weergaven die zijn gemaakt in dezelfde of vergelijkbare basistabellen, zelfs als hun gegevens elkaar niet overlappen. Het combineren van gerealiseerde weergaven kan leiden tot een grotere weergave dan de som van de afzonderlijke weergaven, maar de onderhoudskosten voor weergaven moeten worden verlaagd. Voorbeeld:

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

Niet alle prestatie-afstemming vereist een querywijziging

De SQL-queryoptimalisatie kan automatisch geïmplementeerde gerealiseerde weergaven gebruiken om de queryprestaties te verbeteren. Deze ondersteuning wordt transparant toegepast op query's die niet verwijzen naar de weergaven en query's die gebruikmaken van statistische functies die niet worden ondersteund bij het maken van gerealiseerde weergaven. Er is geen wijziging van de zoekopdracht nodig. U kunt het geschatte uitvoeringsplan van een query controleren om te controleren of een gerealiseerde weergave wordt gebruikt.

Gematerialiseerde weergaven bewaken

Een gerealiseerde weergave wordt opgeslagen in de toegewezen SQL-pool, net als een tabel met een geclusterde columnstore-index (CCI). Het lezen van gegevens uit een gerealiseerde weergave omvat het scannen van de CCI-indexsegmenten en het toepassen van incrementele wijzigingen uit basistabellen. Wanneer het aantal incrementele wijzigingen te hoog is, kan het oplossen van een query vanuit een gerealiseerde weergave langer duren dan het rechtstreeks opvragen van de basistabellen.

Om te voorkomen dat queryprestaties afnemen, is het een goede gewoonte om DBCC-PDW_SHOWMATERIALIZEDVIEWOVERHEAD uit te voeren om de overhead_ratio van de weergave te bewaken (total_rows/max(1, base_view_row)). Gebruikers moeten de gematerialiseerde weergave opnieuw opbouwen als de overhead_ratio te hoog is.

Gematerialiseerde weergave en caching van resultatensets

Deze twee functies in een toegewezen SQL-pool worden gebruikt voor het afstemmen van queryprestaties. Caching van resultatensets wordt gebruikt voor hoge gelijktijdigheid en snelle reactie van terugkerende query's op statische gegevens.

Als u het resultaat in de cache wilt gebruiken, moet de vorm van de aangevraagde cachequery overeenkomen met de query die de cache heeft geproduceerd. Bovendien moet het resultaat in de cache worden toegepast op de hele query.

Gerealiseerde weergaven maken gegevenswijzigingen in de basistabellen mogelijk. Gegevens in gerealiseerde weergaven kunnen worden toegepast op een deel van een query. Met deze ondersteuning kunnen dezelfde gerealiseerde weergaven worden gebruikt door verschillende query's die een aantal berekeningen delen voor snellere prestaties.

Voorbeeld

In dit voorbeeld wordt een TPCDS-achtige query gebruikt die klanten vindt die meer geld uitgeven via de catalogus dan in winkels, de voorkeursklanten en hun land/regio van herkomst identificeren. De query omvat het selecteren van de TOP 100 records uit de UNION van drie sub-SELECT-instructies met SUM() en 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');

Controleer het geschatte uitvoeringsplan van de query. Er zijn 18 shuffle-bewerkingen en 17 join-bewerkingen, die meer tijd kosten om te worden uitgevoerd. Laten we nu één gerealiseerde weergave maken voor elk van de drie subSELECT-instructies.

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

Controleer het uitvoeringsplan van de oorspronkelijke query opnieuw. Nu verandert het aantal joins van 17 tot 5 en is er geen willekeurige volgorde. Selecteer het filterbewerkingspictogram in het plan, de uitvoerlijst geeft aan dat de gegevens worden gelezen uit de gerealiseerde weergaven in plaats van de basistabellen.

Met gerealiseerde weergaven wordt dezelfde query sneller uitgevoerd zonder codewijziging.

Volgende stappen

Zie het overzicht van de ontwikkeling van toegewezen SQL-pools voor meer tips voor ontwikkeling.