Il risultato di una query, in termini di performance, è sovente influenzato dalla presenza di indici (parliamo di indici non cluster). Questi vengono "piazzati" sulle colonne delle tabelle e coinvolgono diversi tipologie di dato ad esempio stringhe, numeri interi, date etc.
Per ogni indice SQL Server riserva spazio variabile all'interno delle sue strutture di memorizzazione in funzione del tipo di dato e del numero di righe di cui è composta la tabella su cui è attivo l'indice.
È evidente che "farcire di indici" una tabella con milioni di righe ha un impatto molto diverso rispetto alla stessa operazione su una una tabella con poche centinaia o migliaia di righe, questo sia in termini di spazio occupato dall'indice che in termini di performance di ricerca delle informazioni sulle strutture dati.
In questo articolo illustremo come l'impiego di indici su valori HASH possa rendere le migliori performance delle query e ridurre l'ingombro degli indici non cluster.
Cos'è un valore di HASH?
Banalmente, un valore di HASH è un numero intero calcolato da un algoritmo (detto funzione di hash) applicato ad una specifica sequenza di caratteri. Tranne rari casi detti collisioni di HASH, i valori restituiti da una funzione di HASH sono univoci e in grado di identificare la stringa di partenza. Per maggiori informazioni sugli HASH potete seguire questo link.
Impiegare le chiavi di HASH nel disegno di un database
Immaginiamo di avere una tabella dal nome LIBRI costituita da 1 milione di righe. Esaminamo la struttura della tabella:
Listato 1. Schema della tabella LIBRI
CREATE TABLE LIBRI
(
LibroId INT IDENTITY(1,1) PRIMARY KEY,
Titolo VARCHAR(100) NOT NULL
)
Le ricerche del sistema delle biblioteca sono basate sul titolo quindi sarà opportuno creare un indice su questa colonna. Calcoliamo l'occupazione di spazio dell'indice: 100 caratteri/riga per 1 milione di righe/tabella.
Creare un indice simile sulla colonna Titolo è inutile, perchè SQL Server non lo prenderà mai in considerazione durante la compilazione del piano della query. Per SQL Server è molto più semplice effettuare la scansione dell'intera tabella tramite l'indice cluster piuttosto che utilizzare l'indice non cluster su Titolo.
Ma allora che strada dobbiamo percorrere per creare un indice efficente ed utilizzabile dalle query durante la ricerca dei titoli? Semplice, creare un indice di HASH sulla colonna Titolo ma per farlo dobbiamo prima:
- Aggiungere una colonna
[HashTitolo]calcolata in base al valore di HASH dei valori della colonna Titolo. La colonna HashTitolo contiene interi (occupano solamente 4 bytes per riga e non più 128!) restituito della funzioneCHECKSUM('Titolo')di SQL Server. - Aggiungere un indice sulla nuova colonna
[HashTitolo]che ora sarà efficiente ed efficace ai fini della ricerca - Creare una query in grado di ricercare i titoli in funzione della colonna di Hash appena creata
Dettagli sulla funzione CHECKSUM() sono disponibili nel paragrafo successivo. Cominciamo a rendere efficace la query di ricerca seguenti i passi descritti prima, quindi modifichiamo la tabella LIBRI e aggiungiamo la colonna calcolata [HashTitolo] con l'ausilio della funzione T-SQL CHECKSUM():
Listato 2. Creazione di una colonna calcolato con i codici HASH dei titoli
SET ARITHABORT ON
GO
ALTER TABLE LIBRI ADD [HashTtitolo] AS checksum(Titolo)
Ora la tabella LIBRI contiene una nuova colonna con all'interno tutti i codici numerici di HASH per ogni titolo. Successivamente aggiungiamo l'indice sulla colonna [HashTitolo] appena creata:
Listato 3. Creazione un indice sulla colonna [HashTitolo]
CREATE INDEX IDX_HashTitolo ON LIBRI ([HashTitolo])
Infine la query, molto semplice e banale che però presenta un adattamento nella clausola WHERE per supportare la ricerca nella colonna con i valori di HASH del titolo. Vediamo nel listato entrambe le query (senza indice HASH prima e con indice HASH dopo) e successivamente un'immagine dei piani di esecuzione delle due query.
Listato 4. Query ricerca dei titoli (senza indice HASH)
SELECT * FROM LIBRI WHERE Titolo='99A64FDA-B16A-4A7D-AD29-0182EB6703AC'
Listato 5. Query ricerca dei titoli (con indice HASH)
SELECT * FROM LIBRI WHERE Titolo='99A64FDA-B16A-4A7D-AD29-0182EB6703AC'
AND HashTitolo=checksum('99A64FDA-B16A-4A7D-AD29-0182EB6703AC')
Come potete osservare in figura 2 il piano di esecuzione della query considera l'indice HASH (operazione indicata con Index Seek) e lo utilizza per la ricerca del titolo. Al contrario in figura 1 il piano di esecuzione non considera nessun indice e SQL Server procedere alla scansione dell'indice cluster (operazione indicata con Clustered Index Scan). Considerando una ricerca su una tabella con un milione di righe la differenza è di performance dei due approcci è abissale!
La funzione Checksum di SQL Server
La funzione CHECKSUM di SQL Server viene utilizzata per la costruzione di indici hash e ritorna sempre un valore intero a 4 bytes. I valori della funzione non sono tuttavia univoci, ma i duplicati sono alquanto limitati.
Listato 6. Sintassi della funzione
CHECKSUM ( * | expression [ ,...n ] )
CHECKSUM soddisfa le proprietà di una funzione hash, perchè viene restituito lo stesso valore se gli elementi sono dello stesso tipo di dati e risultano uguali quando vengono confrontati tramite l'operatore di uguaglianza (=).
Listato 7. Uso di CHECKSUM
IF CHECKSUM('stringa molto lunga')=CHECKSUM('stringa molto lunga')
PRINT('Sono uguali')
IF CHECKSUM('stringa corta')<>CHECKSUM('stringa molto lunga')
PRINT('Sono diverse')
Conclusione
I valori di HASH possono essere molto utili per creare indici (anche su più colonne) efficaci che SQL Server può usare nei piani delle query e quindi successivamente nella velocizzazione della ricerca delle informazioni. Tuttavia quest'approccio ha delle limitazioni, difatti è applicabile solamente nel caso in cui siano in gioco confronti di eguaglianza tra i valori (questa tecnica non è utilizzabile con l'operatore LIKE ad esempio).
