Plan zapytania dla dodatku Power Query

Plan zapytania dla dodatku Power Query to funkcja, która zapewnia lepszy widok oceny zapytania. Warto pomóc ustalić, dlaczego dane zapytanie może nie funkcjonować na danym etapie.

W praktycznym przykładzie w tym artykule przedstawiono główny przypadek użycia i potencjalne korzyści wynikające z używania funkcji planu zapytania w celu przejrzenia kroków zapytania. Przykłady użyte w tym artykule zostały utworzone przy użyciu przykładowej bazy danych AdventureWorksLT dla programu Azure SQL Server, którą można pobrać z przykładowych baz danych AdventureWorks.

Diagram sugerowanego przebiegu korzystania z funkcji planu zapytania, obejmującego przegląd wskaźników składania zapytań, analizę planu zapytania dla wybranego kroku i wdrażanie zmian wynikających z przeglądu planu zapytania.

Ten artykuł jest podzielony w serii zalecanych kroków w celu zinterpretowania planu zapytania. Poniżej przedstawiono następujące kroki:

1. Przejrzyj wskaźniki składania zapytań.
2. Wybierz krok zapytania, aby przejrzeć jego plan zapytania.
3. Zaimplementuj zmiany w zapytaniu.

Wykonaj poniższe kroki, aby utworzyć zapytanie we własnym środowisku usługi Power Query Online.

1. W dodatku Power Query — wybierz źródło danych, wybierz pozycję Puste zapytanie.

2. Zastąp skrypt pustego zapytania następującym zapytaniem.

   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. Zmień servername i database przy użyciu poprawnych nazw dla własnego środowiska.

4. (Opcjonalnie) Jeśli próbujesz nawiązać połączenie z serwerem i bazą danych dla środowiska lokalnego, pamiętaj o skonfigurowaniu bramy dla tego środowiska.

5. Wybierz Dalej.

6. W Edytorze Power Query wybierz pozycję Konfiguruj połączenie i podaj poświadczenia do źródła danych.

Po wykonaniu tych kroków zapytanie będzie wyglądać jak na poniższej ilustracji.

To zapytanie łączy się z tabelą SalesOrderHeader i wybiera kilka kolumn z ostatnich pięciu zamówień z wartością TotalDue powyżej 1000.

1. Przejrzyj wskaźniki składania zapytań

Pierwszym krokiem w tym procesie jest przejrzenie zapytania i zwrócenie uwagi na wskaźniki składania zapytań. Celem jest przejrzenie kroków oznaczonych jako niezłożone. Następnie możesz sprawdzić, czy dokonywanie zmian w ogólnym zapytaniu może spowodować całkowite zawinięcie tych przekształceń.

W tym przykładzie jedyny krok, którego nie można złożyć, to Zachowanie dolnych wierszy, co można łatwo rozpoznać dzięki wskaźnikowi kroku niezłożonego. Ten krok jest również ostatnim krokiem zapytania.

Teraz celem jest przejrzenie tego kroku i zrozumienie, co jest składane z powrotem do źródła danych i czego nie można złożyć.

2. Wybierz krok zapytania, aby przejrzeć plan zapytania

Krok Zachowano dolne wiersze został zidentyfikowany jako interesujący, ponieważ nie wraca do źródła danych. Kliknij prawym przyciskiem myszy krok i wybierz opcję Wyświetl plan zapytania . Ta akcja powoduje wyświetlenie nowego okna dialogowego zawierającego diagram planu zapytania wybranego kroku.

Power Query próbuje zoptymalizować zapytanie, korzystając z leniwego obliczania i składania zapytań, jak wspomniano w Podstawy składania zapytań. Ten plan zapytania reprezentuje zoptymalizowane tłumaczenie zapytania M na zapytanie natywne wysyłane do źródła danych. Obejmuje również wszelkie przekształcenia wykonywane przez silnik Power Query. Kolejność wyświetlania węzłów jest zgodna z kolejnością zapytania rozpoczynającą się od ostatniego kroku lub danych wyjściowych zapytania, które jest reprezentowane po lewej stronie diagramu. W tym przypadku jest to węzeł Table.LastN, reprezentujący krok Dolne wiersze przechowywane.

W dolnej części okna dialogowego znajduje się pasek z ikonami, które ułatwiają powiększanie lub wyłączanie widoku planu zapytania oraz inne przyciski ułatwiające zarządzanie widokiem. Na poprzedniej ilustracji opcja Dopasuj do wyświetlenia z tego paska została użyta do lepszego doceninia węzłów.

Identyfikowanie zwiniętych węzłów od innych węzłów

Węzły na tym diagramie można zidentyfikować jako dwie grupy:

• Złożone węzły: ten węzeł może być albo węzłem Value.NativeQuery, albo węzłem "źródła danych", takim jak Sql.Database. Te węzły można również zidentyfikować z etykietą zdalną pod nazwą funkcji.
• Nieskładane węzły: inne operatory tabeli, takie jak Table.SelectRows, Table.SelectColumnsi inne funkcje, których nie można składać. Te węzły można również zidentyfikować przy użyciu etykiet Pełne skanowanie i Przesyłanie strumieniowe.

Na poniższej ilustracji przedstawiono złożone węzły wewnątrz czerwonego prostokąta. Nie można było przywrócić pozostałych węzłów do źródła danych. Należy przejrzeć pozostałe węzły, ponieważ celem jest doprowadzenie do ponownego połączenia tych węzłów ze źródłem danych.

Możesz wybrać pozycję Wyświetl szczegóły w dolnej części niektórych węzłów, aby wyświetlić informacje rozszerzone. Na przykład szczegóły węzła Value.NativeQuery pokazują zapytanie natywne (w języku SQL), które jest wysyłane do źródła danych.

Przedstawione tutaj zapytanie może nie być dokładnie tym samym zapytaniem wysłanym do źródła danych, ale jest to dobre przybliżenie. W tym przypadku dokładnie informuje, które kolumny są zapytane z tabeli SalesOrderHeader. Następnie sposób filtrowania tej tabeli przy użyciu pola TotalDue w celu pobrania tylko wierszy, w których wartość tego pola jest większa niż 1000. Węzeł znajdujący się obok Table.LastN jest obliczany lokalnie przez silnik Power Query, ponieważ nie można go złożyć.

Przejrzyj niefoldowane węzły i rozważ akcje, aby złożyć przekształcenie

Teraz ustalono, których węzłów nie można zwijać i które można ocenić lokalnie. Ten przypadek ma tylko węzeł Table.LastN, ale w innych scenariuszach może mieć o wiele więcej.

Celem jest zastosowanie zmian w zapytaniu, aby można było zwinąć krok. Niektóre zmiany, które można zaimplementować, mogą wahać się od zmiany kolejności kroków w celu zastosowania alternatywnej logiki do zapytania, które jest bardziej jawne dla źródła danych. Nie oznacza to, że wszystkie zapytania i wszystkie operacje można zmodyfikować, wprowadzając jakieś zmiany. Dobrym rozwiązaniem jest ustalenie metodą prób i błędów, czy zapytanie można zwinąć z powrotem.

Ponieważ źródło danych jest bazą danych programu SQL Server, jeśli celem jest pobranie ostatnich pięciu zamówień z tabeli, dobrym rozwiązaniem jest skorzystanie z klauzul TOP i ORDER BY w języku SQL. Ponieważ w języku SQL nie ma klauzuli BOTTOM, Table.LastN nie można przetłumaczyć przekształcenia w usłudze PowerQuery na język SQL. Możesz usunąć Table.LastN krok i zastąpić go:

• Krok sortowania malejącego według kolumny SalesOrderID w tabeli, ponieważ ta kolumna określa, które zamówienie jest pierwsze i które zostało wprowadzone jako ostatnie.
• Wybierz pięć pierwszych wierszy, ponieważ tabela została posortowana, ta transformacja wykonuje to samo, co pozostawienie dolnych wierszy ().

Ta alternatywa jest równoważna oryginalnemu zapytaniu. Chociaż ta alternatywa w teorii wydaje się dobra, musisz wprowadzić zmiany, aby sprawdzić, czy ta alternatywa sprawia, że ten węzeł w pełni powraca do źródła danych.

3. Implementowanie zmian w zapytaniu

Zaimplementuj alternatywę omówiną w poprzedniej sekcji:

1. Zamknij okno dialogowe planu zapytania i wróć do Edytora Power Query.

2. Usuń krok Zachowane dolne wiersze.

3. Sortuj kolumnę SalesOrderID w kolejności malejącej.

   

4. Wybierz ikonę tabeli w lewym górnym rogu widoku podglądu danych i wybierz opcję Zachowaj pierwsze wiersze. W oknie dialogowym przekaż liczbę pięć jako argument i naciśnij przycisk OK.

   

Po zaimplementowaniu zmian ponownie sprawdź wskaźniki składania zapytań, czy otrzymujesz zwinięty wskaźnik.

Teraz nadszedł czas, aby przeanalizować plan zapytania dla ostatniego kroku, którym jest teraz Zachowaj pierwsze wiersze. Teraz istnieją tylko złożone węzły. Wybierz pozycję Wyświetl szczegóły w obszarze Value.NativeQuery , aby sprawdzić, które zapytanie jest wysyłane do bazy danych.

Chociaż ten artykuł sugeruje, jaka alternatywa ma być stosowana, głównym celem jest zapoznanie się z planem zapytania w celu zbadania składania zapytań. W tym artykule przedstawiono również informacje o tym, co jest wysyłane do źródła danych i jakie przekształcenia są wykonywane lokalnie.

Możesz dostosować kod, aby zobaczyć wpływ, jaki ma on w zapytaniu. Korzystając ze wskaźników składania zapytań, lepiej jest również zrozumieć, które kroki uniemożliwiają składanie zapytania.