Kolumny sparsowane

Kolumny sparsowane to zbiorcze określenie pól zoptymalizowanych pod przechowywanie licznych wartości pustych.

Kolumny sparsowane w relacyjnej tabeli

Kolumny sparsowane to specjalny mechanizm programu Microsoft SQL Server przeznaczony dla tabel, w których wiele kolumn często przyjmuje wartość NULL. Zwykła kolumna może zajmować miejsce w rekordzie zgodnie z typem danych, natomiast kolumna oznaczona jako SPARSE jest przechowywana w sposób dostosowany do dużej liczby pustych wartości. Wartość NULL w kolumnie sparsowanej nie zajmuje miejsca tak jak analogiczna wartość w kolumnie standardowej, choć sam mechanizm ma narzut dla wartości innych niż NULL.

Rozwiązanie jest przydatne między innymi wtedy, gdy jedna tabela musi przechowywać wiele opcjonalnych atrybutów, ale każdy pojedynczy rekord wykorzystuje tylko niewielką ich część. Przykładem może być tabela opisująca różne typy urządzeń, gdzie część rekordów ma parametry charakterystyczne dla laptopa, inne dla drukarki, a jeszcze inne dla monitora. Zamiast tworzyć osobne tabele lub przechowywać wszystkie możliwe właściwości w jednej szerokiej strukturze, można rozważyć kolumny sparsowane dla pól rzadko wypełnianych.

Kiedy oszczędność miejsca ma znaczenie

Kolumna sparsowana nie jest automatycznie lepsza od zwykłej kolumny. W SQL Server jej użycie ma sens przede wszystkim wtedy, gdy udział wartości NULL jest odpowiednio wysoki. Dla wartości przechowywanych jako dane rzeczywiste kolumna sparsowana może wymagać większej ilości miejsca niż kolumna standardowa, ponieważ SQL Server musi zapisać dodatkowe informacje opisujące obecność i położenie wartości. Opłacalność zależy więc od typu danych, liczby rekordów oraz rzeczywistego rozkładu wartości.

Przy projektowaniu tabeli należy zmierzyć, jak często poszczególne pola są puste, zamiast oceniać to wyłącznie na podstawie nazwy kolumny. Warto także uwzględnić sposób odczytu danych. Jeżeli zapytania regularnie pobierają wiele kolumn sparsowanych jednocześnie, oszczędność wynikająca z pustych pól może być mniejsza, a odczyt bardziej złożony. Analiza powinna obejmować rozmiar tabeli, indeksy, częstotliwość aktualizacji oraz typowe zapytania wykonywane przez aplikację.

Definiowanie, odczyt i ograniczenia mechanizmu

W Microsoft SQL Server kolumnę sparsowaną definiuje się przy deklaracji kolumny za pomocą słowa kluczowego SPARSE, na przykład OpisDodatkowy nvarchar(200) SPARSE. Nadal obowiązuje wybrany typ danych, długość oraz zasady integralności właściwe dla tabeli. Kolumna może być używana w zapytaniach SELECT, INSERT i UPDATE podobnie jak zwykła kolumna, dlatego aplikacja nie musi stosować osobnego formatu odczytu tylko dlatego, że pole jest sparsowane.

Mechanizm ma jednak ograniczenia, które trzeba sprawdzić przed wdrożeniem. Nie każdy typ danych może zostać oznaczony jako SPARSE, a obsługa kluczy, ograniczeń, indeksów i kolumn obliczanych zależy od konkretnej konstrukcji tabeli oraz wersji SQL Server. W tabeli można również skonfigurować zestaw kolumn sparsowanych z użyciem kolumny typu XML COLUMN_SET. Taki zestaw udostępnia wartości kolumn sparsowanych w postaci niejawnego dokumentu XML, ale nie zastępuje zwykłego modelowania danych i może wpływać na sposób zapisu oraz odczytu.

Przed zmianą istniejącej tabeli trzeba zweryfikować zgodność z aplikacją, procedurami składowanymi, widokami, importami i narzędziami administracyjnymi. Sama zmiana właściwości kolumny może wymagać przebudowy struktury tabeli lub migracji danych. Należy też ocenić wpływ na kopie zapasowe, indeksy oraz czas wykonywania zapytań, ponieważ oszczędność miejsca na danych nie oznacza automatycznie krótszego czasu każdej operacji.

Ocena kolumn sparsowanych podczas diagnostyki bazy

Podczas diagnostyki problemu z tabelą wykorzystującą kolumny sparsowane sprawdza się definicję obiektu w katalogu systemowym SQL Server, typy danych, liczbę wartości NULL oraz rozkład wartości rzeczywiście zapisanych. Istotne jest porównanie struktury bazy z modelem używanym przez aplikację. Błąd może wynikać nie z samego mechanizmu SPARSE, lecz z niezgodnej migracji, nieprawidłowej serializacji wartości, pominięcia kolumny w instrukcji INSERT albo założeń programu, że każde pole ma zwykły sposób przechowywania.

Warto analizować plan wykonania zapytań oraz rozmiar indeksów, szczególnie gdy po zmianie standardowych kolumn na sparsowane pojawiło się spowolnienie. Należy również sprawdzić, czy zapytania filtrujące po rzadko wypełnianych polach mają odpowiednie indeksy i czy ich selektywność uzasadnia takie indeksowanie. Wyniki testów powinny pochodzić z danych o podobnej strukturze do środowiska docelowego, ponieważ sztuczny zbiór z małą liczbą rekordów może nie ujawnić kosztów zapisu, odczytu i przebudowy indeksów.

W przypadku problemów z aplikacją pomocne jest rozdzielenie trzech kwestii: poprawności definicji tabeli, poprawności danych oraz sposobu obsługi wyniku przez kod. Jeżeli w bazie znajduje się NULL, program powinien odróżniać go od pustego tekstu, wartości zero lub wartości domyślnej. Takie rozróżnienie jest ważne niezależnie od tego, czy kolumna jest sparsowana, ale przy szerokich tabelach z wieloma opcjonalnymi polami błędna interpretacja pustej wartości może być trudniejsza do wykrycia.