Dotyczy wersji 2023 R1 i powyżej, autor: Łukasz Maciaszkiewicz
Wprowadzenie
Wydajne przeszukiwanie baz danych niejednokrotnie determinuje sprawne działanie systemu i aplikacji tworzonych w jego obrębie. Kwestia ta szczególnie uwydatnia się z czasem – rozrastające się bazy danych mogą – jeżeli nie są zarządzane w odpowiedni sposób – znacząco spowolnić działanie systemu i negatywnie wpłynąć na ogólnie rozumiany komfort użytkowania. Aby usprawnić wyszukiwanie danych oraz zwiększyć wydajność zapytań, w platformie WEBCON BPS wprowadzono indeksowanie kolumn wyboru.
Bazy danych w WEBCON BPS
W platformie WEBCON BPS wartości pól wyboru wprowadzane na formularzu zapisywane są w bazie danych do tabeli WFElements w kolumnach o nazwie rozpoczynającej się od „WFD_AttChoose(…)”. Przykładowo dla atrybutu Pole wyboru, którego konfiguracja przedstawiona jest na poniższym zrzucie, nazwa kolumny to „WFD_AttChoose1”.
Informacja o kolumnie w bazie danych, w której zapisywane są wartości dla danego atrybutu, dostępna jest w oknie konfiguracji atrybutu
We wspomnianych kolumnach dane przechowywane są w postaci złączonej „ID#Nazwa”, gdzie „ID” to identyfikator, a „Nazwa” to nazwa wyświetlana. Pierwszy człon (ID) tak zapisanych danych jest wartością unikalną, w związku z czym jest on elementem najczęściej używanym w zapytaniach SQL mających za zadanie zawężenie wyników wyświetlanych w atrybutach wyboru zasilanych z danego źródła danych. Takie zawężanie danych jest często używane, aby pomijać nieistotne w danym przypadku informacje i prezentować wyłącznie treści interesujące użytkownika na formularzu, raporcie, atrybucie wyboru itp.
- Dotychczasowe podejście – funkcja dbo.ClearWFElemID
Zważywszy, że w kolumnach bazy danych odpowiadających atrybutom wyboru dane zapisywane są w postaci „ID#Nazwa”, zawężenie danych wyświetlanych następnie w takich atrybutach wymagało dotychczas uprzedniego zastosowania funkcji dbo.ClearWFElemID. Za jej pomocą wyodrębniano człon ID, który następnie wykorzystywano do filtrowania lub łączenia różnych rekordów za pomocą klauzuli JOIN używanej w zapytaniu.
Przykładowe zapytanie zawierające funkcję dbo.ClearWFElemID wyodrębniającą wartość ID z rekordów zawartych we wskazanej kolumnie
Funkcja ta, choć użyteczna, negatywnie wpływa na wydajność systemu, ponieważ obliczenia wykonywane są na każdym wierszu tabeli bazy danych, co jest bardzo czasochłonne – zwłaszcza w przypadku dużych zbiorów danych. Ponadto zawiłość składni zapytań SQL zawierających wspomnianą funkcję może skutkować trudnością w napisaniu poprawnego i efektywnego zapytania.
Aby zaradzić wspomnianym problemom wprowadzono indeksowane kolumny wyboru, które stanowią alternatywę dla podejścia opartego na opisanej powyżej funkcji.
Indeksowane kolumny wyboru
Mechanizm indeksacji danych to proces organizacji i strukturyzowania informacji w sposób umożliwiający szybkie i efektywne wyszukiwanie. Polega on na tworzeniu indeksów, które wskazują na konkretne dane lub ich lokalizacje w zbiorze. Dzięki indeksom założonym na poszczególnych kolumnach bazy danych możliwe jest znacznie szybsze wyszukiwanie i odzyskiwanie danych z tabeli.
UWAGA: opisywane rozwiązanie polegające na wykorzystywaniu indeksowanej kolumny wyboru w platformie WEBCON BPS ma zastosowanie wyłącznie do atrybutów wyboru umożliwiających wybranie jedynie jednej wartości. W przypadku atrybutów wyboru działających w trybie wyboru wielu wartości opisywane rozwiązanie nie ma zastosowania.
W platformie WEBCON BPS na poziomie bazy danych zaimplementowano wspomniany mechanizm w odniesieniu do atrybutów wyboru, tworząc dla każdego z nich dedykowane indeksowane kolumny wyboru w bazie danych. Są to kolumny wyliczalne z założonym indeksem, w przypadku których obliczany jest identyfikator (ID). Warto podkreślić tutaj słowo „obliczany”, ponieważ identyfikator (ID) nie jest fizycznie zapisywany w takich kolumnach, ale wyliczany między jedną a drugą operacją bazodanową. Z kolei sama kolumna jest w pewnym sensie zduplikowaną wersją kolumny atrybutu wyboru, lecz w przeciwieństwie do niej zawiera wyłącznie informacje o identyfikatorze.
Nazwy kolumn wyliczanych różnią się od nazw kolumn atrybutów wyborów wyłącznie dodanym przyrostkiem „_ID”, np. „WFD_AttChoose1_ID”. Używając jej nazwy (bądź odpowiedniej zmiennej w edytorze wyrażeń w Designer Studio) kolumnę taką można następnie użyć w zapytaniu SQL do pobierania wyszczególnionych w nim danych.
Zapytanie SQL odwołujące się do indeksowanej kolumny wyboru użyte do filtrowania wyników prezentowanych na raporcie
Co istotne odwołanie się w zapytaniu do takiej indeksowanej kolumny wyboru jest wielokrotnie bardziej wydajne i szybsze – w przeciwieństwie do opisanej powyżej funkcji SQL przeszukiwana nie jest cała tabela bazy danych, a wyłącznie indeksowana kolumna. Po znalezieniu danych wskazanych w zapytaniu silnik SQL doczytuje pozostałe powiązane dane. Obie te czynności (odnajdywanie danych w indeksowanej kolumnie i doczytywanie danych) są wykonywane bardzo szybko co wymiernie wpływa na ogólną wydajność systemu i komfort użytkowania.
Warto nadmienić w tym miejscu, że dzięki wprowadzeniu indeksowanych kolumn wyboru aktualizacja systemu do wersji 2023 R1 powoduje, że część jego elementów automatycznie działa szybciej i to bez konieczności wprowadzania zmian w konfiguracji. Dotyczy to, m.in. filtrów odwołujących się do kolumn atrybutów wyboru na raportach oraz w źródłach BPS View, a także zapytań wykorzystujących zmienną „(nazwa atrybutu) Nazwa pola w bazie – ID” (zob. punkt poniżej).
- Odwołania do indeksowanej kolumny wyboru w Designer Studio
Możliwość korzystania z indeksowanej kolumny wyboru jest obecnie wbudowana w narzędzie Designer Studio wszędzie tam, gdzie dostępne są odwołania do kolumn bazodanowych, np. w raportach, BPS View, czy edytorze wyrażeń. Tworząc zapytanie SQL służące odfiltrowaniu zbędnych danych, jego autor może skorzystać ze zmiennej odwołującej się bezpośrednio do indeksowanej kolumny wyboru. Zmienna taka dostępna jest podobnie jak dotychczas w edytorze wyrażeń w zakładce Obiekty. Ma ona postać „(nazwa atrybutu) Nazwa pola w bazie – ID” i odwołuje się bezpośrednio do wspomnianej kolumny, przez co może być łatwo wstawiona do zapytania. Zmienna ta sama w sobie nie jest nowością w Designer Studio, ale dotychczas zawierała ona funkcję SQL dbo.ClearWFElemID przez co wydajność ewentualnego zapytania, w którym zostałaby użyta, była znacząco niższa.
Warto zwrócić uwagę także, że wśród zmiennych dostępnych w zakładce Obiekty znajduje się także zmienna odwołująca się do nazwy (nazwy wyświetlanej) w kolumnie bazy danych. Jednak w tym przypadku nie zawiera ona odwołania do kolumny indeksowanej, lecz nadal opiera się na odpowiedniej funkcji SQL. Wynika to z faktu, że nazwy jako takie nie zawsze są unikalne i mogą się zmieniać, dlatego z rzadka wykorzystuje się je przy łączeniu rekordów, ich sortowaniu lub grupowaniu.
Zmienną odwołującą się do indeksowanej kolumny wyboru można odnaleźć obecnie w edytorze wyrażeń w zakładce Obiekty
Podsumowanie
Wprowadzenie indeksowanych kolumn wyboru w platformie WEBCON BPS wymiernie wpływa na wydajność nie tylko zapytań SQL, ale także całego systemu. Rozwiązanie to pozwala w optymalny sposób budować precyzyjne zapytania i uzyskiwać oczekiwane dane w bardzo krótkim czasie. Z kolei wbudowanie odwołań do kolumny indeksowanej bezpośrednio w narzędziu Designer Studio znacząco ułatwi tworzenie wspomnianych zapytań SQL i przeszukiwanie nawet bardzo dużych baz danych.
