Article complet: GUID versus auto incrément... Le verdict

L'idée d'utiliser un GUID comme clef primaire en lieu et place d'un INT auto incrémente est généralement un très mauvaise idée en terme de performances...

[Suite:]

Certaines personnes ne pensent pas au coût induits par l'utilisation d'un tel type de données en terme de volume. Voici quelques arguments et une démonstration...

1) le calcul du GUID est plus long quoiqu'on en dise qu'un simple autoinc, même s'il existe des techniques minimisant ce calcul (sérialisation de GUID)

2) le stockage d'un GUID nécessite 16 octets soit 4 fois plus qu'un INT, et donc 8 fois plus couteuses en terme de comparaison dans les jointures...

3) la génération des GUID n'est pas monotone au sens mathématique du terme et conduit donc a de la fragmentation d'index immédiatement.

4) la clef de la table est à défaut un index cluster et sa valeur est reprise dans tous les autres index. Ces index sont donc tous 4 fois plus volumineux pour la valeur de clef de retour.

5) il n'y a aucun intérêt à utiliser un tel type de données car je doute qu'un jour vous ayez 2 puissance 128 lignes dans votre table, soit 34028236692093846346337460743177000000 lignes à stocker...

J'ai été confronté dans plusieurs audit de bases de données à des bases pourries par les très mauvaises performances induite par l'utilisation systématique des GUID. Bien entendu cela n'affecte pas les performances des petites bases, mais lorsque le volume des données à traiter commencer à dépasser largement la RAM, c'est à dire quand il est trop tard pour revenir en arrière.
Lisez les articles que j'ai consacré à l'optimisation : http://sqlpro.developpez.com/optimisation/
en particulier, partie 3 page 9

Maintenant pour vous convaincre de la chose un petit test :

USE master; 
GO 
 
IF EXISTS(SELECT * 
          FROM   sys.databases 
          WHERE  name = 'DB_GUID') 
   DROP DATABASE DB_GUID; 
GO 
 
CREATE DATABASE DB_GUID 
GO 
 
USE DB_GUID 
GO 
 
CREATE TABLE T_GUID1 
(GUID1    UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
 DATA1    VARCHAR(642)); 
GO 
 
CREATE TABLE T_GUID2 
(GUID2    UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
 DATA2    VARCHAR(64), 
 GUID1    UNIQUEIDENTIFIER FOREIGN KEY REFERENCES T_GUID1 (GUID1)); 
GO 
 
CREATE INDEX X ON T_GUID2 (GUID1); 
GO 
 
DECLARE @I INT, @DT1 DATETIME2(3), @DT2 DATETIME2(3) 
SET @I = 50000; 
SET @DT1 = CURRENT_TIMESTAMP; 
 
WHILE @I > 0 
BEGIN 
   INSERT INTO T_GUID1 VALUES (NEWID(), 'GUID : la plaie des clef primaires...'); 
   SET @I -= 1;   
END; 
 
INSERT INTO T_GUID2 
SELECT NEWID(), 'UNIQUEIDENTIFIER : la galère des index !!!', GUID1 
FROM   T_GUID1; 
 
SET @DT2 = CURRENT_TIMESTAMP; 
 
CHECKPOINT; 
 
SELECT DATEDIFF(millisecond, @DT1, @DT2) 
 
 
CREATE TABLE T_ID1 
(ID1    INT IDENTITY PRIMARY KEY, 
 DATA1  VARCHAR(642)); 
GO 
 
CREATE TABLE T_ID2 
(ID2    INT IDENTITY PRIMARY KEY, 
 DATA2  VARCHAR(64), 
 ID1    INT FOREIGN KEY REFERENCES T_ID1 (ID1)); 
GO 
 
CREATE INDEX Y ON T_ID2 (ID1); 
GO 
 
DECLARE @I INT, @DT1 DATETIME2(3), @DT2 DATETIME2(3) 
SET @I = 50000; 
SET @DT1 = CURRENT_TIMESTAMP; 
 
WHILE @I > 0 
BEGIN 
   INSERT INTO T_ID1 VALUES ('GUID : la plaie des clef primaires...'); 
   SET @I -= 1;   
END; 
 
INSERT INTO T_ID2 
SELECT 'UNIQUEIDENTIFIER : la galère des index !!!', ID1 
FROM   T_ID1; 
 
SET @DT2 = CURRENT_TIMESTAMP; 
 
CHECKPOINT; 
 
SELECT DATEDIFF(millisecond, @DT1, @DT2) 
GO 
 
SET STATISTICS IO ON; 
GO 
 
SELECT * 
FROM   T_GUID1 AS T1 
       INNER JOIN T_GUID2 AS T2 
             ON T1.GUID1 = T2.GUID1 
              
 
SELECT * 
FROM   T_ID1 AS T1 
       INNER JOIN T_ID2 AS T2 
             ON T1.ID1 = T2.ID1 
              
GO 
              
DBCC SHOWCONTIG ('T_GUID1') WITH ALL_INDEXES, TABLERESULTS;  
DBCC SHOWCONTIG ('T_ID1') WITH ALL_INDEXES, TABLERESULTS;

Ce script créé une base de données avec 4 tables. Deux avec des GUID et 2 avec des ID et les peuplent en calculant le temps de traitement, puis en mesurant les IO de deux requêtes SELECT et enfin en comparant les fragmentation des index.

Voici le verdict :

Temps de population des tables :
Avec GUID : 36143 ms
Avec ID + : 18640 ms
Perte induite par le GUID : + 94 % (soit près du double de temps)

Pages lues par des requêtes SELECT :
Avec GUID :

• Table 'T_GUID1'. Nombre d'analyses 1, lectures logiques 603
• Table 'T_GUID2'. Nombre d'analyses 1, lectures logiques 543

Avec ID + :

• Table 'T_ID1'. Nombre d'analyses 1, lectures logiques 338
• Table 'T_ID2'. Nombre d'analyses 1, lectures logiques 393

Perte induite par le GUID : + 57 % (soit près des deux tiers en sus)

Fragmentation des index :

ObjectName  Extents  ExtentSwitches  AverageFreeBytes  AveragePageDensity  ScanDensity  BestCount  ActualCount 
----------- -------- --------------- ----------------- ------------------- ------------ ---------- -----------  
T_GUID1     78       597             2586,82           68,04               12,54        75         598 
T_ID1       44       43              60,285            99,26               95,45        42         44

La fragmentation d'extension est catastrophique pour la table avec GUID. Il faut 597 lectures de pages pour lire la table qui n'est composée que de 78 pages du fait des lectures en zig-zag. Soit un taux de fragmentation de 675 % (1 - (597/(78-1)))... En comparaison pour cette même fragmentation la table composée d'un auto incrément est à (1 - (43/(44-1)) = 0. Bien entendu la densité du scan reflète cet état (12,5 contre 95,5, soit un écart de plus de 7 fois.
Notez aussi les autres fragmentations : il y a 2587 octets vide en moyenne dans la table avec GUID contre 60 pour la table en auto incrément soit 43 fois plus...

En augmentant le nombre de ligne de ce test ici fixé à 50000 ce qui est relativement faible, on pourra constater que les différences augmentent encore et les écarts se creusent de plus belle !

Il n'y a donc aucune raison valable d'utiliser le GUID comme clef des tables d'une base de données...

-------- 
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *