Queryplan voor Power Query (preview)

Queryplan voor Power Query is een functie die een beter beeld biedt van de evaluatie van uw query. Het is handig om te bepalen waarom een bepaalde query bij een bepaalde stap niet kan worden gevouwen.

In dit praktische voorbeeld worden in dit artikel de belangrijkste use-case en mogelijke voordelen van het gebruik van de functie queryplan gedemonstreerd om uw querystappen te controleren. 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.

Voorgesteld proces voor het gebruik van de functie queryplan in Power Query door de Step Folding-indicatoren te bekijken, vervolgens het queryplan voor een geselecteerde stap te controleren en ten slotte eventuele wijzigingen te implementeren die zijn afgeleid van het controleren van het queryplan.

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

  1. Bekijk de Step Folding-indicatoren.
  2. Selecteer de querystap om het queryplan te controleren.
  3. Implementeert wijzigingen in uw query.

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

  1. Selecteer Power Query - Gegevensbron kiezen en selecteer 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 met de juiste namen voor uw eigen database 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 Next.

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

Notitie

Voor meer informatie over het maken van verbinding met een SQL Server gaat u naar SQL Server database.

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

Voorbeeldquery met Step Folding-indicatoren ingeschakeld.

Deze query maakt verbinding met de tabel SalesOrderHeader en selecteert enkele kolommen uit de laatste vijf orders met een TotalDue-waarde van meer dan 1000.

Notitie

In dit artikel wordt een vereenvoudigd voorbeeld gebruikt om deze functie te presenteren, maar de concepten die in dit artikel worden beschreven, zijn van toepassing op alle query's. U wordt aangeraden een goede kennis te hebben van het vouwen van query's voordat u het queryplan leest. Ga naar Basisbeginselen van Query Folding voor meer informatie over het vouwen van query's.

1. De Step Folding-indicatoren controleren

Notitie

Voordat u deze sectie leest, raden we u aan het artikel over Step Folding Indicators te lezen.

De eerste stap in dit proces is het controleren van uw query en het goed controleren van de Step Folding-indicatoren. 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 ervoor kan zorgen dat deze transformaties volledig worden gevouwen.

Step Folding-indicatoren voor de voorbeeldquery in het deelvenster Toegepaste stappen.

In dit voorbeeld is de enige stap die niet kan worden gevouwen Onderste rijen behouden, die gemakkelijk te identificeren is via 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 gevouwen 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 belangrijke stap omdat deze niet wordt teruggevouwen 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.

Het dialoogvenster Queryplan waarin een diagramweergave voor het queryplan wordt weergegeven met knooppunten die zijn verbonden door lijnen.

Power Query probeert uw query te optimaliseren door gebruik te maken van luie evaluatie en query folding, zoals vermeld in 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 alle transformaties die worden uitgevoerd door de Power Query Engine. De volgorde waarin de knooppunten worden weergegeven, volgt de volgorde van uw query, beginnend bij de laatste stap of uitvoer van uw query, die helemaal links van het diagram wordt weergegeven en in dit geval het knooppunt Table.LastN dat de stap Onderste rijen behouden vertegenwoordigt.

Aan de onderkant van het dialoogvenster ziet u 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 is de optie Aanpassen aan weergave in deze balk gebruikt om de knooppunten beter te waarderen.

Dialoogvenster queryplan met de knooppunten vergroot voor een betere weergave.

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 er zelfs een interne volgorde van de stappen worden gebruikt om het vouwen te maximaliseren. Met dit in gedachten bevatten de knooppunten/operators in dit geoptimaliseerde queryplan doorgaans de 'gevouwen' gegevensbronquery en eventuele operators die niet kunnen worden gevouwen en lokaal worden geëvalueerd.

Gevouwen knooppunten van andere knooppunten identificeren

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

  • Gevouwen knooppunten: dit knooppunt kan knooppunten of Value.NativeQuery gegevensbronknooppunten zijn, zoals Sql.Database . Deze kunnen ook worden geïdentificeerd met het label remote onder hun functienaam.
  • Niet-gevouwen knooppunten: andere tabeloperators, zoals , en andere functies Table.SelectRows die niet kunnen worden Table.SelectColumns gevouwen. Deze kunnen ook worden geïdentificeerd met de labels Volledige scan_ en _Streaming.

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 controleren omdat het doel is om te proberen deze knooppunten weer terug te vouwen naar de gegevensbron.

Besturingselementen voor de weergave van het queryplan onder aan het dialoogvenster met de optie Passend voor weergave geselecteerd.

U kunt onderaan sommige knooppunten Details weergeven selecteren om uitgebreide informatie weer te geven. De details van het knooppunt tonen bijvoorbeeld de native query (in SQL) die naar de Value.NativeQuery gegevensbron wordt verzonden.

Detailsweergave voor het knooppunt Value.NativeQuery in het queryplan.

De query die hier wordt weergegeven, is mogelijk niet exact dezelfde query die naar de gegevensbron is verzonden, maar het is een goede benadering. In dit geval ziet u precies welke kolommen worden opgevraagd uit de tabel SalesOrderHeader en hoe deze tabel vervolgens wordt gefilterd met behulp van het veld TotalDue om alleen rijen op te halen waarbij de waarde voor dat veld groter is dan 1000. Het knooppunt er naast, 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.

Niet-gevouwen knooppunten controleren en acties overwegen om uw transformatie te vouwen

U hebt nu bepaald welke knooppunten niet kunnen worden gevouwen en lokaal worden geëvalueerd. In dit geval heeft alleen Table.LastN het knooppunt, maar in andere scenario's kan het er 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. Het is echter een goed idee om via trial and error te bepalen of uw query kan worden teruggevouwen.

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

  • Een aflopende sorteerstap op de kolom SalesOrderID in de tabel, omdat deze kolom bepaalt welke volgorde het eerst wordt en welke het laatst is ingevoerd.
  • Selecteer de bovenste vijf rijen omdat de tabel is gesorteerd. Deze transformatie wordt op dezelfde manier bereikt als bij 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 ervoor kan zorgen dat dit knooppunt volledig wordt teruggevouwen in de gegevensbron.

3. Wijzigingen in uw query implementeren

Implementeert het alternatief dat in de vorige sectie is besproken:

  1. Sluit het dialoogvenster queryplan en ga terug naar Power Query Editor.

  2. Verwijder de stap Onderste rijen behouden.

  3. Sorteer de kolom SalesOrderID in aflopende volgorde.

    De kolom SalesOrderID in aflopende volgorde sorteren met behulp van het menu automatisch filters.

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

    Gebruik het contextmenu van de tabel om de transformatie Bovenste rijen behouden te selecteren om alleen de bovenste vijf rijen te behouden.

Nadat u de wijzigingen hebt geïmplementeerd, controleert u opnieuw de step folding-indicatoren en controleert u of u hiermee een gevouwen indicator krijgt.

Alle step folding-indicatoren zijn groen en laten zien dat ze kunnen worden gevouwen. De laatste tabel bevat dezelfde rijen, maar in een andere volgorde.

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

Nieuw queryplan na het aanbrengen van de wijzigingen in de query, die nu alleen gevouwen knooppunten laat zien, met Value.NativeQuery met de volledige SQL-instructie waarmee de query wordt geëvalueerd.

Hoewel in dit artikel wordt voorstellen welk alternatief u kunt toepassen, is het belangrijkste doel dat u leert 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. Door de step folding-indicatoren te gebruiken, hebt u ook een beter idee van welke stappen verhinderen dat uw query wordt gevouwen.