Spis treści:

Kategoria:SQL Server


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:

CREATE TABLE NULLSort
(
value int
)

INSERT INTO NULLSort
VALUES (-7),(1),(5),(NULL)

SELECTFROM 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.

Umieszczanie wartości NULL na końcu listy wyników w Oracle jest ustawieniem domyślnym. Oracle pozwala sterować tym zachowaniem za pomocą słów kluczowych NULLS FIRST i NULLS LAST umieszczanych po instrukcji ORDER BY. Domyślną wartością jest właśnie NULLS LAST. Aby wynik instrukcji Oracle pokrywał się z wynikiem SQL Server, należy jawnie skorzystać z opcji NULLS FIRST.

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:

--Działa tylko na SQL Server
SELECTFROM NULLSort
ORDER BY ISNULL(value, 0x7FFFFFFF)

--Działa na SQL Server i Oracle,
--a nawet w przypadku języka HQL,
--bardziej uniwersalne
SELECTFROM 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:

CREATE TABLE NullText
(
value varchar(6)
)

INSERT INTO NullText
VALUES ('AAA'), ('BBB'),
(NULL), ('CCC')

SELECTFROM 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.

Rozwiązanie korzysta z instrukcji IF ... THEN ... ELSE w wersji SQL Server. Więcej na ten temat można znaleźć tutaj: Instrukcja IF THEN ELSE w sekcji SELECT SQL Server.

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ć:

CREATE TABLE NULLSort
(
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)

SELECTFROM NULLSort
ORDER BY COALESCE(value, 0x7FFFFFFF)

SELECTFROM 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.

Plan wykonania instrukcji ORDER BY z przestawionym porządkiem wartości NULL
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:

Plan wykonania instrukcji ORDER BY ze standardowym porządkiem wartości NULL
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

, 2013-12-20

Komentarze:

Pip2016 (2016-07-29 14:33:23)
Jak zwykle na czasie ;)
Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!