Queryplan voor Power Query (preview)

Queryplan voor Power Query is een functie die een beter overzicht biedt van de evaluatie van uw query. Het is handig om te bepalen waarom een bepaalde query mogelijk niet in een bepaalde stap wordt gevouwen.

In een praktisch voorbeeld ziet u in dit artikel de belangrijkste use case en mogelijke voordelen van het gebruik van de functie queryplan om uw querystappen te bekijken. De voorbeelden die in dit artikel worden gebruikt, zijn gemaakt met behulp van de AdventureWorksLT-voorbeelddatabase voor Azure SQL Server, die u kunt downloaden uit AdventureWorks-voorbeelddatabases.

Notitie

De functie queryplan voor Power Query is alleen beschikbaar in Power Query Online.

Dit artikel is onderverdeeld in een reeks aanbevolen stappen om het queryplan te interpreteren. Deze stappen zijn:

1. Bekijk de indicatoren voor het vouwen van query's.
2. Selecteer de querystap om het queryplan te controleren.
3. Implementeer wijzigingen in uw query.

Gebruik de volgende stappen om de query te maken in uw eigen Power Query Online-omgeving.

1. Selecteer in Power Query - Gegevensbron kiezen de optie Lege query.

2. Vervang het script van de lege query door de volgende query.

   let
     Source = Sql.Database("servername", "database"),
     Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
     #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
     #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
     #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
   in
     #"Kept bottom rows"
   

3. Wijzig servername en database met de juiste namen voor uw eigen omgeving.

4. (Optioneel) Als u verbinding probeert te maken met een server en database voor een on-premises omgeving, moet u een gateway voor die omgeving configureren.

5. Selecteer Volgende.

6. Selecteer in het Power Query-editor de optie Verbinding configureren en geef de referenties op voor uw gegevensbron.

Notitie

Ga naar de SQL Server-database voor meer informatie over het maken van verbinding met een SQL Server.

Nadat u deze stappen hebt uitgevoerd, ziet uw query eruit als de query in de volgende afbeelding.

Deze query maakt verbinding met de tabel SalesOrderHeader en selecteert een paar kolommen uit de laatste vijf orders met een TotalDue-waarde boven 1000.

Notitie

In dit artikel wordt een vereenvoudigd voorbeeld gebruikt om deze functie weer te geven, maar de concepten die in dit artikel worden beschreven, zijn van toepassing op alle query's. Het is raadzaam dat u goed op de hoogte bent van het vouwen van query's voordat u het queryplan leest. Voor meer informatie over het vouwen van query's gaat u naar de basisbeginselen van Query Folding.

1. Bekijk de indicatoren voor het vouwen van query's

Notitie

Voordat u deze sectie leest, raden we u aan het artikel over query folding-indicatoren te bekijken.

De eerste stap in dit proces is het controleren van uw query en de aandacht vestigen op de indicatoren voor het vouwen van query's. Het doel is om de stappen te controleren die zijn gemarkeerd als niet gevouwen. Vervolgens kunt u zien of het aanbrengen van wijzigingen in de algemene query deze transformaties volledig kan maken.

In dit voorbeeld is de enige stap die niet kan worden gevouwen , onderste rijen behouden, wat gemakkelijk te herkennen is door de niet-gevouwen stapindicator. Deze stap is ook de laatste stap van de query.

Het doel is nu om deze stap te bekijken en te begrijpen wat er wordt teruggevouwen naar de gegevensbron en wat niet kan worden gevouwen.

2. Selecteer de querystap om het queryplan te controleren

U hebt de stap Onderste rijen behouden geïdentificeerd als een stap van belang omdat deze niet terugvouwt naar de gegevensbron. Klik met de rechtermuisknop op de stap en selecteer de optie Queryplan weergeven. Met deze actie wordt een nieuw dialoogvenster weergegeven dat een diagram bevat voor het queryplan van de geselecteerde stap.

Power Query probeert uw query te optimaliseren door te profiteren van luie evaluatie en query folding, zoals vermeld in de basisprincipes van Query Folding. Dit queryplan vertegenwoordigt de geoptimaliseerde vertaling van uw M-query in de systeemeigen query die naar de gegevensbron wordt verzonden. Het bevat ook transformaties die worden uitgevoerd door de Power Query Engine. De volgorde waarin de knooppunten worden weergegeven, volgt de volgorde van de query die begint met de laatste stap of uitvoer van uw query, die uiterst links van het diagram wordt weergegeven. In dit geval is het knooppunt Table.LastN dat de stap Onderste rijen behouden vertegenwoordigt.

Onderaan het dialoogvenster bevindt zich een balk met pictogrammen waarmee u kunt in- of uitzoomen op de weergave van het queryplan en andere knoppen om de weergave te beheren. Voor de vorige afbeelding werd de optie Passend om te bekijken op deze balk gebruikt om de knooppunten beter te waarderen.

Notitie

Het queryplan vertegenwoordigt het geoptimaliseerde plan. Wanneer de engine een query evalueert, wordt geprobeerd alle operators in een gegevensbron te vouwen. In sommige gevallen kan het zelfs een aantal interne volgorde van de stappen uitvoeren om het vouwen te maximaliseren. Met dit in gedachten bevatten de knooppunten/operators in dit geoptimaliseerde queryplan doorgaans de gevouwen gegevensbronquery en alle operators die niet kunnen worden gevouwen en lokaal worden geëvalueerd.

Gevouwen knooppunten van andere knooppunten identificeren

U kunt de knooppunten in dit diagram identificeren als twee groepen:

• Gevouwen knooppunten: dit knooppunt kan ofwel gegevensbronknooppunten zijn Value.NativeQuery , zoals Sql.Database. Deze kunnen ook worden geïdentificeerd met het externe label onder hun functienaam.
• Niet-gevouwen knooppunten: andere tabeloperators, zoals Table.SelectRows, Table.SelectColumnsen andere functies die niet konden worden gevouwen. Deze kunnen ook worden geïdentificeerd met de labels Volledig scannen en streamen.

In de volgende afbeelding ziet u de gevouwen knooppunten in de rode rechthoek. De rest van de knooppunten kan niet worden teruggevouwen naar de gegevensbron. U moet de rest van de knooppunten bekijken, omdat het doel is om deze knooppunten terug te laten vouwen naar de gegevensbron.

U kunt details onder aan sommige knooppunten weergeven selecteren om uitgebreide informatie weer te geven. In de details van het Value.NativeQuery knooppunt wordt bijvoorbeeld de systeemeigen query (in SQL) weergegeven die naar de gegevensbron wordt verzonden.

De query die hier wordt weergegeven, is mogelijk niet exact dezelfde query die naar de gegevensbron is verzonden, maar dit is een goede benadering. In dit geval wordt precies aangegeven welke kolommen worden opgevraagd uit de tabel SalesOrderHeader en vervolgens hoe deze tabel wordt gefilterd met behulp van het veld TotalDue om alleen rijen op te halen waarin de waarde voor dat veld groter is dan 1000. Het knooppunt ernaast, Table.LastN, wordt lokaal berekend door de Power Query-engine, omdat het niet kan worden gevouwen.

Notitie

De operators komen mogelijk niet exact overeen met de functies die worden gebruikt in het script van de query.

Controleer niet-gevouwen knooppunten en overweeg acties om uw transformatievouw te maken

U hebt nu vastgesteld welke knooppunten niet kunnen worden gevouwen en lokaal worden geëvalueerd. Dit geval heeft alleen het Table.LastN knooppunt, maar in andere scenario's kan het nog veel meer hebben.

Het doel is om wijzigingen toe te passen op uw query, zodat de stap kan worden gevouwen. Sommige van de wijzigingen die u kunt implementeren, kunnen variëren van het opnieuw rangschikken van uw stappen tot het toepassen van een alternatieve logica op uw query die explicieter is voor de gegevensbron. Dit betekent niet dat alle query's en alle bewerkingen kunnen worden gevouwen door enkele wijzigingen toe te passen. Maar het is een goede gewoonte om te bepalen door middel van een evaluatie en fout als uw query kan worden teruggevouwen.

Omdat de gegevensbron een SQL Server-database is, is het een goed alternatief om de laatste vijf orders uit de tabel op te halen, dan is het een goed alternatief om te profiteren van de TOP- en ORDER BY-componenten in SQL. Omdat er geen BOTTOM-component in SQL is, kan de Table.LastN transformatie in PowerQuery niet worden omgezet in SQL. U kunt de Table.LastN stap verwijderen en vervangen door:

• Een aflopende sorteerstap op de kolom SalesOrderID in de tabel, omdat deze kolom bepaalt welke volgorde het eerst gaat en welke laatste is ingevoerd.
• Selecteer de bovenste vijf rijen sinds de tabel is gesorteerd. Met deze transformatie wordt hetzelfde bereikt als als de onderste rijen behouden (Table.LastN).

Dit alternatief is gelijk aan de oorspronkelijke query. Hoewel dit alternatief in theorie goed lijkt, moet u de wijzigingen aanbrengen om te zien of dit alternatief dit knooppunt volledig terugvouwt naar de gegevensbron.

3. Wijzigingen in uw query implementeren

Implementeer het alternatief dat in de vorige sectie is besproken:

1. Sluit het dialoogvenster queryplan en ga terug naar de Power Query-editor.

2. Verwijder de stap Bewaarde onderste rijen .

3. Sorteer de kolom SalesOrderID in aflopende volgorde.

   

4. Selecteer het tabelpictogram in de linkerbovenhoek van de voorbeeldweergave van de gegevens en selecteer de optie voor het lezen van bovenste rijen behouden. Geef in het dialoogvenster het getal vijf door als het argument en druk op OK.

   

Nadat u de wijzigingen hebt geïmplementeerd, controleert u de indicatoren voor het vouwen van query's opnieuw en controleert u of u een gevouwen indicator krijgt.

Nu is het tijd om het queryplan van de laatste stap te bekijken. Dit is nu toprijen behouden. Er zijn nu alleen gevouwen knooppunten. Selecteer Details weergeven onder Value.NativeQuery om te controleren welke query naar de database wordt verzonden.

Hoewel in dit artikel wordt voorgesteld welk alternatief u kunt toepassen, is het belangrijkste doel voor u om te leren hoe u het queryplan kunt gebruiken om query folding te onderzoeken. Dit artikel biedt ook inzicht in wat er naar uw gegevensbron wordt verzonden en welke transformaties lokaal worden uitgevoerd.

U kunt uw code aanpassen om de impact te zien die deze heeft in uw query. Met behulp van de indicatoren voor het vouwen van query's hebt u ook een beter idee van welke stappen ervoor zorgen dat uw query niet kan worden gevouwen.