Spis treści:

Kategoria:Optymalizacja SQLSQL Server


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.

--Tabela ntext
CREATE TABLE NTxt
(
txt ntext
)
--Tabela nvarchar(max)
CREATE TABLE NVar
(
txt NVARCHAR(MAX)
)

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

SET NOCOUNT ON
DECLARE @i int=1

--operacja na tabeli ntext
DECLARE @t DATETIMEGETDATE()
WHILE @i<=50000
BEGIN
INSERT INTO NTxt values('WWW')
SET @i=@i+1
END
SELECT DATEDIFF(millisecond,@t,GETDATE())

SET @i=1

--operacja na tabeli nvarchar(max)
SELECT @t = GETDATE()
WHILE @i<=50000
BEGIN
INSERT INTO NVar values('WWW')
SET @i=@i+1
END
SELECT DATEDIFF(millisecond,@t,GETDATE())

Rezultaty są następujące:

ntext202201855620163217402139321610
nvarchar(MAX)196561934319436207202139318156

ŚredniaMediana
ntext20613,720806,5
nvarchar(MAX)20194,020136,0

Pomiar 2:

ntext1801318166185231920018910
nvarchar(MAX)1816317926184931794618156

ŚredniaMediana
ntext18562,418523
nvarchar(MAX)18136,818156

Pomiar 3, tym razem 150000 rekordów:

ntext5576056436586635580055840
nvarchar(MAX)5463356063553365383355600

ŚredniaMediana
ntext56499,855840
nvarchar(MAX)5509355336

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

ntext68606220640062506170
nvarchar(MAX)49704620454045604600

ŚredniaMediana
ntext63806250
nvarchar(MAX)46584600

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.

EXEC sp_spaceused 'NTxt'
EXEC sp_spaceused 'NVar'

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

namerowsreserveddataindex_sizeunused
NTxt15000018000 KB17920 KB8 KB72 KB

Poniżej wyniki tabeli NVar:

namerowsreserveddataindex_sizeunused
NVar1500003080 KB3032 KB8 KB40 KB

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.

SELECT 'NTxt' [Table], in_row_data_page_count,
lob_used_page_count,used_page_count, row_count 
FROM sys.dm_db_partition_stats WHERE [object_id]=OBJECT_ID('NTxt')

SELECT 'NVar' [Table], in_row_data_page_count,
lob_used_page_count, used_page_count, row_count
FROM sys.dm_db_partition_stats WHERE [object_id]=OBJECT_ID('NVar')

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

Tablein_row_data_page_countlob_used_page_countused_page_countrow_count
NTxt57216682241150000

Tablein_row_data_page_countlob_used_page_countused_page_countrow_count
NVar3790380150000

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.

Warto w tym momencie, albo później, przeczytać artykuł na temat poprawnego mierzenia wydajności. Wspomniany artykuł jest tutaj: Jak poprawnie mierzyć wydajność zapytań SQL

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.

NTxt16501800168616101526 16601843174316531606
NVar803820773926746 783780750730760

ŚredniaMediana
ntext1677,71656,5
nvarchar(MAX)787,1776,5

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.

Porównajmy jeszcze plan wykonania.

Porównanie instrukcji SELECT dla ntext i nvarchar(MAX)
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

, 2013-12-20

Brak komentarzy - bądź pierwszy

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!