Porównanie ntext i nvarchar(max)

Informacje na temat ntext i nvarchar(MAX)

W dokumentacji MSDN obok typów ntext, text oraz image pojawiła się następująca notka:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Przekładając na nasze dowiadujemy się, że typy ntext, text i image zostaną w którejś z kolejnych wersji SQL Server usunięte. Należy zatem unikać tych typów i zaplanować zmiany w aplikacjach, które z tych typów korzystają. Zamiast wspomnianych należy używać nvarchar(max), varchar(max), and varbinary(max).

Dowiadujemy się tam również, że ntext to łańcuchy znaków zmiennej długości i maksymalnym rozmiarze 2^30 - 1 (tj. 1 073 741 823) znaków. Zajmowany obszar w bazie jest oczywiście dwa razy większy niż ilość znaków.

Korzystając z typu nvarchar(max) możemy wstawić łańcuchy znaków takiej samej długości, jakie mogliśmy umieszczać w typie ntext. Dokumentacja podpowiada nam także, że dane tekstowe korzystają z formatu UNICODE UCS-2.

Obie definicje wydają się bardzo podobne, wręcz identyczne. Co zatem sprawiło, że Microsoft odradza stosowanie pierwszego typu, a zaleca stosowanie nvarchar(max)? Przekonamy się o tym czytając dalszą część artykułu.

Wydajność operacji INSERT

Sróbujmy stworzyć dwie tabele, a następnie wrzućmy do tych tabel, powiedzmy, 50000 rekordów.

Teraz wykonajmy naszą procedurę do badania wydajności operacji INSERT:

Rezultaty są następujące:

Pomiar 2:

Pomiar 3, tym razem 150000 rekordów:

Wzrostu wydajności rzędu 1-2% nie należy traktować jako coś nadzwyczajnego, wszystko mieści się w granicach błędu. To nie tutaj należy szukać poprawy wydajności, choć różnica jest już zauważalna i powtarzalna. Z czego to wynika? Zapewne z tego, że SQL Server musi modyfikować większą ilość stron pamięci masowej. Więcej szczegółów dalej. Jako ciekawostkę podam fakt, że te same operacje wykonywane w jednej transakcji są w sposób oczywisty szybsze. Co więcej, wtedy dopiero wychodzi przewaga nvarchar(MAX) nad ntext. Przy jednej transakcji większy wpływ na wydajność zapisu ma rzeczywisty zapis danych, a mniejszy modyfikacja indeksu stosowego, nieklastrowanego i logowanie poszczególnych operacji. Wyniki są następujące (wstawianie 150000 rekordów):

Jak widać, tym razem różnica jest znaczna. Może nie jest to przekonujące zwycięstwo, ale możemy spokojnie napisać 1:0 dla nvarchar(max).

Rozmiary tabel ntext i nvarchar(MAX)

Pierwszy test być może nie wszystkich przekonał i nie wykazał jakiejś nadzwyczajnej poprawy, choć przy dużej ilości danych ta poprawa jest zauważalna. Spróbujmy zatem przyjrzeć się rozmiarom obu tabel. Na początek skorzystamy z porcedury sp_spaceused. Przypomnijmy, że w każdej z tabel zapisanych jest 150000 rekordów.

Wyniki z tabeli NTxt przedstawiają się następująco:

Poniżej wyniki tabeli NVar:

Co tutaj widać? Po pierwsze to, że dla tabeli z typem ntext zarezerwowane jest 18000 KB (używane 17920 KB), podczas gdy dla tabeli NVar tylko 3080 KB. Poprawa jest tutaj dość znaczna. W tym przypadku ponad pięciokrotna. Czy to nie jest wystarczający powód, aby zmienić w istniejących bazach kolumny typu ntext na nvarchar(max)? Być może jest, ale nie jest to koniec testów porównawczych obu typów. Przyjrzyjmy się jeszcze dokładnej strukturze obu tabel. Tym razym skorzystamy z widoku systemowego sys.dm_db_partition_stats.

Popatrzmy teraz na wyniki zwrócone przez te dwa zapytania:

Tutaj znacznie lepiej widać co się stało i jaki jest powód różnic wydajnościowych. Dane w kolumnie typu ntext przechowywane są w obszarze pamięci przeznaczonym na duże obiekty (LOB_DATA). Dane typu nvarchar(max) przechowywane są w obszarze dużych obiektów tylko wtedy, gdy nie mieszczą się na jednej stronie pamięci. Nie ma niepotrzebnych wskaźników, nie ma struktur pośrednich. Jak się później przekonamy - nie zawsze. Nie uprzedzajmy jednak faktów.

Wydajność operacji SELECT

Przed każdą operacją SELECT należy wyczyścić wewnętrzny bufor SQL Server. SQL Server może zapamiętywać sobie wyniki poprzednich operacji. Sprawia to, że zamiast wykonywać fizyczny dostęp do danych na dysku, wykonuje fizyczny dostęp do danych bufora. Jak nietrudno się domyślić, dostęp do bufora jest znacznie szybszy niż do danych z dysku, a nasze pomiary okażą się niemiarodajne i przekłamane. Do czyszczenia pamięci cache serwera służy instrukcja DBCC DROPCLEANBUFFERS.

Czas na pomiar numer 1. Procedura pomiarowa jest prosta i polega na wykonaniu operacji SELECT * FROM Tabela. Popatrzmy na wyniki tych operacji. Dla pewności zostały one powtórzone 10 razy.

Co się stało, że wyniki są takie różne? Po pierwsze, jak już mogliśmy się przekonać, dane typu nvarchar(max) zapisane są na mniejszej liczbe stron. Konsekwencja tego jest taka, że SQL Server odczytuje z dysku mniejszą ilość danych. Mniej operacji odczytu to szybsze działanie, bo głównym kosztem są operacje dyskowe, o kilka rzędów wielkości wolniejsze niż operacje na pamięci operacyjnej. Druga rzecz - podczas operacji odczytu nie są wykonywane dodatkowe skoki do obszaru dużych obiektów - to też ma swój wpływ na wydajność kwerend.

Rys. 1. Porównanie instrukcji SELECT dla ntext i nvarchar(MAX) na planie wykonania.

Jak widać na załączonym rysunku, także plany wykonania zakładają, że operacja odczytu tej samej ilości rekordów będzie trwała dłużej dla kolumn typu ntext. Różnica w testach jest znacznie większa, ale pamiętajmy, że w naszym przypadku wszystkie rekordy mieszczą się na stronie i nie ma żadnych odwołań do obszaru LOB_DATA. Przypomnijmy, że nvarchar(max) też wędruje do obszaru dużych obiektów, ale tylko wtedy, gdy rozmiar przekracza 8KB. Doświadczenie pokazuje, że większość danych w takich kolumnach ma rozmiar mniejszy niż 8KB. Jeżeli nawet niewielka część danych, powiedzmy 30%, mieści się na stronie razem z pozostałymi danymi, to osiągniemy wymierne korzyści - poprawiając wydajnośći nieznacznie zmniejszając obszar zajmowany przez te dane.

Wnioski

Dość niespodziewanie wyszedł z tego wpisu dość spory artykuł. Nie oznacza to, że temat został wyczerpany. Zachęcam do dzielenia się własnymi spostrzeżeniami w komentarzach i używania nvarchar(MAX). Skrypt, który zamienia wszystkie kolumny typu ntext na nvarchar(MAX) można znaleźć w tym wpisie: Zamiana wszystkich kolumn typu ntext na nvarchar(MAX).

Kategoria:Optymalizacja SQLSQL Server