Příklady optimalizace dotazů

Tento článek poskytuje několik příkladových scénářů pro všechny tři možné výsledky skládání dotazů. Obsahuje také několik návrhů, jak využít mechanismus optimalizace dotazů na maximum a jaký vliv to může mít na vaše dotazy.

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
• Description
• 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 nesklá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 fact_Sale tabulce vyberete transformaci Zachovat dolní řádky nalezené ve skupině Snížit řádky na kartě Domů .

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.

Návod

V tomto případě provedení této operace přinese výsledek posledních 10 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 Key, 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šimněte, že indikátory skládání dotazů pro Zachované dolní řádky a Zvolit sloupce jsou označené jako kroky, které se vyhodnocují mimo zdroj dat, tedy stroj 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 s názvem Zobrazit plán dotazu. 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ředchozím příkladu Value.NativeQuery a uzel, představují, která část dotazu se přesměruje do zdroje dat. Zbývající uzly, v tomto případě Table.LastN a Table.SelectColumns, které jsou zvýrazněné v obdélníku na předchozím obrázku, jsou vyhodnocovány modulem Power Query. Tyto dva uzly představují dvě transformace, které jste přidali, uchované dolní řádky a zvolení 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řipojuje se k databázi a odesílá požadavky na metadata, aby porozuměla jejím 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žívá 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 Key, Customer Key sloupec, Invoice Date Key sloupec, Description sloupec, a Quantity sloupec a pak vyberte OK.

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

Dále vyberte kontextovou 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ů: Porozumění vyhodnocení dotazu

Při kontrole podokna použitých kroků si všimnete, že indikátory skládání dotazů ukazují, že poslední transformace, kterou jste přidali, Kept bottom rows, je označena jako krok vyhodnocený mimo zdroj dat, tedy stroj 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řipojí se k databázi a odešle 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 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 Zvolit sloupce vyberte Sale Key, Customer Key, Invoice Date Key, Description a Quantity sloupce a pak zvolte OK.

Teď vytvoříte logiku, která tabulku seřadí tak, aby měla poslední prodeje v horní části tabulky. Vyberte sloupec Sale Key, který je primárním klíčem a přírůstkovou sekvencí nebo indexem tabulky. Seřaďte tabulku pomocí tohoto pole v sestupném pořadí pouze z kontextového menu pro sloupec.

Dále vyberte místní nabídku tabulky a zvolte transformaci Udržet 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šimněte, ž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 vyhodnocují 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.

Snímek obrazovky s plánem dotazu a příkazem SQL nalezeným uvnitř value.NativeQuery, který představuje požadavek 10 nejlepších záznamů tabulky "fact_Sale" seřazených pomocí pole Sale Key a pouze s poli Sale Key, Customer Key, 'Invoice Date Key', 'Description' a 'Quantity'.

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řipojí se k databázi a odešle 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ě se jedná o požadavek na získání pouze prvních 10 záznamů z tabulky fact_Sale, obsahující pouze nezbytná pole, a to po jejich seřazení v sestupném pořadí podle pole Sale Key.

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

Example	Označení	Čas v sekundách
Bez posouvání dotazů	None	361
Částečné posouvání dotazů	Částečný	184
Skládání celých dotazů	Full	31

Často se stává, že dotaz, který je plně kompatibilní s datovým zdrojem, předčí podobné dotazy, které nejsou plně kompatibilní se zdrojem 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 skládání dotazů snaží využít k minimalizaci vlivu těchto procesů na Power Query:

• Data v transitu
• Transformace spouštěné modulem Power Query

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

Data v transitu

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.

Example	Označení	Požadované řádky	Description
Bez posouvání dotazů	None	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ů	Full	10	Požadavek na pouze povinná pole a TOP 10 záznamů tabulky fact_Sale po seřazení podle pole Sale Key v sestupném pořadí.

Když požadujete data ze zdroje dat, musí zdroj dat vypočítat výsledky požadavku a pak je odeslat žadateli. I když už byly zmíněny výpočetní prostředky, může přesun dat prostřednictvím síťových prostředků ze zdroje dat do Power Query a následný efektivní příjem a příprava dat v Power Query na lokální transformace nějakou dobu trvat, a to v závislosti na velikosti dat.

V ukázkových příkladech musel Power Query požádat o více než 3,6 milionu řádků ze zdroje dat pro příklady bez skládání dotazu a částečného skládání dotazu. Příklad úplného skládání dotazů vyžadoval pouze 10 řádků. Pro požadovaná pole příklad bez překlápění dotazu požadoval všechna dostupná pole z tabulky. Částečná transformace dotazu i úplná transformace dotazu pouze odeslaly požadavek na přesně ta pole, která potřebovala.

Upozornění

Doporučujeme implementovat řešení přírůstkové aktualizace, která použí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 můžete zobrazit přesné uzly transformačních operací provedených modulem Power Query.

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

Example	Označení	Transformační uzly modulu Power Query
Bez posouvání dotazů	None	Table.LastN, Table.SelectColumns
Částečné posouvání dotazů	Částečný	Table.LastN
Skládání celých dotazů	Full	—

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	Description
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 pomocí jednoduchého filtru můžete výsledky obvykle filtrovat při průchodu operátorem a před přesunutím dat nemusíte shromažďovat všechny řádky. Table.SelectColumns a Table.ReorderColumns jsou to další příklady těchto operátorů.
úplné skenování	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.NestedJoin, a Table.Pivot.

Návod

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 účinnost 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 změnou uspořádání kroků. Pak znovu zkontrolujte plán dotazu posledního kroku dotazu a zjistěte, jestli plán dotazu vypadá lépe než předchozí. Například nový plán dotazu má méně uzlů než předchozí a většina uzlů jsou uzly streamování, nikoliv plného skenování. U zdrojů dat, které podporují skládání, všechny uzly v plánu dotazu kromě Value.NativeQuery a uzlů pro přístup ke zdroji dat představují transformace, které se neslož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í skládá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í nástrojů pro diagnostiku dotazů lépe porozumíte požadavkům odesílaným do zdroje dat, když jsou pro konektor k dispozici schopnosti skládání dotazů.
• Když zkombinujete data z více konektorů, Power Query se pokusí co nejvíce práce optimalizovat a delegovat na oba zdroje dat, přičemž dodržuje ú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ů, abyste předešli tomu, že vaše dotazy narazí na chybu brány firewall pro ochranu dat.
• Pomocí jiných nástrojů můžete zkontrolovat posouvání dotazů z pohledu požadavku přijatého zdrojem dat. V závislosti na příkladu v tomto článku můžete použít Microsoft SQL Server Profiler ke kontrole požadavků odeslaných Power Query a přijetí microsoft SQL Serverem.
• Pokud do plně složeného dotazu přidáte nový krok a tento nový krok se také složí, může Power Query odeslat nový požadavek do zdroje dat namísto využití výsledku z uložené mezipaměti. 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á tím, že Power Query znovu provádí dotazování na zdroj dat, místo aby pracovala s místní kopií dat.