Utilizzo dei tipi di dati hierarchyid (Motore di database)

Quando si utilizza l'approccio dell'elemento padre/figlio, ogni riga contiene un riferimento all'elemento padre. La tabella seguente definisce una tabella tipica utilizzata per contenere le righe padre e figlio in una relazione padre/figlio:

USE AdventureWorks ;
GO

CREATE TABLE ParentChildOrg
   (
    EmployeeID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(EmployeeID),
    EmployeeName nvarchar(50) 
   ) ;
GO

Confronto padre/figlio e hierarchyid per operazioni comuni

• Le query del sottoalbero sono notevolmente più veloci con hierarchyid.

• Le query discendenti dirette sono leggermente più lente con hierarchyid.

• Lo spostamento di nodi non foglia è notevolmente più lento con hierarchyid. L'inserimento di nodi non foglia e l'inserimento o lo spostamento di nodi foglia sono caratterizzati dalla stessa complessità con hierarchyid.

La relazione padre/figlio è superiore quando esistono le condizioni seguenti:

• La dimensione della chiave è molto importante. Per lo stesso numero di nodi, il valore hierarchyid è uguale a o maggiore di una famiglia di integer (smallint, int, bigint). Questo è solo uno dei motivi per cui utilizzare la relazione padre/figlio in casi rari, poiché hierarchyid si colloca meglio nell'I/O e nella complessità della CPU che nelle espressioni della tabella comune richieste quando si utilizza la struttura padre/figlio.

• Le query vengono eseguite raramente sulle sezioni della gerarchia. In altre parole, le query di solito vengono eseguite solo su un solo punto della gerarchia. In questi casi il percorso unito non è importante. Ad esempio, la relazione padre/figlio è superiore se la tabella dell'organizzazione viene utilizzata solo per l'esecuzione del libro paga per i singoli dipendenti.

• I sottoalberi non-foglia vengono spostati frequentemente e la prestazione è molto importante. In una rappresentazione padre/figlio, la modifica del percorso di una riga in una gerarchia influisce su una sola riga. La modifica del percorso di una riga in un utilizzo di hierarchyid influisce sulle righe n, dove n è il numero di nodi del sottoalbero spostato.

  Se i sottoalberi non-foglia vengono spostati frequentemente e la prestazione è molto importante, ma la maggior parte degli spostamenti sono a un livello ben definito della gerarchia, suddividere i livelli superiori e inferiori in due gerarchie. In questo modo, tutti gli spostamenti si verificano a livello di foglia nella gerarchia più elevata. Ad esempio, considerare una gerarchia di siti Web ospitata da un servizio. I siti contengono molte pagine disposte in modo gerarchico. I siti di hosting potrebbero essere spostati in altri percorsi nella gerarchia del sito, ma le pagine subordinate raramente vengono disposte nuovamente. Questo potrebbe essere rappresentato tramite:

  CREATE TABLE HostedSites 
     (
      SiteId hierarchyid, PageId hierarchyid
     ) ;
  GO
  

Un documento XML è un albero e pertanto una sola istanza del tipo di dati XML può rappresentare una gerarchia completa. In SQL Server quando viene creato un indice XML, i valori hierarchyid vengono utilizzati internamente per rappresentare la posizione nella gerarchia. 

L'utilizzo del tipo di dati XML può essere superiore quando tutte le seguenti condizioni sono vere:

• La gerarchia completa viene sempre archiviata e recuperata.

• I dati vengono utilizzati in un formato XML dall'applicazione.

• Le ricerche del predicato sono estremamente limitate e non sono importanti per la prestazione.

Ad esempio, se un'applicazione rileva più organizzazioni, archivia e recupera sempre la gerarchia organizzativa completa e non esegue query in una singola organizzazione, una tabella del modulo seguente potrebbe avere senso:

CREATE TABLE XMLOrg 
    (
    Orgid int,
    Orgdata xml
    ) ;
GO

Se E viene specificato come colonna o variabile, E.IsDescendantOf(c) viene trasformato in una ricerca di intervallo. In questo modo viene ridotto in modo significativo il costo della ricerca di discendenti. Se esiste un indice depth-first in E, questa trasformazione aiuta perché tutti i discendenti di E vengono trovati insieme. Ad esempio, il frammento di codice EmployeeId.IsDescendantOf(@value) è eseguito come EmployeeId >= @Value AND EmployeeId <= @Value.DescendantLimit(). DescendantLimit è un metodo interno che determina il limite superiore minimo di tutti i possibili discendenti di un nodo. Non è necessario che @value sia un parametro. Può essere una colonna, ad esempio di una condizione di join.

GetAncestor(n) fornisce l'ennesimo predecessore di un nodo. Questo è utile quando la relazione precisa (padre, figlio, padre del padre ecc.) tra due nodi è necessaria, contrariamente a quanto accade con la relazione IsDescendantOf più generale.

Ad esempio, eseguire la query seguente per cercare tutti i dipendenti il cui responsabile diretto è @value:

SELECT * FROM Employees WHERE EmployeeId.GetAncestor(1) = @value

Questo viene trasformato in una scansione di intervalli per i discendenti di @value, con il predicato originale come residuo. Il codice viene trasformato negli elementi seguenti:

SELECT * FROM Employees 
WHERE 
   EmployeeId >= @Value AND EmployeeId <= @value.DescendantLimit() 
   AND EmployeeId.GetAncestor(1) = @value

Lo scopo è di limitare la scansione al sottoalbero di @value.

Nella query sopra indicata, se @value si trova nei livelli superiori dell'albero, l'ottimizzazione non riduce in modo significativo il numero di righe analizzate. In caso di domande comuni sull' ennesimo predecessore, le applicazioni devono creare un indice breadth-first come descritto in precedenza.

Se esiste un indice breadth-first, la query sopra indicata viene trasformata ulteriormente nel seguente modo:

SELECT * FROM Employees 
WHERE 
   EmployeeId >=@value AND EmployeeId <= @Value.DescendantLimit() 
   AND @value.GetLevel()+1 = EmployeeId.GetLevel()

L'ultima riga (contenente i metodi GetLevel) diviene una ricerca dell'indice nell'indice breadth-first. Se EmployeeId è la chiave di clustering, rappresenta la seconda colonna dell'indice breadth-first e i due predicati diventano una ricerca dell'indice che specifica in modo esatto i report diretti trovati insieme n di @value.

Le trasformazioni GetAncestor non sono limitate alle query per gli elementi padre diretti. L'argomento GetAncestor può essere una costante o una variabile.