Tipul de date hierarchyid in SQL Server 2008

SQL Server 2008 introduce un nou tip de date pentru modelarea structurilor ierarhice numit hierarchyid.

In aproape toate proiectele mai mari la care am lucrat pana acum am avut nevoie de o structura ierarhica... ca era vorba de un bill of materials, sau categorii organizate pe mai multe nivele, intotdeauna tabela continea o coloana cu referinta spre o alta coloana din aceeasi tabela.

Cu alte cuvinte pe SQL 2000 si 2005 o astfel de tabela arata astfel:
CREATE TABLE Hierarchy (
Id int PRIMARY KEY IDENTITY(1, 1),
ParentId int
.....
)

De cele mai multe ori, pentru a evita aparitia nodurilor orfane, se adauga si o constrangere de tip foreign key pe coloana ParentId (solutie care functioneaza doar daca se adauga in mod deliberat un nod de root cu ParentId = NULL si care nu va fi niciodata sters).

In SQL Server 2008 s-a introdus un nou tip de date hierarchyid care permite modelarea acestor structuri ierarhice intr-un mod mult mai eficient. Deci tabela anterioara o vom putea crea cu structura:
CREATE TABLE Hierarchy2 (
Id hierarchyid,
...
)

Nu am sa intru in prea multe detalii, pentru ca puteti gasi pe MSDN foarte multe informatii despre:

• cum se foloseste hierarchyid http://msdn2.microsoft.com/en-us/library/bb677173(SQL.100).aspx
• doua tutoriale despre:
• cum se poate converti o tabela intr-o structura ierarhica http://msdn2.microsoft.com/en-us/library/bb677237(SQL.100).aspx
• cum se populeaza o astfel de tabela si cum se obtin datele din ea http://msdn2.microsoft.com/en-us/library/bb677270(SQL.100).aspx


• referinta completa a tipului hierarchyid http://msdn2.microsoft.com/en-us/library/bb677193(SQL.100).aspx

Acestea fiind spuse, sa trecem la treaba... bineinteles ca prima mea intrebare a fost: "ok, la ce bun un nou tip de date, daca o structura ierarhica mi-o puteam defini simplu cu relatii parinte-copil?"

Primul lucru la care m-am gandit a fost sa testez cum se comporta din punct de vedere al performantei... asa ca mi-am creat doua tabele cu urmatoarele structuri:

1. Folosind relatii de tip parinte-copil:
CREATE TABLE Hierarchy (
Id int PRIMARY KEY IDENTITY(1, 1),
ParentId int REFERENCES Hierarchy(Id),
Name nvarchar(50)
)
CREATE INDEX IDX_ParentId ON Hierarchy(ParentId)

2. Folosind noul tip de date hierarchyid
CREATE TABLE Hierarchy2 (
Id hierarchyid,
Level as Id.GetLevel(),
Name nvarchar(50)
)

CREATE CLUSTERED INDEX IDX_Hierarchy2_BF ON Hierarchy2(Level, Id)
CREATE UNIQUE INDEX IDX_Hierarchy2_DF ON Hierarchy2(Id)

Pentru ca diferenta de performanta sa fie vizibila, mi-am generat in ambele tabele cate 406901 inregistrari (25 de copii pentru fiecare nod, iar la al 4-lea nivel m-am oprit... deci 1 (root-ul) + 25 nivel 1 + 25 * 25 nivel 2 + 25 * 25 * 25 nivel 3 + 25 * 25 * 25 * 25 nivel 4).

Pentru structura clasica de tabela, pentru a obtine toti copii descendenti ai unui nod, se face o parcurgere pe latime a arborelui... la fiecare pas se adauga intr-o tabela temporara toti copii directi ai nodurilor de pe ultimul nivel vizitat, apoi se trece la urmatorul nivel.

Cazul cel mai defavorabil este cand nodul de plecare este chiar radacina:

1. Parcurgerea tabelei construita prin relatii parinte-copil:
DECLARE @ParentId int
SET @ParentId = 1
DECLARE @Level int
SET @Level = 1

CREATE TABLE #Hierarchy (Id int, [Level] int, Name nvarchar(50))
INSERT INTO #Hierarchy
SELECT Id, @Level, Name
FROM Hierarchy
WHERE @ParentId = ParentId

WHILE EXISTS (SELECT *
FROM Hierarchy WITH (NOLOCK)
WHERE ParentId IN (SELECT Id
FROM #Hierarchy WITH (NOLOCK)
WHERE [Level] = @Level))
BEGIN
INSERT INTO #Hierarchy (Id, [Level], Name)
SELECT Id, @Level + 1, Name
FROM Hierarchy WITH (NOLOCK)
WHERE ParentId IN (SELECT Id
FROM #Hierarchy WITH (NOLOCK)
WHERE [Level] = @Level)

SET @Level = @Level + 1
END
DROP TABLE #Hierarchy

2. Parcurgerea tabelei construita cu nou tip de date hierarchyid:
CREATE TABLE #Hierarchy2 (Id hierarchyid, [Level] int, Name nvarchar(50))

DECLARE @Level int, @Parent hierarchyid
SET @Level = 1
SELECT @Parent = Id
FROM Hierarchy2
WHERE Id = '/'

WHILE EXISTS (SELECT *
FROM Hierarchy2 (NOLOCK)
WHERE Id.GetAncestor(@Level) = @Parent)
BEGIN
INSERT INTO #Hierarchy2
SELECT Id, Level, Name
FROM Hierarchy2 WITH (NOLOCK)
WHERE Id.GetAncestor(@Level) = @Parent

SET @Level = @Level + 1
END
DROP TABLE #Hierarchy2

Concluzii:

- in primul caz, parcurgerea a durat 3 secunde, iar folosind hierarchyid parcurgerea a fost instanta; deci performantele sunt net superioare folosind noua structura;

- script-ul de parcurgere al tabelei cu hierarchyid este mult mai simplu si usor de modificat.