Sortowanie i kolejność wartości NULL
Wiemy dobrze, że SQL Server działa w ramach logiki trójwartościowej. Mamy PRAWDĘ, FAŁSZ i NULL. Przyjęło się, że PRAWDA to 1, FAŁSZ to 0 (stąd między innymi zapis binarny). Co natomiast zrobić z NULL? Jest to wbrew pozorom zagadnienie, które mocno wpływa na całą architekturę serwera i sposób przetwarzania żądań, czyli instrukcji SQL. Konsekwencje logiki trójwartościowej wchodzą między innymi w obszar sortowania. Już kilkuletnie dziecko wie, że jedno jabłko to więcej niż zero jabłek. Zapytane, choćby w środku nocy, z łatwością odgadnie, że 1 to więcej niż 0. Teraz najważniejsze: gdzie w tym miejscu jest NULL? Ile to jest NULL? Poruszając się w zbiorze liczb całkowitych zapytajmy czy:
- NULL to zero?
- NULL to mniej niż zero?
- NULL to więcej niż zero?
- NULL to nieskończoność?
- NULL to minus nieskończoność?
Odpowiedź nie jest taka oczywista. Nie jest oczywista do tego stopnia, że twórcy głównych silników baz danych inaczej sobie na te pytania odpowiadają. Inaczej zachowa się baza danych Oracle, a inaczej SQL Server.
Czy ORDER BY umieści NULL na początku, czy na końcu?
Jak już wspomniałem, w sensie matematycznym nie ma jednej słusznej odpowiedzi. Przyjrzyjmy się zatem jak to działa w SQL Server:
(
value int
)
INSERT INTO NULLSort
VALUES (-7),(1),(5),(NULL)
SELECT * FROM NULLSort
ORDER BY value
Przyjrzyjmy się teraz wynikowi zapytania:
| value |
|---|
| NULL |
| -7 |
| 1 |
| 5 |
Jak widać, pole NULL znalazło się przed innymi wartościami. W bazie danych Oracle jest zupełnie inaczej. Tam wartości NULL domyślnie wędrują na koniec.
Nie pierwszy i nie ostatni raz należałoby zapytać: jak sprawić, aby SQL Server dopasował się do Oracle? Sprawa nie jest tak prosta i oczywista jak w przypadku bazy danych Oracle. Przyjrzyjmy się możliwemu rozwiązaniu tego problemu:
SELECT * FROM NULLSort
ORDER BY ISNULL(value, 0x7FFFFFFF)
--Działa na SQL Server i Oracle,
--a nawet w przypadku języka HQL,
--bardziej uniwersalne
SELECT * FROM NULLSort
ORDER BY COALESCE(value, 0x7FFFFFFF)
Tym razem wartość NULL wędruje na koniec listy:
| value |
|---|
| -7 |
| 1 |
| 5 |
| NULL |
Trik jest dość prosty. Zamiast wartości NULL podajemy największą możliwą wartość, jaką może przyjąć dany typ. Rozwiązanie ma jednak pewne wady. Zastanówmy się chwilę i spróbujmy sobie wyobrazić co się stanie, jeżeli wśród danych różnych od NULL są też takie, które przyjmują maksymalną wartość w ramach danego typu. Istnieje ryzyko, że wartości NULL będą przemieszane z wartościami maksymalnymi.
Pytanie numer dwa: jak poradzić sobie z innymi typami? Jaka jest maksymalna wartość dla varchar(3)? Pierwsze rozwiązanie jest bardzo proste i wyjątkowo przejrzyste, ale, jak widać, ma pewne ograniczenia.
Sortowanie pustych wartości tekstowych
Zaprezentowana wcześniej metoda nie nadaje się do sortowania wartości tekstowych. Dla takich wartości można zastosować nieco inną metodę. Przyjrzyjmy się poniższemu listingowi:
(
value varchar(6)
)
INSERT INTO NullText
VALUES ('AAA'), ('BBB'),
(NULL), ('CCC')
SELECT * FROM NullText
ORDER BY
CASE
WHEN value IS NULL THEN 1
ELSE 0
END, value
Zaprezentowane rozwiązanie nie jest tak proste jak poprzednie, ale dzięki niemu znikają wszystkie wymienione wcześniej wady. Jeżeli w typie brakuje nam wartości, która mogłaby reprezentować NULL, należy posłużyć się dodatkową zmienną (dodatkową kolumną). Sama instrukcja trochę się rozrasta, ale niestety nie ma innego wyjścia.
Niektórzy mogą odnieść wrażenie, że wygenerowanie dodatkowej kolumny może mieć wpływ na wydajność całeo zapytania. Nie można się z tym nie zgodzić. Zaskakujące jest to, że większość osób upatruje spadku wydajności w zupełnie innym miejscu, niż w tym, które stanowi rzeczywiste źródło problemów.
NULL, sortowanie i korzystanie z indeksu
Zanim przejdę do wyjaśnień, przeanalizujmy nieco inny przykład. Tabela będzie taka sama jak w pierwszym przykładzie, ale na polu value zostanie założony indeks. Co się zmieni? Jakie będą różnice wydajnościowe? Popatrzmy na poniższy listing, który za chwilę będziemy analizować:
(
value int
)
--Generujemy 100000 wartości, aby przykład
--był bliższy rzeczywistości
DECLARE @i int=1
SET NOCOUNT ON
BEGIN TRANSACTION
WHILE @i<100000
BEGIN
INSERT INTO NULLSort VALUES (@i)
SET @i += 1
END
INSERT INTO NULLSort VALUES (NULL)
COMMIT
--Tworzymy indeks na tabeli
CREATE INDEX IX_Value ON NULLSort(value)
SELECT * FROM NULLSort
ORDER BY COALESCE(value, 0x7FFFFFFF)
SELECT * FROM NULLSort
ORDER BY value
Skrypt nie jest szczególnie skomplikowany. Tworzymy sobie tabelę, uzupełniamy ją wartościami od 1 do 99999, dorzucamy NULL. Na polu value zakładamy indeks. Po założeniu zwykłego indeksu próbujemy wykonać dwa zapytania - jedno korzystające z prostej techniki przenoszenia NULL na koniec listy wyników, drugie natomiast zwracające wyniki w standardowym porządku, czyli z NULL na początku. Przyjrzyjmy się teraz planom wykonania poszczególnych instrukcji.
Rys. 1. Plan wykonania instrukcji ORDER BY z przestawionym porządkiem wartości NULL.
Popatrzmy teraz na drugą instrukcję i jej plan wykonania. Różnice są aż nadto widoczne:
Rys. 1. Plan wykonania instrukcji ORDER BY ze standardowym porządkiem wartości NULL.
Druga instrukcja jest znacznie szybsza, ale nie ze względu na dodatkowe wyliczenia czy też brak dodatkowej kolumny (w przypadku techniki z typem varchar). Ta dodatkowa operacja lub kolumna to operacje w pamięci, a te, w porównaniu do operacji dyskowych, są bardzo szybkie. Chodzi tutaj głównie o to, że silnik SQL Server nie może skorzystać z indeksu, w którym te wartości są już odpowiednio posortowane. Nasza technika przestawiania wartości NULL wymaga przeprowadzenia operacji sortowania w pamięci (sortowanie jest dużo bardziej kosztowne niż zwykła podmiana NULL na 0x7FFFFFFF). To tutaj, właśnie w dodatkowej operacji sortowania, należy szukać powodu spadku wydajności. Czy da się coś z tym zrobić?
Minimalizacja spadku wydajności
Pojawił nam się problem, którego nigdy byśmy nie napotkali na swojej drodze, gdyby nie nasz wymysł przenoszenia wartości NULL na koniec listy wyników. Nie ma łatwego rozwiązania. Jeżeli mamy taką możliwość, można wykonać dwa zapytania - pierwsze zwracające tylko wyniki z wartościami różnymi od NULL, a w przypadku braku odpowiedniej liczby wyników wykonać zapytanie na wartościach NULL. Otrzymamy dwa zbiory wyników, sortowane po indeksie, ale w wielu przypadkach (np. prezentacja na stronie Web, przetwarzanie danych w aplikacji) nie wiąże się to z żadnymi konsekwencjami. Dane będą posortowane, bo druga lista wyników przyjdzie później. Generalnie rzecz ujmując - należy unikać takiego niestandardowego rozwiązania. Jeżeli mimo wszystko zależy nam na tym, można pomyśleć o dodatkowej kolumnie, tradycyjnie, za pomocą typu logicznego reprezentującej NULL. Taką kolumnę można umieścić w indeksie (indeks na dwóch kolumnach). Jeżeli liczba zwracanych rekordów będzie wyrażana w dziesiątkach, setkach, czy nawet tysiącach - nie warto nic robić. Żadna ze znanych mi technik nie jest tego warta.
Zachęcam do dzielenia się metodami rozwiązywania tego typu problemów w komentarzach - być może są jakieś, o których nie wiem.
Kategoria:SQL Server
Komentarze: