Plán dotazů pro Power Query

Plán dotazů pro Power Query je funkce, která poskytuje lepší přehled o vyhodnocení dotazu. Je užitečné určit, proč se konkrétní dotaz nemusí v určitém kroku sbalit.

V praktickém příkladu tento článek ukazuje hlavní případ použití a potenciální výhody použití funkce plánu dotazů ke kontrole kroků dotazu. Příklady použité v tomto článku byly vytvořeny pomocí ukázkové databáze AdventureWorksLT pro Azure SQL Server, kterou si můžete stáhnout z ukázkových databází AdventureWorks.

Poznámka:

Funkce plánu dotazů pro Power Query je dostupná jenom v Power Query Online.

Tento článek je rozdělený do řady doporučených kroků pro interpretaci plánu dotazů. Toto jsou tyto kroky:

1. Zkontrolujte indikátory sloučení dotazů.
2. Vyberte krok dotazu a zkontrolujte jeho plán dotazu.
3. Implementujte změny dotazu.

Pomocí následujících kroků vytvořte dotaz ve vlastním prostředí Power Query Online.

1. V Power Query – Zvolte zdroj dat a vyberte prázdný dotaz.

2. Nahraďte skript prázdného dotazu následujícím dotazem.

   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. Změňte servername a database zadejte správné názvy pro vaše vlastní prostředí.

4. (Volitelné) Pokud se pokoušíte připojit k serveru a databázi pro místní prostředí, nezapomeňte pro toto prostředí nakonfigurovat bránu.

5. Vyberte Další.

6. V Editoru Power Query vyberte Konfigurovat připojení a zadejte přihlašovací údaje ke zdroji dat.

Poznámka:

Další informace o připojení k SQL Serveru najdete v databázi SQL Serveru.

Po provedení těchto kroků bude váš dotaz vypadat jako ten na následujícím obrázku.

Tento dotaz se připojí k tabulce SalesOrderHeader a vybere několik sloupců z posledních pěti objednávek s hodnotou TotalDue nad 1000.

Poznámka:

Tento článek používá zjednodušený příklad k prezentaci této funkce, ale koncepty popsané v tomto článku platí pro všechny dotazy. Před čtením plánu dotazů doporučujeme mít dobré znalosti o skládání dotazů. Další informace o posouvání dotazů najdete v tématu Základy posouvání dotazů.

1. Kontrola indikátorů posouvání dotazů

Poznámka:

Před přečtením této části doporučujeme projít si článek o indikátorech posouvání dotazů.

Vaším prvním krokem v tomto procesu je zkontrolovat dotaz a věnovat pozornost indikátorům posouvání dotazů. Cílem je zkontrolovat kroky, které jsou označené jako nerozbalené. Poté uvidíte, zda úpravy v rámci dotazu mohou tyto transformace zcela odstranit.

V tomto příkladu je jediným krokem, který nelze sbalit, Uchované dolní řádky, které lze snadno identifikovat pomocí ukazatele není sbaleno. Tento krok je také posledním krokem dotazu.

Cílem je teď zkontrolovat tento krok a pochopit, co se vrací zpět ke zdroji dat a co se nedá vrátit.

2. Vyberte krok dotazu a zkontrolujte jeho plán dotazu.

Identifikovali jste krok Uložené dolní řádky jako krok, který vás zajímá, protože se nevrací zpět ke zdroji dat. Klikněte pravým tlačítkem myši na krok a vyberte možnost Zobrazit plán dotazu . Tato akce zobrazí nové dialogové okno, které obsahuje diagram pro plán dotazu vybraného kroku.

Power Query se snaží optimalizovat dotaz tím, že využívá opožděné vyhodnocení a posouvání dotazů, jak je uvedeno v základech posouvání dotazů. Tento plán dotazu představuje optimalizovaný překlad dotazu M do nativního dotazu odeslaného do zdroje dat. Zahrnuje také všechny transformace, které provádí modul Power Query. Pořadí, ve kterém se uzly zobrazují, se řídí pořadím dotazu počínaje posledním krokem nebo výstupem dotazu, který je znázorněný úplně nalevo od diagramu. V tomto případě se jedná o uzel Table.LastN , který představuje krok Chovaných dolních řádků .

V dolní části dialogového okna je pruh s ikonami, které vám pomůžou přiblížit nebo oddálit zobrazení plánu dotazu a další tlačítka, která vám pomůžou se správou zobrazení. U předchozího obrázku se možnost Přizpůsobit zobrazení z tohoto pruhu použila k lepšímu ocenění uzlů.

Poznámka:

Plán dotazu představuje optimalizovaný plán. Při vyhodnocování dotazu se engine pokouší integrovat všechny operátory do zdroje dat. V některých případech může dokonce dojít k vnitřnímu přeuspořádání kroků, aby se maximalizovalo skládání. S ohledem na tento proces uzly a operátoři, kteří zůstali v tomto optimalizovaném plánu dotazů, obvykle obsahují dotaz na "složený" zdroj dat. Všechny operátory, které nelze zjednodušit, se vyhodnocují lokálně.

Odlišit složené uzly od jiných uzlů

Uzly v tomto diagramu můžete identifikovat jako dvě skupiny:

• Složené uzly: Tento uzel může být buď Value.NativeQuery nebo uzly "zdroje dat" jako Sql.Database. Tyto uzly lze také identifikovat pomocí vzdáleného popisku pod názvem jejich funkce.
• Neskládané uzly: Jiné operátory tabulky, například Table.SelectRows, Table.SelectColumns, a další funkce, které se nedaly složit. Tyto uzly lze také identifikovat pomocí popisků Úplné prohledávání a Streamování.

Následující obrázek znázorňuje složené uzly uvnitř červeného obdélníku. Zbývající uzly nebylo možné přeložit zpět do zdroje dat. Musíte zkontrolovat zbývající uzly, protože cílem je pokusit se o to, aby tyto uzly pokračovaly zpět do zdroje dat.

Pokud chcete zobrazit rozšířené informace, můžete vybrat Zobrazit podrobnosti v dolní části některých uzlů. Například podrobnosti Value.NativeQuery uzlu zobrazují nativní dotaz (v SQL), který se odesílá do zdroje dat.

Zde uvedený dotaz nemusí být úplně stejný dotaz odeslaný do zdroje dat, ale je to dobrá aproximace. V tomto případě vám přesně řekne, jaké sloupce se dotazují z tabulky SalesOrderHeader. Potom, jak filtruje tuto tabulku pomocí pole TotalDue, aby získala pouze řádky, ve kterých je hodnota pro toto pole větší než 1 000. Uzel vedle něj Table.LastN se počítá místně modulem Power Query, protože ho nejde přeložit.

Poznámka:

Operátory nemusí přesně odpovídat funkcím použitým ve skriptu dotazu.

Zkontrolujte nerozvinuté uzly a zvažte akce, které vám umožní, aby se vaše transformace složila.

Teď jste zjistili, které uzly nemohou být složeny a které je možné vyhodnotit lokálně. V tomto případě má pouze Table.LastN uzel, ale v jiných scénářích může mít mnohem více.

Cílem je použít změny ve vašem dotazu tak, aby mohl být krok sbalen. Některé změny, které můžete implementovat, můžou být v rozsahu od změny uspořádání kroků až po použití alternativní logiky pro váš dotaz, který je explicitnější pro zdroj dat. To neznamená, že všechny dotazy a operace jsou zpracovatelné při aplikaci některých změn. Je však dobrým zvykem zjistit pomocí pokusů a omylů, zda lze váš dotaz optimalizovat.

Vzhledem k tomu, že zdrojem dat je databáze SQL Serveru, je-li cílem načíst posledních pět objednávek z tabulky, je dobrou alternativou využít klauzule TOP a ORDER BY v SQL. Vzhledem k tomu, že v SQL není žádná klauzule BOTTOM, Table.LastN transformace v PowerQuery se nedá přeložit do SQL. Můžete odebrat tento krok Table.LastN a nahradit ho:

• Seřadit sestupně podle sloupce SalesOrderID v tabulce, protože tento sloupec určuje, které pořadí následuje jako první a které bylo zadáno jako poslední.
• Vyberte prvních pět řádků po seřazení tabulky, tato transformace dosáhne stejného výsledku jako při ponechání dolních řádků (Table.LastN).

Tato alternativa je ekvivalentní původnímu dotazu. I když tato alternativa v teorii vypadá dobře, musíte provést změny, abyste zjistili, jestli tato alternativa způsobí, že se tento uzel plně spojí zpět se zdrojem dat.

3. Implementace změn dotazu

Implementujte alternativu popsanou v předchozí části:

1. Zavřete dialogové okno plánu dotazu a vraťte se do Editoru Power Query.

2. Odeberte krok s uloženými dolními řádky .

3. Seřaďte sloupec SalesOrderID v sestupném pořadí.

   

4. Vyberte ikonu tabulky v levém horním rohu zobrazení náhledu dat a vyberte možnost číst horní řádky. V dialogovém okně předejte jako argument číslo pět a stiskněte OK.

   

Po implementaci změn znovu zkontrolujte indikátory skládání dotazů a podívejte se, jestli vám zobrazuje indikátor skládání.

Teď je čas zkontrolovat plán dotazu z předchozího kroku, který nyní zachová horní řádky. Nyní tam jsou pouze složené uzly. Vyberte Zobrazit podrobnosti pod Value.NativeQuery, abyste zkontrolovali, který dotaz se posílá do databáze.

I když tento článek navrhuje, jakou alternativu použít, hlavním cílem je, abyste se naučili, jak využít plán dotazů k prozkoumání skládání dotazů. Tento článek také poskytuje přehled o tom, co se odesílá do vašeho zdroje dat a jaké transformace se provádějí místně.

Kód můžete upravit, abyste viděli dopad, jaký má v dotazu. Pomocí indikátorů posouvání dotazů máte také lepší představu o tom, které kroky brání posouvání dotazu.