L'obbiettivo è che questo sia il post definitivo sulla diatriba GUID vs INT: in molti sostengono che l'uso di un GUID come Primary Key sia una cosa buona e giusta, da usare nella maggior parte dei casi al posto del "solito" numero intero (tipicamente generato tramite IDENTITY) perchè più comodo e senza particolari controindicazioni. Altri invece si schierano a favore dell'INT perchè più leggero e quindi apparentemente con meno problemi di performance.
Chi ha ragione? Con quale criterio è bene usare un GUID o un INT? Dato che da troppo tempo si sentono diverse campane in merito e mai nessuno si è preso la briga di dimostrare scientificamente che impatto può avere l'uso di GUID come Primary Key al posto dei "classici" INT, è giunto il momento di farlo, in modo da porre fine alla diatriba in modo rigoroso ed inconfutabile. In questo modo anche coloro che non conoscono in profondità l'archittettura di SQL Server (se la conoscesero la risposta sarebbe già chiara) potranno avere un riscontro oggettivo, chiaro ed inequivocabile.
Premetto che sto ovviamente parlando dal punto di vista strettamente tecnico. Dal punto di vista architetturale e di design del database i motivi per cui è meglio usare l'uno piuttosto che l'altro dipendono dai requisiti funzionali che si devono soddisfare, sui quali - in certa misura - non si può discutere. Si può però discutere su cosa sia meglio scegliere se dal punto di vista funzionale non ci sono problemi e si tratta quindi di una scelta puramente tecnica.
Non sto in questa sede a ripetere le motivazioni riportate in un articolo già pubblicato su MSDN, e passo quindi subito a concentrarmi sulla simulazione di una situazione reale. Ipotizziamo di usare un GUID come Primary Key delle nostre tabelle, e quindi ipotizziamo di avere una tabella del genere:
create table dbo.TestGuid
(
id uniqueidentifier not null primary key default(newid()),
dt1 datetime not null,
dt2 datetime not null,
txt1 varchar(16) not null,
txt2 varchar(24) not null,
int1 int not null,
int2 int not null,
fk uniqueidentifier not null
);
go
create nonclustered index uix__TestGuid__fk on TestGuid(fk)
go
Com'è possibile notare si sta anche prendendo in considerazione il fatto che un Primary Key esiste per essere usata, e quindi la suddetta tabella ha anche un Foreign Key - anch'essa GUID - che fa riferimento alla Primary Key di un'altra ipotetica tabella usata nel nostro DB di prova. Come spesso accade (o dovrebbe accadere), su tale FK è creato un indice Non-Cluster.
Questo approccio di permette di vedere in una situazione reale, seppur semplificata (in quanto la tabella contiene poche colonne e solo un indice Cluster ed uno Non-Cluster, quando invece la realtà è tipicamente molto più complessa) come i GUID impattino sul nostro sistema.
Ma che tipo di impatto vogliamo controllare? Dobbiamo tenere in considerazione tutte le problematiche tecniche che gravitano attorno ad una tabella, e quindi dobbiamo misurare il grado di performance e la quantità di manutenzione indotta. In questo caso manutenzione significa necessità di re-indicizzare una tabella in modo da mantenere gli indici e quindi le operazioni di I/O sempre efficienti.
La tabella che utilizza la soluzione basata su Integer - utilizzata come confronto - è fatta invece in questo modo:
create table dbo.TestInt
(
id int identity not null primary key,
dt1 datetime not null,
dt2 datetime not null,
txt1 varchar(16) not null,
txt2 varchar(16) not null,
int1 int not null,
int2 int not null,
fk int not null
);
go
create nonclustered index uix__TestInt__fk on TestInt(fk)
go
Com'è possibile notare le uniche cose che cambiamo sono i tipi di dato usati per la PK e per l'ipotetica FK utilizzata.
I test sono fatti su SQL Server 2008 in modo da poter usare i nuovi tipi di dato che sono molto più precisi dei precedenti, permettendoci una risoluzione di 100ns. Il test consiste nel caricamento di 100.000 righe, misurando la velocità dello stesso e la frammentazione dei dati alla fine del processo.
Questo lo script per caricare la tabella con i GUID:
set nocount on;
truncate table dbo.TestGuid;
declare @ts as time(7) = sysdatetime();
insert into
dbo.TestGuid with (tablockx) (dt1, dt2, txt1, txt2, int1, int2, fk)
select
dt1 = '20000101',
dt2 = '20080101',
txt1 = 'ABCDEFGHILMNOPRS',
txt2 = 'ABCDEFGHILMNOPRS',
int1 = 123456789,
int2 = 123456789,
fk = '0D5C533A-A351-4D71-8DF6-120A464CA4CE'
from
dbo.fn_Nums(100000)
option (maxdop 1)
declare @te as time(7) = sysdatetime();
select datediff(ms, @ts, @te);
go
e questo quello per caricare la tabella con gli INT:
set nocount on;
truncate table dbo.TestInt;
declare @ts as time(7) = sysdatetime();
insert into
dbo.TestInt with (tablockx) (dt1, dt2, txt1, txt2, int1, int2, fk)
select
dt1 = '20000101',
dt2 = '20080101',
txt1 = 'ABCDEFGHILMNOPRS',
txt2 = 'ABCDEFGHILMNOPRS',
int1 = 123456789,
int2 = 123456789,
fk = 1
from
dbo.fn_Nums(100000)
option (maxdop 1)
declare @te as time(7) = sysdatetime();
select datediff(ms, @ts, @te);
go
L'hint option (maxdop 1) è utilizzato per essere al 100% sicuri che in nessun caso SQL Server possa scegliere di parallelizzare in modo differente le due query, mentre l'hint tablockx ci assicura che i lock vengano acquisiti sull'intera tabella si dall'inizio e quindi non intervenga il meccanismo di lock-escalation che potrebbe inficiare le prestazione della query nel caso della presenza di altre query concorrenti nel nostro sistema di test.
Effettuanto per 50 volta ciascuna query, le performance sono le seguenti:
Query di insert di 100.000 righe nella tabella TestGuid: 2342ms
Query di insert di 100.000 righe nella tabella TestInt: 1497ms
i test sono stati eseguiti sia utilizzando semplicemente il Management Studio (tramite il comando go 50), sia tramite SQLQueryStress, un tool fondamentale in questi casi:
Nell'inserimento massivo di dati, la tabella TestInt risulta più performante di oltre il 50% (!!!!!!) Non stiamo quindi parlando di noccioline.....
Bene, ok per l'inserimento di un certa quantità di righe in un sol colpo, ma per quanto riguarda l'inserimento di una singola riga? Tra le due soluzioni non c'è nessuna differenza apprezzabile. Il che è piuttosto semplice da spiegare: l'overhead necessario per il compimento della transazione maschera il peso (piccolo rispetto al peso della transazione) dell'operazione di insert, che quindi non è percepibile, mentre tale differenza si manifesta molto bene quando ci sono molti inserimenti ed una sola transazione, in quanto si verifica la condizione opposta, ossia il peso della transazione diventa trascurabile rispetto al peso delle insert.
Detto ciò, passiamo ad analizzare la frammentazione dei dati? Utilizzando la DMF sys.dm_db_index_physical_stats è possibile analizzare la struttura fisica degli indici presenti sulle tabelle:
select table_name = object_name([object_id]), index_id, index_type_desc, index_depth, avg_fragmentation_in_percent, page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') where index_level = 0 and object_name([object_id]) in ('TestGuid', 'TestInt')
go
Come potete notare su questi dati è bene riflettere:
La frammentazione degli indici sulla tabella TestGuid è pari al 99%. (Terrificante)
Per contenere la stessa quantità di dati, sulla tabella TestGuid l'indice cluster necessità di 1882 pagine anziche di 576 mentre l'indice non cluster di 981 invece di 136 (!!!!!!)...il che implica che l'efficienza degli indici nella tabella TestInt è rispettivamente pari al 92% in più per l'indice cluster e del 348% in più per quanto concerne l'indice non-cluster.
E' bene anche notare che l'indice Non-Cluster nella tabella TestInt ha un profondità di due livelli anzichè tre, e ciò implica che l'operazione di Bookmark Lookup, per tutte le query che ne dovessero fare uso, sia meno onerosa rispetto all'indice non-cluster sulla tabella TestGuid, abbassando quindi il valore soglia di selettività necessaria per usare tale indice. Detto in altre parole, ma come già si dovrebbe sapere, un indice non-cluster più magro è più "appetibile" per SQL Server, che quindi lo userà con più probabilità.
La tabella TestGuid ha quindi bisogno di molta più manutenzione, dato che si frammenta molto più velocemente che la tabella TestInt: il che significa dover re-indicizzare molto più spesso, cosa che può essere problematica a meno di non avere una licenza Enterprise e quindi poter fare l'online re-indexing (il che significa spedere molti più soldi...)
Dal punto di vista delle performance, inoltre, la pagine sono piene in media solo al 68% nella tabella TestGuid, mentre sono piene al 99% nel caso della TestInt. Ciò significa che una qualsiasi operazione di lettura è più dispendiosa nella tabella TestGuid, in quanto per leggere lo stesso numero di righe devo fare più I/O...il che può significare andare a creare colli di bottiglia proprio al sistema di I/O, che può quindi diventare un problema per tutto il sistema.
Sempre dal punto di vista delle performance, è bene sottolineare che il valore di "avg_fragmentation_in_percent" rappresenta la quantità di pagine il cui ordine fisico è diverso dall'ordine logico. In parole più semplici: la sequenza in cui le pagine sono state scritte sul disco, per ragioni di performance di scrittura, è diversa da quella in cui l'indice cluster le vorrebbe organizzate (ordine logico). Insomma, è la cara vecchia "frammentazione" che il dottor Norton ci ha sempre detto essere male. Questo comporta un superlavoro per il sistema I/O che ne risente ed infatti porta ad avere una latenza di accesso ai dati più alta (sul mio notebook di test, l'aumento era di un ordine di grandezza...il che significa passare da una situazione buona, ad es 30msec ad una semplicemente pessima, ossia 300msec) con un impatto decisamente negativo sulle performance di lettura (si passa da 0,3 sec per una lettura di tutte le righe ad 1,6 sec....ossia 5 volte di più, ovviamente per una lettura "a freddo" ossia senza l'utilizzo della cache)
Ma non è finita qui. Dato che come ogni database serio SQL Server fa abbondante uso di cache, dobbiamo fare i conti anche con la memoria disponibile sul sistema. Quanto "pesano" quindi gli indici? Dato che finiranno in cache è bene saperlo, perchè la cache è ordini di grandezza inferiore allo spazio su disco disponibile, ed un abuso della cache per indici "grassi" comporta un sovraccarico del sistemo di I/O che come detto prima può diventare un bel collo di bottiglia. Vediamo i dati, utilizzando sempre la DMF di prima:
select
index_name = name,
type_desc,
space_used_in_mb = (page_count * 8.0 / 1024.0)
from
sys.indexes i
inner join
sys.dm_db_index_physical_stats(db_id(), null, null, null, null) P on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]
order by
i.index_id
go
ecco i risultati:
L'indice cluster sulla tabella TestGuid pesa il doppio di quello sulla tabella TestInt, mentre l'indice non-cluster pesa quasi il quadruplo!!!!
In totale stiamo usando circa 19 MB per la tabella TestGuid al posto dei 9 MB della tabella TestInt. Stiamo in pratica abusando della cache del 50% o, se volete, la stiamo usando al 50% dell'efficenza, il che si traduce nel fatto che per avere le stesse performance dobbiamo usare il doppio della memoria.
Con conclusioni possiamo tirare quindi? Che il GUID ha un costo non idifferente, in termini di performance e di manutenzione, che prima o poi impatteranno sul budget hardware, rendendo necessario spendere più soldi per avere performance che altrimenti potremmo aver avuto gratis.
Sono quindi di evitare, in favore di un uso assolutistico di INT IDENTITY come Primary Key? NO, NO, e poi NO. Questo post non vuole mettere paura, ma mira a promuovere un utilizzo consapevole. Se per esigenze funzionali è necessario usare un GUID facciamolo, sapendo che però ha un costo. Quello che non bisogna mai fare è usare qualcosa senza sapere che impatto può avere nella nostra soluzione, sia ora sia in futuro. Ricordate infatti che un db vive molto più a lungo di un'applicazione, e quindi come "database architect" (o dba o muratori del db o ingegneri dei dati, chiamatevi un pò come volete, ma ricordate che disegnare un database è un'operazione di grande responsabilità), abbiamo il dovere di essere più lungimiranti di altri.
Ma quindi se è necessario usare un GUID non c'è modo di salvare capra e cavoli? Posto che sull'articolo su MSDN citato in precedenza è dimostrato come non sempre l'utilizzo di GUID è necessario, anche se in prima battuta può sembrarlo, se state usando SQL Server 2005 o superiori, un buon compromesso può essere l'uso di SEQUENTIALGUID, in modo che il problema della frammentazione dei dati sia quantomeno evitato, in quanto vengono generati valori GUID sequenziali, monotonici crescenti, e quindi al riparo dal problema della frammentazione. Rimane il fatto che un GUID rende gli indici "cicciotti" e quindi poco efficienti in termini di caching e I/O, ma se non ci può fare altrimenti, questo rappresenta un buon compromesso. Il migliore attualmente, IMHO.
Credo che ora la diatriba "GUID si / GUID no" possa avere termine...ognuno con questo post può giudicare se la spesa in termini di prestazioni e manutenzione vale la candela nel proprio gioco oppure no. Tutto il resto sono ipotesi, illazioni, castelli di sabbia. L'unico modo per poter verificare una cosa è usare un ben noto metodo: quello scentifico. Lasciate stare chi parla semplicemente per supposizioni: in ambito tecnico, il campo del database non è solo quello informatico ma anche e soprattutto quello matematico, in cui non c'e spazio per altro che non una dimostrazione rigorosa. Fidatevi solo di coloro che ve la forniscono o provatela voi stessi. Per tutto il resto Fabian Pascal ha già detto tutto in merito: http://www.dbdebunk.com/page/page/3161496.htm.