Jak mierzyć czas zapytań Power Query za pomocą VBA

W tym artykule pokażę, jak efektywnie mierzyć czas wykonania zapytań Power Query w Excelu przy użyciu prostego kodu VBA. Dokładne monitorowanie czasu zapytań pozwala na optymalizację wydajności arkuszy i zapewnia lepszą kontrolę nad procesami przetwarzania danych.

Odpowiednie techniki pomiaru czasu są kluczowe dla deweloperów tworzących zaawansowane rozwiązania w Excelu.

W moim wideo demonstruję dwie metody pomiaru czasu wykonania zapytania Power Query:

Pomiar czasu wykonania zapytania Power Query za pomocą VBA

Monitorowanie czasu wykonywania zapytań Power Query jest niezbędnym elementem optymalizacji wydajności arkuszy Excel, szczególnie przy pracy z dużymi zbiorami danych. W tym artykule przedstawiam praktyczne metody pomiaru czasu wykonania zapytań przy użyciu prostego kodu VBA, który pozwala na precyzyjne określenie, jak długo trwa przetwarzanie danych.

Podstawowa idea polega na zapisaniu czasu początkowego przed uruchomieniem zapytania, a następnie obliczeniu różnicy po jego zakończeniu. Takie podejście daje dokładny wynik i pozwala na świadome zarządzanie wydajnością naszych rozwiązań w Excelu.

Pierwsza metoda: Odświeżanie zapytania na podstawie zaznaczonej komórki

Pierwsza metoda, którą prezentuję, wykorzystuje odświeżanie zapytania na podstawie zaznaczonej komórki. Oto kod VBA, który realizuje to zadanie:

Sub MierzCzasZapytania()
    Dim t0 As Double
    t0 = Timer
    
    ActiveCell.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    MsgBox "Czas wykonania: " & Timer - t0 & " sekund"
End Sub

W powyższym kodzie definiujemy zmienną t0, której przypisujemy aktualny czas systemowy przy użyciu funkcji Timer. Następnie odświeżamy zapytanie powiązane z tabelą w aktywnej komórce. Kluczowym elementem jest ustawienie parametru BackgroundQuery na False, co wyłącza odświeżanie w tle i pozwala na dokładny pomiar czasu.

Po zakończeniu odświeżania, kod wyświetla okno dialogowe z informacją o czasie wykonania zapytania. Aby uzyskać średni czas wykonania, możemy uruchomić makro kilkukrotnie (np. używając klawisza F5) i obliczyć średnią z otrzymanych wyników.

Druga metoda: Odświeżanie zapytania przez jego nazwę

Druga metoda polega na odświeżaniu zapytania poprzez bezpośrednie odwołanie do jego nazwy. Jest to szczególnie przydatne, gdy chcemy zmierzyć czas zapytania, które nie jest bezpośrednio związane z tabelą w aktywnej komórce. Kod VBA dla tej metody wygląda następująco:

Sub MierzCzasZapytaniaNazwa()
    Dim t0 As Double
    t0 = Timer
    
    ThisWorkbook.Queries("Query1").Refresh
    
    MsgBox "Czas wykonania: " & Timer - t0 & " sekund"
End Sub

W tym przypadku odświeżamy konkretne zapytanie o nazwie "Query1". Należy jednak pamiętać o ważnej kwestii — domyślnie zapytania Power Query są odświeżane w tle, co uniemożliwia dokładny pomiar czasu wykonania.

Problem odświeżania w tle

Podczas testów wykazałem, że metoda odświeżania przez nazwę zapytania nie daje dokładnych wyników, jeśli zapytanie ma włączoną opcję odświeżania w tle. Jest to szczególnie problematyczne dla zapytań, które są połączeniami (connections) i nie są ładowane bezpośrednio do arkusza Excel.

Aby rozwiązać ten problem, musimy wyłączyć odświeżanie w tle dla danego zapytania. Możemy to zrobić na dwa sposoby:

1. Ręcznie poprzez interfejs Power Query — klikamy prawym przyciskiem myszy na zapytanie, wybieramy "Właściwości" i odznaczamy opcję "Włącz odświeżanie w tle".
2. Programowo w kodzie VBA — w pierwszej metodzie używamy parametru BackgroundQuery:=False, ale niestety w przypadku odświeżania przez nazwę zapytania taka opcja nie jest dostępna.

Nazewnictwo zapytań w VBA

Warto zauważyć, że nazwy zapytań Power Query widoczne w edytorze i nazwy używane w VBA mogą się różnić. W kodzie VBA zapytania często mają dodatkowy przedrostek przed nazwą widoczną w interfejsie Power Query. Jest to istotna informacja, o której należy pamiętać podczas tworzenia kodu do pomiaru czasu zapytań.

Optymalizacja wydajności zapytań Power Query

Posiadając narzędzie do dokładnego pomiaru czasu wykonania zapytań Power Query, możemy przystąpić do optymalizacji ich wydajności. Oto kilka wskazówek, które mogą pomóc w przyspieszeniu zapytań:

• Ograniczanie danych źródłowych — filtruj dane jak najwcześniej w procesie przetwarzania
• Usuwanie niepotrzebnych kolumn — mniej danych to szybsze przetwarzanie
• Minimalizacja transformacji — każda operacja wymaga czasu, staraj się łączyć podobne operacje
• Używanie funkcji buforowania — funkcja Table.Buffer może przyspieszyć niektóre operacje
• Monitorowanie używania pamięci — niektóre zapytania mogą wymagać dużych ilości pamięci, co wpływa na wydajność

Praktyczne zastosowania pomiaru czasu zapytań

Pomiar czasu wykonania zapytań Power Query ma wiele praktycznych zastosowań w codziennej pracy z Excelem:

Pozwala na identyfikację "wąskich gardeł" w naszych rozwiązaniach, czyli zapytań, które zajmują najwięcej czasu i wymagają optymalizacji. Umożliwia porównanie różnych podejść do tego samego problemu i wybór najwydajniejszego rozwiązania. Jest niezbędny podczas tworzenia rozwiązań dla użytkowników końcowych, aby zapewnić im komfortową pracę bez długiego oczekiwania na przetworzenie danych.

Regularne monitorowanie czasu wykonania zapytań pomaga również w identyfikacji potencjalnych problemów, które mogą pojawić się wraz ze wzrostem ilości przetwarzanych danych. Dzięki temu możemy odpowiednio wcześnie zareagować i zoptymalizować nasze rozwiązania.