Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] Indeksy XML w SQL Server 2008 od środka

Jakiś czas temu pisałem o moich "zabawach" z typem danych XML w SQL Server. Od tamtego czasu minęło kilka miesięcy, w czasie których moja styczność z typem danych XML była dość znikoma. Ale ostatnio XML wrócił w kręgi moich zainteresowań, bo - po pierwsze – zajmowałem się rozgryzaniem grafów zakleszczeń (deadlock graphs), a po drugie przeprowadziłem w firmie serię szkoleń z używania typu danych XML w SQL Server. Przy okazji wziąłem na warsztat indeksy XML oraz tematy związane z wydajnością zapytań XQuery. Część moich obserwacji spróbuję zatem przelać na elektroniczny papier :-)

A więc masz sporo XML-a?

I chcesz, żeby zapytania odwołujące się do poszczególnych węzłów dokumentów XML-owych działały szybko i sprawnie? Zatem indeksy XML są dla Ciebie :-) Niezależnie od scenariusza: a) mało dużych dokumentów, b) dużo małych dokumentów, c) dużo dużych dokumentów – założenie indeksów XML może okazać się strzałem w dziesiątkę.

Zacznijmy od tego, że wygenerujemy sporo dokumentów XML (na potrzeby testów założyłem sobie bazę o nazwie XMLTest – rozmiar początkowy bazy ustawiłem na kilkadziesiąt megabajtów, żeby uniknąć niepotrzebnego automatycznego rozrostu).

USE XMLTest;
GO
IF OBJECT_ID('dbo.XMLTable', 'U') IS NOT NULL
DROP TABLE dbo.XMLTable;
GO
CREATE TABLE dbo.XMLTable (
XMLTableID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
XMLColumn xml NOT NULL
);
GO
INSERT INTO dbo.XMLTable (XMLColumn)
SELECT
(
SELECT
o2.[object_id] AS '@id',
o2.name AS 'name',
o2.type_desc AS 'type'
FROM sys.all_objects AS o2
WHERE o2.[object_id] = o1.[object_id]
FOR XML PATH('object'), TYPE
)
FROM sys.all_objects AS o1;
GO
SELECT * FROM dbo.XMLTable;
GO

Po wykonaniu powyższego kodu w bazie XMLTest mamy tabelę dbo.XMLTable zawierającą jakieś 1980 wierszy (lub więcej). W przypadku bazy, na której pracuję, wierszy w tabeli było jakieś 130 tysięcy :-)

Zawartość tabeli przedstawia się mniej więcej tak:

image

Czyli w dokumentach XML są trzymane metadane obiektów bazodanowych (tabel, procedur etc.) na zasadzie – jeden wiersz w tabeli – jeden obiekt z bazy danych.

Główny indeks XML

Zabawę zaczynamy od utworzenia indeksu głównego XML (PRIMARY XML INDEX).

CREATE PRIMARY XML INDEX IX_XML_Primary 
ON dbo.XMLTable (XMLColumn);
GO

Kod prawie jak przy tworzeniu zwykłego indeksu. Wykonanie go może chwilę potrwać. W tle SQL Server zakłada wewnętrzną tabelę. Zdobycie jej nazwy to chwila:

SELECT TOP 1 * FROM sys.internal_tables ORDER BY create_date DESC;

I mamy coś a la xml_index_nodes_2105058535_256000 (oczywiście, nazwa tabeli wewnętrznej jest dość przypadkowa). Tabele te znajdują się w schemacie sys (schema_id = 4 w widoku sys.internal_tables). Próba ognia:

SELECT * FROM sys.xml_index_nodes_2105058535_256000;

kończy się zwróceniem przez SQL Server komunikatu

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.xml_index_nodes_2105058535_256000'.

Ale sama tabela istnieje, bo udaje się wykonać:

EXEC sp_help 'sys.xml_index_nodes_2105058535_256000';
GO
EXEC sp_helpindex 'sys.xml_index_nodes_2105058535_256000';
GO

Wyniki:

image

A zatem powstaje tabela o zadanej z góry strukturze (prawie niezależnej od struktury tabeli, w której tworzymy indeks XML).

Nowa tabela jest klastrowana indeksem o nazwie identycznej z nazwą naszego świeżo utworzonego indeksu głównego XML. Indeks główny XML został założony na kolumnach pk1 i id. Pierwsza z nich, jak można się domyślać, przechowuje wartości z kolumny XMLTableID (z kolumny klucza głównego – jeżeli klucz główny oryginalnej tabeli jest złożony, w utworzonej tabeli wewnętrznej pojawią się kolumny o nazwach pk1, pk2, pk3 itd.). Druga przechowuje identyfikator węzła w zakresie pojedynczego dokumentu XML (taki identyfikator węzła w dokumencie XML).

Sam indeks główny XML jeszcze niczego nam praktycznie nie oferuje (nie udało mi się skonstruować przykładu, w którym sam indeks główny XML przyspieszałby wykonywanie zapytania). Stanowi jednak punkt wyjścia do utworzenia kolejnych indeksów XML. Ważna uwaga – indeksu głównego XML nie da się założyć, jeżeli w tabeli nie ma klucza głównego. Ciekawostka – jeżeli chcemy tworzyć indeksy XML, klucz główny tabeli nie może być "szerszy" niż 15 kolumn (bo indeks w tabeli wewnętrznej, jak w każdej tabeli, można założyć na maksymalnie 16 kolumnach, a musi się w nim zmieścić kolumna id).

Spróbujmy podejrzeć, jak wygląda tabela wewnętrzna. W tym celu należy połączyć się z SQL Serverem za pomocą połączenia DAC. Ja użyję do tego trybu SQLCMD Mode (tryb ten włącza się w Management Studio w menu Query) i dyrektywy :CONNECT służącej do nawiązania tymczasowego połączenia ze wskazanym serwerem.

:CONNECT Admin:0809-001
USE XMLTest;
SELECT * FROM sys.xml_index_nodes_2105058535_256000;
GO

Wynik:

image

A więc dla każdego dokumentu XML powstało w tym przypadku 7 wierszy w tabeli wewnętrznej (w sumie dla 1980 wierszy z tabeli dbo.XMLTable dostałem 13860 wierszy w tabeli wewnętrznej). A jeżeli dokumenty XML będą bardziej złożone, liczba wierszy w tabeli wewnętrznej pójdzie w miliony.

Najbardziej oczywista jest zawartość kolumn value (wartości atrybutów i zawartość tekstowa elementów) oraz pk1 (wartości z kolumny klucza głównego tabeli XMLTable). Patrząc na nid możemy dowiedzieć się, który wiersz odpowiada atrybutowi w dokumencie XML (wartość ujemna w kolumnie nid), a który elementowi (wartość dodatnia w kolumnie nid). Wyjątkiem od tej reguły wydają się być deklaracje przestrzeni nazw (namespace’ów), dla których wartości nid są dodatnie.

Pierwsza myśl, jaka mi przyszła do głowy, to że w tabeli jest aż 5 kolumn zawierających same wartości NULL. Nie wiem dokładnie, jakie jest przeznaczenie tych kolumn (choć mogę się domyślać, że w tagname powinny znaleźć się nazwy elementów – tagów z dokumentu XML), ani dlaczego nie są używane, ale testowałem różne warianty dokumentów XML (z przestrzeniami nazw, z prefiksami kolumn) i nigdy nie zauważyłem w tych kolumnach niczego poza NULLami. "For future use"???

Druga myśl – te binaria w kolumnie id to zapewne sławny ORDPATH. A ponieważ hierarchie kojarzą mi się od ponad roku z typem danych hierarchyid, nie mogłem sobie odmówić próby:

:CONNECT Admin:0809-001
USE XMLTest;
SELECT CAST(id AS hierarchyid).ToString(), *
FROM sys.xml_index_nodes_2105058535_256000;
GO

Wynik: dwa wiersze (z hierarchyid w pierwszej kolumnie!) oraz komunikat:

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24000: SqlHierarchyId operation failed because HierarchyId object was constructed from an invalid binary string.
Microsoft.SqlServer.Types.HierarchyIdException:
   at Microsoft.SqlServer.Types.OrdPath.ExtractComponent(UInt16& bitOffset, SComponent& component, levelType& type)
   at Microsoft.SqlServer.Types.OrdPath.ToString()
   at Microsoft.SqlServer.Types.SqlHierarchyId.ToString()

Skoro wywrotka nastąpiła na wierszu odpowiadającym atrybutowi, postanowiłem odfiltrować atrybuty:

:CONNECT Admin:0809-001
USE XMLTest;
SELECT CAST(id AS hierarchyid).ToString(), *
FROM sys.xml_index_nodes_2105058535_256000
WHERE nid > 0;
GO

Tym razem wynik jest taki:

image

A więc jednak ORDPATH (albo przynajmniej "prawie", bo nadal nie umiem rozszyfrować, jakimi ścieżkami zostały opatrzone atrybuty) :-)

W kolumnie hid trzymane jest zahaszowane id (jest prawdobodobne, że w haszu została także umieszczona nazwa elementu / atrybutu).

Pozostałe indeksy XML

Mając założony główny indeks XML pora założyć pozostałe indeksy - indeksy secondary (wolę tego słowa nie tłumaczyć ;-)).

CREATE XML INDEX IX_XML_Path
ON dbo.XMLTable (XMLColumn)
USING XML INDEX IX_XML_Primary
FOR PATH;
GO
CREATE XML INDEX IX_XML_Value
ON dbo.XMLTable (XMLColumn)
USING XML INDEX IX_XML_Primary
FOR VALUE;
GO
CREATE XML INDEX IX_XML_Property
ON dbo.XMLTable (XMLColumn)
USING XML INDEX IX_XML_Primary
FOR PROPERTY;
GO

Po założeniu trzech nowych indeksów XML (każdy innego typu) oglądam, jak teraz jest indeksowana tabela wewnętrzna:

EXEC sp_helpindex 'sys.xml_index_nodes_2105058535_256000';
GO

image

Mamy więc indeksy secondary następujących typów:

  • PATH – tu kluczem indeksu w tabeli wewnętrznej są kolumny hid i value, a co za tym idzie taki indeks może być pomocny, gdy próbujemy optymalizować zapytania, w których szukamy dobrze określonych ścieżek i znamy wartości węzłów tekstowych, np.
SELECT 
x.XMLTableID,
T.c.value('@object_id','int') AS object_id
FROM dbo.XMLTable AS x
CROSS APPLY x.XMLColumn.nodes('/object') AS T(c)
WHERE T.c.exist('name/text()[. = "objects"]') = 1;

  • VALUE – tu kluczem są kolumny value i hid, a zatem taki indeks może się przydać, gdy szukamy określonych wartości w nieprecyzyjnie określonych ścieżkach w dokumencie XML, np.
SELECT 
x.XMLTableID,
T.c.value('(name/text())[1]','sysname') AS name
FROM dbo.XMLTable AS x
CROSS APPLY x.XMLColumn.nodes('/object') AS T(c)
WHERE T.c.exist('//type/text()[. = "VIEW"]') = 1;

  • PROPERTY – tu kluczem są kolumny pk1 (pk2, pk3,…), hid i value, co oznacza, że indeks ten przydaje się, gdy wyszukiwanie w tabeli oryginalnej odbywa się po kolumnie klucza głównego, ale z dokumentów XML wydobywane są konkretne węzły.

Ważne, jak pytamy

Warto zapamiętać parę reguł, jeżeli chcemy rzeczywiście wykorzystywać indeksy XML do optymalizacji naszych zapytań. Oto niektóre z tych reguł:

  1. Ścieżki w filtrach XPath powinny być jak najdłuższe, np.

    JakisElement/@JakisAtrybut[. = "120"]

    zamiast
    JakisElement[@JakisAtrybut = "120"]

  2. Jak najmniej dowolności – unikamy ścieżek typu //JakisElement czy ../../CosDwaPoziomyWyzej.
  3. Jeżeli nie ma narzuconego schematu dokumentu XML (nie ma XML SCHEMA COLLECTION, które pilnuje zawartości wstawianych dokumentów XML), należy pilnować, by wartości w filtrach były ujęte w cudzysłowy/apostrofy (czyli aby były wartościami tekstowymi), np.

    JakisElement/@JakisAtrybut[. = "120"]

    zamiast

    JakisElement/@JakisAtrybut[. = 120]
  4. Unikamy filtrów "w środku" ścieżki, np.

    JakisElement1[@JakisAtrybut1 = "123"]/JakisElement2/JakisElement3[@JakisAtrybut = "120"]

Takich reguł zapewne można znaleźć więcej. Wychodzą na jaw na ogół wtedy, gdy nie możemy osiągnąć przyzwoitej optymalizacji za pomocą indeksów. Testy, testy, testy :-)

Suma sumarum

Indeksy XML to żadna magia - ot, zwykłe B+-Tree ;-) Nawet sam Microsoft traktuje je jak pospolite indeksy, bo informacje o nich są zwracane przy odpytywaniu widoku sys.indexes (choć jest dedykowany widok sys.xml_indexes, który wyświetla metadane tylko indeksów XML). Wiedząc, co się dzieje "w bebechach", można sobie łatwiej wyobrazić, jaki wpływ może mieć utworzenie indeksów XML na wydajność. I, co ważne, można lepiej rozumieć plany wykonania zapytań, które wykorzystują metody typu danych XML i przeszukują / przetwarzają dokumenty XML przechowywane w bazie danych.

Polecam zabawę w małego odkrywcę. Sprawdź, jaki wpływ na wydajność i strukturę (?) indeksów XML może mieć na przykład dodanie kolekcji schematów. Zobacz, jak w indeksach XML trzymane są informacje o przestrzeniach nazw i prefiksach elementów.

Podobny "research" można zrobić dla indeksów na typach przestrzennych (geometry i geography). Rączki na klawiatury i dajcie znać, jak coś ciekawego Wam wyjdzie w doświadczeniach.

PS1. Plik z kodem przedstawionym w tej notce można pobrać poniżej.
PS2. Spróbujcie odpytywać tabele wewnętrzne założone pod indeksy XML w bazie AdventureWorks2008. U mnie dostałem wyjątek w SSMS (niepoprawny format tekstu???) ;-)
PS3. Podczas testów udało mi się osiągnąć maksymalny poziom zagnieżdżenia węzłów w dokumencie XML w SQL Server 2008. Otrzymałem przez to taki komunikat:

Msg 6335, Level 16, State 102, Line 1
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

[EDIT]

Nietypowane wartości atrybutów

Marek Powichrowski, spec od XML-a, stwierdził, że w testach nie wyszła mu żadna różnica a propos punktu 3 z paragrafu "Ważne, jak pytamy" (test robiłem dawno temu na SQL Server 2005). Przeprowadziłem więc taki test jeszcze raz i oto wynik.

Tabela miała około 130 tysięcy wierszy, a dokumenty XML były takie, jak w powyższej notce (metadane obiektów z bazy danych). Zapytania do testów:

-- Zapytanie 1 - "złe"
DECLARE @t datetime = GETDATE();
SELECT
x.XMLTableID,
T.c.value('(name/text())[1]','sysname') AS name,
T.c.value('@id','int') AS id
FROM dbo.XMLTable AS x
CROSS APPLY x.XMLColumn.nodes('/object') AS T(c)
WHERE T.c.exist('./@id[. = 546135905]') = 1;
SELECT DATEDIFF(ms, @t, GETDATE());
GO
-- Zapytanie 2 - "dobre"
DECLARE @t datetime = GETDATE();
SELECT
x.XMLTableID,
T.c.value('(name/text())[1]','sysname') AS name,
T.c.value('@id','int') AS id
FROM dbo.XMLTable AS x
CROSS APPLY x.XMLColumn.nodes('/object') AS T(c)
WHERE T.c.exist('./@id[. = "546135905"]') = 1;
SELECT DATEDIFF(ms, @t, GETDATE());

I teraz czasy:
  • Bez indeksów XML
    • zapytanie 1 – 3200 ms
    • zapytanie 2 – 2200 ms
  • Tylko z indeksem PRIMARY
    • zapytanie 1 – 3900 ms
    • zapytanie 2 – 800 ms
  • Z indeksami SECONDARY
    • zapytanie 1 – 1180 ms (a czasem gorzej – około 2 sekund)
    • zapytanie 2 – 180 ms

Wyniki były powtarzalne dla różnych wartości @id. Różnice, moim zdaniem, są widoczne gołym okiem.

Opublikowane 15 grudnia 2009 09:36 przez brejk
Attachment(s): XMLIndexes.txt

Komentarze:

# re: [PL] Indeksy XML w SQL Server 2008 od środka

15 grudnia 2009 17:51 by marekpow

Paweł,

przećwiczyłem wariant:

JakisElement/@JakisAtrybut[. = "120"]

zamiast

JakisElement/@JakisAtrybut[. = 120]

na tabeli bez indeksów XML (około 15tys. rekordów z dość złożonymi nieregularnymi, XML-ami).

Przed każdym zapytaniem czyściłem cache. I wyniki było zgodne co do sekundy. Ścieżka była podane dokładanie od rdzenia i była wyrywkiem z pełne ścieżki. Próbowałem również wariantu gdy na końcu nie jest atrybut a liść. I wynik mam ten sam co do sekundy. Namespace nie było określone dla zapytania.

# re: [PL] Indeksy XML w SQL Server 2008 od środka

15 grudnia 2009 21:04 by brejk

@marekpow: Hmmm, może coś pokręciłem, jak robiłem doświadczenia :-) A testowałeś, co się stanie, jak narzucisz schemat?

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 07:11 by marekpow

Zrobię taki test. Ale spodziewam się zmian w przeciwnym kierunku (szybszej odpowiedzi dla typizowanego XML-a)A może masz zachowany ten przykład i da się go odtworzyć?

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 08:42 by brejk

@marekpow: Niestety, nie mam. Poszedł z dymem dawno temu (jeszcze na 2005 robiłem).

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 10:46 by marekpow

@brejk: Moje testy wykonywałem dla wersji 2008. No i bez indeksów XML i dla funkcji exist a nie nodes. Twoje wyniki z zastosowaniem indeksów XML są bardzo ciekawe. Bo jeżeli zapytanie odnosi sie do nietypizowanego XML-a to wydaje sie być jasne, że zapytanie o @id z apostrofami (czyli jako string) będzie szybsze niż jako liczba (czyli int). Bo przecież dla takiego XML-a server spodziewa się na liściu wyłącznie typu string. Więc musi zrobić rzutowanie ale żeby to powodowało aż takie różnice (o rząd wielkości) dla zapytania korzystającego z indeksów secondary to jestem zaskoczony.

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 11:05 by brejk

@marekpow: Wszystkie regułki, które podałem w notce, dotyczą wykorzystania indeksów. Rząd wielkości różnicy w podanym przykładzie był zaskoczeniem i dla mnie. W przypadku dużych plików XML (za to mniejszej ich liczby) miałem różnice 2-3 razy szybciej (ale nie 10...).

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 11:34 by marekpow

@brejk: Ostatecznie udało mi się potwierdzić Twoje wyniki. Wraz ze wzrostem tabeli przepaść pomiędzy predykatem z liczbą i stringiem powiększa się na niekorzyść liczby.

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 11:48 by brejk

@marekpow: Dodajmy, że na zimnym cache'u różnica nie jest aż tak wyraźna. Ale mimo wszystko jest widoczna.

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 12:14 by marekpow

@brejk: Natomiast silna typizacja kolumny XML według XSD wygenerowanego na podstawie zawartości tej kolumny daje uzysk około 10%. Mniej niż się spodziewałem ale to może być wyraźniejszy efekt dla samego zapytania z exist bez nodes.

# re: [PL] Indeksy XML w SQL Server 2008 od środka

16 grudnia 2009 23:07 by marekpow

@brejk: Poćwiczyłem ten Twój przykład w dwóch wariantach. Jeden z użyciem nodes i exist a drugi tylko z exist. I tym razem zastosowałem go nie do "top level id" ale do wewnętrznego elementu. Doświadczenie zrobiłem z kolumna xml nietypizowaną i typizowaną. Oto moje przykłady:

DBCC DROPCLEANBUFFERS

-- Zapytanie 1 - "nodes i exist"

DECLARE @t datetime = GETDATE();

SELECT

 x.XMLTableID,

 T.c.value('(name)[1]','sysname') AS name ,

 T.c.value('@id','int') AS id

FROM dbo.XMLTable AS x

CROSS APPLY x.XMLColumn.nodes('/object') AS T(c)

WHERE t.c.exist('./type[. = "VIEW"]') = 1;

SELECT DATEDIFF(ms, @t, GETDATE());

GO

DBCC DROPCLEANBUFFERS

-- Zapytanie 2 - "tylko exist"

DECLARE @t datetime = GETDATE();

SELECT

 x.XMLTableID,

 xmlcolumn.value('(/object/name)[1]','sysname') AS name ,

 XMLColumn.value('(/object/@id)[1]','varchar(10)') AS id

FROM dbo.XMLTable AS x

WHERE xmlcolumn.exist('/object/type[. = "VIEW"]') = 1;

SELECT DATEDIFF(ms, @t, GETDATE());

Wyniki obu zapytań dla każdego z wariantów z osobna różnią się nieznacznie. Natomiast typizowana kolumna xml daje dwa razy szybsze wyszukiwanie. Na w sumie dość prostym dokumencie XML w niej zawartym. Spodziewałbym się, że dla bardziej skomplikowanych będzie to uzysk jeszcze większy.

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek