Příklady posouvání dotazů

Tento článek obsahuje některé ukázkové scénáře pro každý ze tří možných výsledků pro posouvání dotazů. Obsahuje také několik návrhů, jak využít mechanismus posouvání dotazů na maximum a jaký vliv může mít v dotazech.

Scénář

Představte si scénář, kdy při použití databáze SQL Wide World Importers pro databázi AZURE Synapse Analytics máte za úkol vytvořit dotaz v Power Query, který se připojí k fact_Sale tabulce a načte posledních 10 prodejů pouze s následujícími poli:

• Prodejní klíč
• Klíč zákazníka
• Klíč data faktury
• Popis
• Množství

Poznámka:

Pro demonstrační účely tento článek používá databázi popsanou v kurzu načítání databáze Wide World Importers do Azure Synapse Analytics. Hlavním rozdílem v tomto článku je tabulka, která fact_Sale obsahuje pouze data pro rok 2000, s celkovým počtem 3 644 356 řádků.

I když výsledky nemusí přesně odpovídat výsledkům, které získáte podle kurzu z dokumentace ke službě Azure Synapse Analytics, cílem tohoto článku je prezentovat základní koncepty a dopad, který může mít posouvání dotazů ve vašich dotazech.

Tento článek představuje tři způsoby, jak dosáhnout stejného výstupu s různými úrovněmi posouvání dotazů:

• Bez posouvání dotazů
• Částečné posouvání dotazů
• Skládání celých dotazů

Příklad skládání dotazů

Důležité

Dotazy, které se spoléhají výhradně na nestrukturované zdroje dat nebo které nemají výpočetní modul, jako jsou soubory CSV nebo Excel, nemají možnosti posouvání dotazů. To znamená, že Power Query vyhodnotí všechny požadované transformace dat pomocí modulu Power Query.

Po připojení k databázi a přechodu k tabulce vyberete transformaci Zachovat dolní řádky nalezené ve skupině Snížit řádky na kartě Domů.fact_Sale

Po výběru této transformace se zobrazí nové dialogové okno. V tomto novém dialogovém okně můžete zadat počet řádků, které chcete zachovat. V tomto případě zadejte hodnotu 10 a pak vyberte OK.

Tip

V tomto případě při provádění této operace vznikne výsledek posledních deseti prodejů. Ve většině scénářů doporučujeme zadat explicitnější logiku, která definuje, které řádky jsou považovány za poslední použitím operace řazení v tabulce.

Dále vyberte transformaci Zvolit sloupce nalezené ve skupině Spravovat sloupce na kartě Domů . Potom můžete vybrat sloupce, které chcete z tabulky zachovat, a zbytek odebrat.

Nakonec v dialogovém okně Zvolit sloupce vyberte Sale Keypoložku , Customer Key, Invoice Date Key, Description, a Quantity sloupce a pak vyberte OK.

Následující ukázka kódu je úplný skript jazyka M pro dotaz, který jste vytvořili:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Kept bottom rows" = Table.LastN(Navigation, 10),
  #"Choose columns" = Table.SelectColumns(#"Kept bottom rows", {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"})
in
  #"Choose columns""

Bez posouvání dotazů: Principy vyhodnocení dotazu

V části Použitý postup v editoru Power Query si všimnete, že indikátory posouvání dotazů pro dolní řádkya Zvolit sloupce jsou označené jako kroky, které se vyhodnotí mimo zdroj dat nebo jinými slovy modul Power Query.

Můžete kliknout pravým tlačítkem myši na poslední krok dotazu, ten s názvem Zvolit sloupce a vybrat možnost, která čte plán Zobrazit dotaz. Cílem plánu dotazů je poskytnout vám podrobný přehled o tom, jak se dotaz spouští. Další informace o této funkci najdete v plánu dotazů.

Každé pole na předchozím obrázku se nazývá uzel. Uzel představuje rozpis operace pro splnění tohoto dotazu. Uzly, které představují zdroje dat, jako je SQL Server v příkladu výše a uzel Value.NativeQuery , představují, která část dotazu se přesměruje do zdroje dat. Zbývající uzly, v tomto případě Table.LastN zvýrazněné Table.SelectColumns v obdélníku na předchozím obrázku, se vyhodnocují modulem Power Query. Tyto dva uzly představují dvě transformace, které jste přidali, uchovávané dolní řádky a výběr sloupců. Zbývající uzly představují operace, ke kterým dochází na úrovni zdroje dat.

Pokud chcete zobrazit přesný požadavek odeslaný do zdroje dat, vyberte Zobrazit podrobnosti v Value.NativeQuery uzlu.

Tento požadavek na zdroj dat je v nativním jazyce vašeho zdroje dat. V tomto případě tento jazyk je SQL a tento příkaz představuje požadavek na všechny řádky a pole z fact_Sale tabulky.

Konzultace s touto žádostí o zdroj dat vám může pomoct lépe porozumět příběhu, který se plán dotazu pokusí sdělit:

• Sql.Database: Tento uzel představuje přístup ke zdroji dat. Připojení do databáze a odesílá požadavky na metadata, aby porozuměly jeho schopnostem.
• Value.NativeQuery: Představuje požadavek vygenerovaný Power Query pro splnění dotazu. Power Query odešle žádosti o data v nativním příkazu SQL do zdroje dat. V tomto případě představuje všechny záznamy a pole (sloupce) z fact_Sale tabulky. V tomto scénáři je tento případ nežádoucí, protože tabulka obsahuje miliony řádků a zájem je pouze v posledních 10.
• Table.LastN: Jakmile Power Query přijme všechny záznamy z fact_Sale tabulky, použije modul Power Query k filtrování tabulky a zachování pouze posledních 10 řádků.
• Table.SelectColumns: Power Query použije výstup Table.LastN uzlu a použije novou transformaci s názvem Table.SelectColumns, která vybere konkrétní sloupce, které chcete zachovat z tabulky.

Pro účely vyhodnocení musel tento dotaz stáhnout všechny řádky a pole z fact_Sale tabulky. Tento dotaz trval průměrně 6 minut a 1 sekundu, než se zpracuje ve standardní instanci toků dat Power BI (který odpovídá vyhodnocení a načítání dat do toků dat).

Příklad skládání částečných dotazů

Po připojení k databázi a přechodu k fact_Sale tabulce začněte výběrem sloupců, které chcete zachovat z tabulky. Vyberte transformaci Zvolit sloupce nalezené ve skupině Spravovat sloupce na kartě Domů. Tato transformace vám pomůže explicitně vybrat sloupce, které chcete zachovat z tabulky, a odebrat zbytek.

V dialogovém okně Zvolit sloupce vyberte Sale Keypoložku , Customer Key, Invoice Date KeyDescription, a sloupce a Quantity pak vyberte OK.

Teď vytvoříte logiku, která seřadí tabulku tak, aby měla poslední prodeje v dolní části tabulky. Sale Key Vyberte sloupec, což je primární klíč a přírůstková sekvence nebo index tabulky. Seřaďte tabulku pouze pomocí tohoto pole ve vzestupném pořadí z místní nabídky sloupce.

Dále vyberte místní nabídku tabulky a zvolte transformaci Zachovat dolní řádky .

V části Zachovat dolní řádky zadejte hodnotu 10 a pak vyberte OK.

Následující ukázka kódu je úplný skript jazyka M pro dotaz, který jste vytvořili:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
  #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
  #"Kept bottom rows"

Příklad skládání částečných dotazů: Principy vyhodnocení dotazu

Při kontrole podokna použitých kroků si všimnete, že indikátory posouvání dotazů ukazují, Kept bottom rowsže poslední přidaná transformace je označena jako krok, který se vyhodnotí mimo zdroj dat nebo jinými slovy modul Power Query.

Můžete kliknout pravým tlačítkem na poslední krok dotazu, ten s názvem Kept bottom rowsa vybrat možnost plánu dotazu, abyste lépe pochopili, jak se dotaz může vyhodnotit.

Každé pole na předchozím obrázku se nazývá uzel. Uzel představuje každý proces, ke kterému musí dojít (zleva doprava), aby se dotaz vyhodnotil. Některé z těchto uzlů je možné vyhodnotit ve zdroji dat, zatímco jiné, jako je uzel pro Table.LastN, reprezentovaný krokem Chované dolní řádky , se vyhodnocují pomocí modulu Power Query.

Pokud chcete zobrazit přesný požadavek odeslaný do zdroje dat, vyberte Zobrazit podrobnosti v Value.NativeQuery uzlu.

Tento požadavek je v nativním jazyce vašeho zdroje dat. V tomto případě tento jazyk je SQL a tento příkaz představuje požadavek pro všechny řádky, přičemž pouze požadovaná pole z fact_Sale tabulky seřazené podle Sale Key pole.

Konzultace s touto žádostí o zdroj dat vám může pomoct lépe porozumět příběhu, který se celý plán dotazu pokusí sdělit. Pořadí uzlů je sekvenční proces, který začíná vyžádáním dat ze zdroje dat:

• Sql.Database: Připojení do databáze a odesílá požadavky na metadata, aby porozuměly jeho schopnostem.
• Value.NativeQuery: Představuje požadavek vygenerovaný Power Query pro splnění dotazu. Power Query odešle žádosti o data v nativním příkazu SQL do zdroje dat. V tomto případě, který představuje všechny záznamy, s pouze požadovanými poli z fact_Sale tabulky v databázi seřazené vzestupně podle Sales Key pole.
• Table.LastN: Jakmile Power Query přijme všechny záznamy z fact_Sale tabulky, použije modul Power Query k filtrování tabulky a zachování pouze posledních 10 řádků.

Pro účely vyhodnocení musel tento dotaz stáhnout všechny řádky a pouze požadovaná pole z fact_Sale tabulky. Zpracování v standardní instanci toků dat Power BI trvalo průměrně 3 minuty a 4 sekundy (což odpovídá vyhodnocení a načítání dat do toků dat).

Příklad skládání celých dotazů

Po připojení k databázi a přechodu k fact_Sale tabulce začněte výběrem sloupců, které chcete z tabulky zachovat. Vyberte transformaci Zvolit sloupce nalezené ve skupině Spravovat sloupce na kartě Domů. Tato transformace vám pomůže explicitně vybrat sloupce, které chcete zachovat z tabulky, a odebrat zbytek.

V části Zvolit sloupce vyberte Sale Keypoložku , Customer Key, Invoice Date Key, Descriptiona Quantity sloupce a pak vyberte OK.

Teď vytvoříte logiku, která seřadí tabulku tak, aby měla poslední prodeje v horní části tabulky. Sale Key Vyberte sloupec, což je primární klíč a přírůstková sekvence nebo index tabulky. Seřaďte tabulku pouze pomocí tohoto pole v sestupném pořadí z místní nabídky sloupce.

Dále vyberte místní nabídku tabulky a zvolte transformovat horní řádky .

Do pole Zachovat horní řádky zadejte hodnotu 10 a pak vyberte OK.

Následující ukázka kódu je úplný skript jazyka M pro dotaz, který jste vytvořili:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
  #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
  #"Kept top rows"

Příklad skládání celých dotazů: Principy vyhodnocení dotazu

Při kontrole podokna použitých kroků si všimnete, že indikátory posouvání dotazů ukazují, že přidané transformace, Výběr sloupců, Seřazené řádky a Chované horní řádky jsou označené jako kroky, které se vyhodnotí ve zdroji dat.

Můžete kliknout pravým tlačítkem myši na poslední krok dotazu, ten s názvem Kept top rows a vybrat možnost, která čte plán dotazu.

Tento požadavek je v nativním jazyce vašeho zdroje dat. V tomto případě tento jazyk je SQL a tento příkaz představuje požadavek na všechny řádky a pole z fact_Sale tabulky.

Konzultace s dotazem na tento zdroj dat vám může pomoct lépe porozumět příběhu, který se celý plán dotazu pokusí sdělit:

• Sql.Database: Připojení do databáze a odesílá požadavky na metadata, aby porozuměly jeho schopnostem.
• Value.NativeQuery: Představuje požadavek vygenerovaný Power Query pro splnění dotazu. Power Query odešle žádosti o data v nativním příkazu SQL do zdroje dat. V tomto případě představuje požadavek pouze pro prvních 10 záznamů fact_Sale tabulky, přičemž po seřazení v sestupném pořadí pomocí Sale Key tohoto pole jsou pouze povinná pole.

Poznámka:

I když neexistuje žádná klauzule, kterou je možné použít k výběru dolních řádků tabulky v jazyce T-SQL, existuje klauzule TOP, která načte horní řádky tabulky.

Pro účely vyhodnocení tento dotaz stáhne jenom 10 řádků s pouze poli, která jste požadovali z fact_Sale tabulky. Zpracování tohoto dotazu trvalo v průměru 31 sekund ve standardní instanci toků dat Power BI (které odpovídá vyhodnocení a načítání dat do toků dat).

Porovnání výkonu

Abyste lépe pochopili, jaké vliv má posouvání dotazů v těchto dotazech, můžete aktualizovat dotazy, zaznamenat dobu potřebnou k úplné aktualizaci jednotlivých dotazů a porovnat je. Pro zjednodušení tento článek poskytuje průměrné časování aktualizací zachycené pomocí mechanika aktualizace toků dat Power BI při připojování k vyhrazenému prostředí Azure Synapse Analytics s dw2000c jako úrovní služby.

Čas aktualizace pro každý dotaz byl následující:

Příklad	Popisek	Čas v sekundách
Bez posouvání dotazů	Nic	361
Částečné posouvání dotazů	Částečná	184
Skládání celých dotazů	Úplný	31

Často se jedná o případ, že dotaz, který se plně přeloží zpět do zdroje dat, přepne podobné dotazy, které se zcela nepřeloží zpět do zdroje dat. Existuje mnoho důvodů, proč tomu tak je. Tyto důvody se liší od složitosti transformací, které dotaz provádí, až po optimalizace dotazů implementované ve zdroji dat, jako jsou indexy a vyhrazené výpočetní prostředky a síťové prostředky. Stále existují dva konkrétní klíčové procesy, které se dotaz posouvání snaží použít, což minimalizuje vliv, který oba tyto procesy mají s Power Query:

• Přenášená data
• Transformace spouštěné modulem Power Query

Následující části vysvětlují, že tyto dva procesy mají v dříve zmíněných dotazech vliv.

Přenášená data

Když se dotaz spustí, pokusí se načíst data ze zdroje dat jako jeden z prvních kroků. Jaká data se načítají ze zdroje dat, je definována mechanismem posouvání dotazů. Tento mechanismus identifikuje kroky z dotazu, které je možné přesměrovat do zdroje dat.

Následující tabulka uvádí počet řádků požadovaných z fact_Sale tabulky databáze. Tabulka obsahuje také stručný popis příkazu SQL odeslaného k vyžádání těchto dat ze zdroje dat.

Příklad	Popisek	Požadované řádky	Popis
Bez posouvání dotazů	Nic	3644356	Žádost o všechna pole a všechny záznamy z fact_Sale tabulky
Částečné posouvání dotazů	Částečná	3644356	Žádost o všechny záznamy, ale pouze povinná pole z fact_Sale tabulky po seřazení podle Sale Key pole
Skládání celých dotazů	Úplný	10	Požadavek pouze na požadovaná pole a záznamy fact_Sale TOP 10 tabulky po seřazení v sestupném pořadí podle Sale Key pole

Při vyžádání dat ze zdroje dat musí zdroj dat vypočítat výsledky požadavku a pak je odeslat žadateli. I když už byly výpočetní prostředky zmíněny, síťové prostředky přesunu dat ze zdroje dat do Power Query a pak mají Power Query možnost efektivně přijímat data a připravit je na transformace, ke kterým dojde místně, může nějakou dobu trvat v závislosti na velikosti dat.

Pro ukázkové příklady musel Power Query požádat o více než 3,6 milionu řádků ze zdroje dat za účelem bez posouvání dotazů a částečných příkladů posouvání dotazů. V případě úplného skládání dotazů požadoval pouze 10 řádků. U požadovaných polí nepožadoval žádný příklad posouvání dotazu všechny dostupné pole z tabulky. Částečné posouvání dotazů i úplné příklady posouvání dotazů odeslaly požadavek pouze na pole, která potřebovala.

Upozornění

Doporučujeme implementovat řešení přírůstkové aktualizace, která využívají posouvání dotazů pro dotazy nebo tabulky s velkým množstvím dat. Různé integrace produktů Power Query implementují vypršení časových limitů pro ukončení dlouhotrvajících dotazů. Některé zdroje dat také implementují časové limity u dlouhotrvajících relací a snaží se na jejich serverech spouštět nákladné dotazy. Další informace: Použití přírůstkové aktualizace s toky dat a přírůstkové aktualizace pro sémantické modely

Transformace spouštěné modulem Power Query

V tomto článku se dozvíte, jak můžete použít plán dotazu, abyste lépe pochopili, jak se váš dotaz může vyhodnotit. V plánu dotazu uvidíte přesné uzly transformovacích operací, které budou provedeny modulem Power Query.

Následující tabulka ukazuje uzly z plánů dotazů předchozích dotazů, které by modul Power Query vyhodnotil.

Příklad	Popisek	Transformační uzly modulu Power Query
Bez posouvání dotazů	Nic	Table.LastN, Table.SelectColumns
Částečné posouvání dotazů	Částečná	Table.LastN
Skládání celých dotazů	Úplný	—

V příkladech uvedených v tomto článku nevyžaduje úplný příklad posouvání dotazů žádnou transformaci uvnitř modulu Power Query, protože požadovaná výstupní tabulka pochází přímo ze zdroje dat. Naproti tomu ostatní dva dotazy vyžadovaly, aby se některé výpočty prováděly v modulu Power Query. Vzhledem k množství dat, která je potřeba zpracovat těmito dvěma dotazy, proces pro tyto příklady trvá déle než celý příklad posouvání dotazů.

Transformace je možné seskupit do následujících kategorií:

Typ operátoru	Popis
Vzdálené	Operátory, které jsou uzly zdroje dat. Vyhodnocení těchto operátorů probíhá mimo Power Query.
Streamování	Operátory jsou průchozí operátory. Table.SelectRows Například jednoduchý filtr může výsledky obvykle filtrovat při průchodu operátorem a před přesunutím dat nebude nutné shromáždit všechny řádky. Table.SelectColumns a Table.ReorderColumns jsou to další příklady těchto operátorů.
Úplná kontrola	Operátory, které potřebují shromáždit všechny řádky, aby se data mohly přesunout k dalšímu operátoru v řetězci. Pokud například chcete seřadit data, musí Power Query shromáždit všechna data. Další příklady operátorů úplné kontroly jsou Table.Group, Table.NestedJoina Table.Pivot.

Tip

I když ne každá transformace je z hlediska výkonu stejná, ve většině případů je méně transformací obvykle lepší.

Důležité informace a návrhy

• Při vytváření nového dotazu postupujte podle osvědčených postupů uvedených v osvědčených postupech v Power Query.
• Pomocí indikátorů posouvání dotazů zkontrolujte, které kroky brání posouvání dotazu. Pokud je to potřeba, přeuspořádejte je, abyste zvýšili skládání.
• Pomocí plánu dotazu určete, které transformace probíhají v modulu Power Query pro konkrétní krok. Zvažte úpravu existujícího dotazu opětovným uspořádáním kroků. Pak znovu zkontrolujte plán dotazu posledního kroku dotazu a zjistěte, jestli plán dotazu vypadá lépe než předchozí. Nový plán dotazů má například méně uzlů než předchozí a většina uzlů je uzly Streamování a nikoli úplná kontrola. U zdrojů dat, které podporují posouvání, představují všechny uzly v jiném plánu dotazu než Value.NativeQuery uzly pro přístup ke zdroji dat transformace, které se nepřeložily.
• Pokud je k dispozici, můžete použít možnost Zobrazit nativní dotaz (nebo zobrazit dotaz zdroje dat), abyste měli jistotu, že se dotaz dá přeložit zpět do zdroje dat. Pokud je tato možnost pro váš krok zakázaná a používáte zdroj, který ji normálně povolí, vytvořili jste krok, který zastaví posouvání dotazů. Pokud používáte zdroj, který tuto možnost nepodporuje, můžete se spolehnout na indikátory posouvání dotazů a plán dotazů.
• Pomocí diagnostických nástrojů dotazů lépe porozumíte požadavkům odesílanými do zdroje dat, když jsou pro konektor k dispozici možnosti posouvání dotazů.
• Při kombinování dat z použití více konektorů se Power Query pokusí odeslat co nejvíce práce do obou zdrojů dat a zároveň dodržovat úrovně ochrany osobních údajů definované pro každý zdroj dat.
• Přečtěte si článek o úrovních ochrany osobních údajů a chraňte své dotazy před chybou brány firewall ochrany osobních údajů dat.
• Pomocí jiných nástrojů můžete zkontrolovat posouvání dotazů z pohledu přijatého požadavku zdrojem dat. Na základě příkladu v tomto článku můžete použít Microsoft SQL Server Profiler ke kontrole požadavků odesílaných Power Query a přijetí Microsoft SQL Serverem.
• Pokud do plně přeloženého dotazu přidáte nový krok a nový krok se také přeloží, Může Power Query odeslat nový požadavek do zdroje dat místo použití verze uložené v mezipaměti předchozího výsledku. V praxi může tento proces vést k zdánlivě jednoduchým operacím s malým množstvím dat, která se v náhledu aktualizují déle, než se čekalo. Tato delší aktualizace je způsobená opětovným dotazováním zdroje dat v Power Query, a ne kvůli tomu, aby se nevypracovává místní kopie dat.