Czy mi się przyda hierarchyid?

80. spotkanie PLSSUG Warszawa, 09.10.2014.

O czym będzie?

• O hierarchiach ogólnie

• Hierarchyid jako takie

• Męczenie i dręczenie hierarchyid

• Ograniczenia i możliwości

• Linki i literatura

O mnie

Bartosz Ratajczyk

programista baz danych(i aplikacji)

czasem też administrator

PHP, JS, C#, T-SQL, SAS 4GL

http://bartekr.net | b.ratajczyk@gmail.com | MCTS, MCP

Hierarchia

ustalony porządek wg wybranego kryterium, np. ważności

Przykłady hierarchii

• schemat organizacyjny firmy

• system plików

• kategorie sklepu internetowego

• wątki dyskusji

• wykaz składników (bill of materials)

• masa innych

• (i innych)

Jak to sensownie obsługiwać w bazie?

• Adjacency List

• Nested sets

• Closure table/bridge table

• Lineage column / materialized path

• (inne)

Adjacency List

element rodzic

A NULL

B A

C A

D B

E B

F E

G C

H C

I C

Nested sets

element lewy prawy

A 1 18

B 2 9

C 10 17

D 3 4

E 5 8

F 6 7

G 11 12

H 13 14

I 15 16

Closure table/bridge tableID element

1 A

2 B

3 C

4 D

5 E

6 F

7 G

8 H

9 I

rodzic dziecko

1 2

1 4

1 5

1 6

2 4

2 5

2 6

5 6

… …

rodzic dziecko poziom

1 2 1

1 4 2

1 5 2

1 6 3

2 4 1

2 5 1

2 6 2

5 6 1

… … …

Lineage column/materialized path

elementsciezkaA AB A/BC A/CD A/B/DE A/B/EF A/B/E/FG A/C/GH A/C/HI A/C/I

Hierarchyid

• typ danych .NET

• materialized path w wersji MS

• wystarczy jedna kolumna do obsługi hierarchii

• duże upakowanie danych

• zawsze bez problemów dodasz węzeł potomny

• domyślnie przeszukuje w dół hierarchii

Hierarchyid

Metody obsługi

SELECT

-- statyczne

hierarchyid::GetRoot(),

hierarchyid::Parse('/1/2/3/4/5/'),

-- dynamiczne

@h.GetLevel(),

@h.GetAncestor(@n),

@h.GetDescendant(@m, @n),

@h.IsDescendantOf(@n),

@h.GetReparentedValue(@m, @n),

@h.ToString()

-- tylko .NET

-- hierarchyid.Read()

-- hierarchyid.Write()

Indeksy

Depth first

Indeksy

Breadth first

DEMO

Są pewne ograniczenia

• maksymalny rozmiar węzła to 892 bajty

• kanoniczna reprezentacja poziomu/węzła nie dłuższa niż 14-15 znaków

• chyba że mamy węzeł „z kropkami”

Ale i zalety

• zajmuje mało miejsca

• wystarczy jedna kolumna do obsługi hierarchii

• wygodny zestaw metod do obsługi

Porównanie sposobówobsługi hierarchii

(nie, jednak nie)

Co zapamiętać

• zajmuje niewiele miejsca

• metody tylko generują identyfikator

• programista robi całą robotę

Do poczytania

• Microsoft SQL Server 2008 Bible – Paul Nielsen et al.

• Inside Microsoft SQL Server 2008: T-SQL Querying – Itzik Ben-Gan et al.

• Joe Celko’s Trees and Hierarchies in SQL for Smarties, Second Edition – Joe Celko

Linki

• Jak może działać hierarchyid: http://www.adammil.net/blog/v100_how_the_SQL_Server_hierarchyid_data_type_works_kind_of_.html

• Przykłady porównań metod obsługi hierarchii– Bill Karwin - http://www.slideshare.net/billkarwin/models-for-

hierarchical-data str. 69 lub http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back str. 77

– Stackoverflow: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

• Jedno z porównań prędkości działania obsługi hierarchii w MS SQL http://jsimonbi.wordpress.com/2011/03/01/sql-hierarchy-comparative-performance-2/

• Patent metody Davida Chandlera: http://www.google.com/patents/US6480857