Scalanie dużych tabel trwa bardzo długo - jak przyspieszyć Power Query

Power Query w Excelu, Power BI i Fabric

Scalanie dużych tabel trwa bardzo długo - jak przyspieszyć Power Query

W skrócie

Scalanie w Power Query działa wolno przede wszystkim przy dużych tabelach faktów, na przykład sprzedaży z kilku lat łączonej z katalogiem produktów, danymi klientów albo budżetem. Każde odświeżenie trwa wtedy minutami, a praca w edytorze polega na czekaniu na podgląd. Pokazujemy, od czego zależy czas scalania, jak sprawdzić, gdzie jest wykonywane, i jakie zmiany w zapytaniu zmniejszają ilość danych do pobrania i połączenia. Opieramy się na lekcji o składaniu zapytań, dokumentacji Microsoft i testach w polskim Excelu.

Jak to wygląda w praktyce

Zapytanie ze scaleniem odświeża się wyraźnie dłużej niż łączone zapytania osobno, a w edytorze kliknięcie każdego kroku po scaleniu oznacza czekanie na podgląd. W Power BI Desktop, przy zapytaniu do bazy danych scalonym z plikiem, menu kontekstowe kroku scalania ma wyszarzoną pozycję Wyświetl zapytanie natywne, czyli od tego miejsca kroki nie są wykonywane przez serwer.

Komunikatu błędu zwykle nie ma. Wyjątkiem jest brak pamięci przy bardzo dużych tabelach: odświeżenie kończy się wtedy błędem Expression.Error: Za mało pamięci, nie można kontynuować obliczania. (ang. Evaluation ran out of memory and can't continue.). Dokumentacja Microsoft wymienia scalenia obok sortowania, grupowania i usuwania duplikatów jako operacje zużywające dużo pamięci.

Dlaczego tak się dzieje

Czas scalania zależy od tego, gdzie się ono odbywa i ile danych trzeba do niego pobrać. W lekcji o składaniu zapytań (ang. query folding, czyli zamianie kroków na jedno zapytanie wykonywane przez źródło) pokazujemy, że scalanie tabel z tej samej bazy zwykle składa się do serwera, a scalanie dwóch różnych źródeł, na przykład bazy i pliku, przerywa składanie. Power Query pobiera wtedy obie tabele i łączy je lokalnie, w pamięci komputera. Pliki CSV, skoroszyty i foldery nie składają się wcale, więc ich scalanie zawsze odbywa się lokalnie.

Do tego dochodzą trzy rzeczy w samym zapytaniu. Każda zbędna kolumna i każdy zbędny wiersz to dane do pobrania i porównania. Kilka zapytań odwołujących się do tego samego, ciężkiego źródła może wczytywać je osobno. Przy listach SharePoint rozwijanie powiązanego rekordu generuje według dokumentacji Microsoft osobne wywołanie drugiej tabeli dla każdego wiersza pierwszej. Dokumentacja zaleca też, żeby operacje zużywające dużo pamięci, w tym scalenia, składały się do źródła.

Jak to rozwiązać krok po kroku

1. Sprawdź, gdzie wykonuje się scalenie. W Power BI Desktop kliknij prawym przyciskiem krok scalania na liście Zastosowane kroki. Aktywna pozycja Wyświetl zapytanie natywne oznacza, że scalenie złożyło się do bazy, a wyszarzona, że odbywa się lokalnie. W Power Query Online tę samą informację daje wskaźnik przy kroku.
2. Scalaj w bazie, jeśli obie tabele w niej są. Pobierz słownik z tej samej bazy co sprzedaż, a nie z osobnego pliku, i ustaw scalenie przed krokami, które przerywają składanie, takimi jak kolumna indeksu. Gdy słownik istnieje tylko w pliku, a scalenie jest wąskim gardłem, rozważ trzymanie go w bazie jako tabeli.
3. Usuń zbędne kolumny i odfiltruj wiersze przed scaleniem. W obu zapytaniach zaznacz potrzebne kolumny, na karcie Strona główna rozwiń Usuń kolumny i wybierz Usuń inne kolumny, a filtry, na przykład zakres dat, ustaw na początku zapytania. W słowniku zostaw tylko klucz i kolumny, które naprawdę rozwijasz.
4. Nie ładuj zapytań pośrednich. Zapytania pomocnicze zapisz jako samo połączenie: Zamknij i załaduj do, a w oknie Importowanie danych opcja Utwórz tylko połączenie. Gdy kilka zapytań korzysta z tego samego źródła, zbuduj porządne zapytanie bazowe i odwołania do niego.
5. Zbuforuj mały słownik. W zapytaniu, które scala, dodaj krok Slownik = Table.Buffer(Table.SelectColumns(tProdukty, {"Kod produktu", "Kategoria"})) i scalaj z Slownik zamiast z tProdukty. Opis funkcji ostrzega, że Table.Buffer może przyspieszyć albo spowolnić zapytanie i blokuje składanie kolejnych kroków, więc dużej tabeli z bazy nie buforuj. W teście scalenie z buforowanym słownikiem dało ten sam wynik co bez bufora.
6. Przy dużych tabelach dobierz algorytm sprzężenia. Table.Join przyjmuje parametr algorytmu, a opis JoinAlgorithm.RightHash zaleca go, gdy prawa tabela jest mała, a większość wierszy lewej ma w niej parę, czyli w układzie sprzedaż plus słownik: Table.Join(Sprzedaz, "Kod", Slownik, "Kod produktu", JoinKind.LeftOuter, JoinAlgorithm.RightHash). Wynik jest od razu płaską tabelą, bez rozwijania. Unikaj JoinAlgorithm.SortMerge na nieposortowanych danych: w teście trzy z czterech wierszy dostały null zamiast kategorii, a po posortowaniu obu tabel wynik był poprawny.
7. Przy listach SharePoint scalaj zamiast rozwijać rekordy. Dokumentacja Microsoft zaleca pobrać drugą listę osobnym zapytaniem i połączyć obie przez Scal zapytania jako nowe po kluczu obcym. Druga lista jest wtedy pobierana jednym wywołaniem, a łączenie odbywa się w pamięci.

Jak sprawdzić, że zadziałało

Zmierz czas przed zmianą i po niej. W Power BI Desktop zaznacz krok scalania i na karcie Narzędzia kliknij Diagnozuj krok. Wynik diagnostyki to zapytania z każdym zdarzeniem i jego czasem rozpoczęcia i zakończenia, a przy bazie danych kolumna Data Source Query pokazuje zapytanie wysłane do serwera. Edytor w Excelu tej karty nie ma, więc tam porównaj czas odświeżenia samego zapytania na tych samych danych. Sprawdź też, czy wynik się nie zmienił: liczba wierszy i suma kontrolna po optymalizacji muszą być takie same jak wcześniej. Nawrotom zapobiegnie kolejność kroków z lekcji o składaniu: filtry i usuwanie kolumn na początku, kroki przerywające składanie na końcu.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5