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…
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 * * * * *
Bonjour,
Je relance ce thread fort intéressant, car j’envisage une réécriture de mon SI. Et je me pose des questions…
Contexte :
Par exemple, prenons un règlement et ses imputations.
Pour une donnée, un imputation concerne une facture.
On a donc un champ imputation.FK_facture où on stocke la clé
Pour une autre donnée, une imputation concerne un dépôt de garantie.
On a donc un champ imputation.FK_depotDeGarantie
A mesure qu’on enrichit le système, on peut finir par avoir assez de clés étrangères (mais une seule est non NULL à la fois) pour justifier :
– cas 1 : soit un champ générique FK qui stockerait un long et un champ table qui identifierait la table d’origine (facture, dépôt, etc)
– cas 2 : soit un seul champ FK et dans ce cas, la clé primaire dans la table d’origine serait toujours un UUID
Ce que j’en pense et si je ne me trompe :
Dans le cas 1, il est possible de faire un INNER JOIN avec la clé étrangère = la clé primaire ET la table réf. = ma réf. souhaitée
Dans le cas 2, on ne soucie de rien, et on dit INNER JOIN sur clé sans rien de plus
Mais, in fine, quel est le meilleur choix ? Ce cas là justifie t’il l’emploi d’un UUID ?
(De tendance plutôt économe, je dirais non pour toutes les raisons évoquées.)
Merci d’avance pour votre avis !
Vous ne maîtrisez visiblement pas le modélisation des bases de données et commettez l’erreur gravissime de vouloir créer des tables sans étudier préalablement un modèle conceptuel à base d’entité et d’association.
Si vous vous donniez la peine de faire correctement votre travail, c’est à dire modéliser au niveau conceptuel, alors la solution vous sauterais aux yeux. Il suffit d’utiliser dans le MCD (Modèle Conceptuel de Données) la notion d’héritage et le tour est joué.
Cette technique n’est pas nouvelle (au moins 30 ans !!! et les outils de conception comme Power AMC sont capable de la mettre en Å“uvre sans aucune difficulté.
Lisez l’article que j’ai écrit à ce sujet :
Pour apprendre à modéliser une base de données, l’ouvrage de mon confrère Christian Soutou, annoté par moi même vous serait d’un grand secours :
Bonjour,
Merci pour votre réponse et pour le lien. Je travaille dans un EDI qui, s’il implémente le SQL, ne l’implémente pas complètement. Il manque le partionnement de table, et l’héritage par exemple. Donc, effectivement, un rappel sur l’héritage m’est très utile. Je vous rassure toutefois : je suis un adepte du DRY pour le code et de la 3F pour le mcd. Maintenant que ceci est dit, et par rapport au lien sur l’héritage, je peux dire qu’on ne s’amuse pas à redonder des infos partout. On a une table d’annuaire par exemple. Par contre, j’ai réécrit notre système actuel en partenariat et certaines parties m’ont été imposées. C’est le cas pour l’exemple que je cite (facture, dépôts de garantie, imputations, règlements, etc).
J’ai d’ailleurs à ce titre, proposé une seule table qui s’appellerait par exemple « FLUX » et qui serait typé : facture, trop perçu, dépôt de garantie, etc.
Maintenant, la problématique que j’expose se pose tout de même dans un autre cas : exemple avec une table GED_DOCUMENT. On est sur une table fille dans laquelle une clé étrangère pourrait potentiellement stocker une clé de n’importe quelle table du système. Puisqu’on ne va pas créer une table de document pour chaque table pour laquelle le besoin existerait. Et dans ce cas se repose la question de l’utilisation d’un uuid ou de deux champs… J’ai été voir du côté d’une clé composite, mais si cela serait complètement justifié dans le cadre de la table GED_DOCUMENT où on aurait une clé composée des deux colonnes tableNumber et FK_table, étant donné que je ne peux (à ma connaissance), pas déclarer une clé primaire composite avec une constante pour la table père et que je ne vais certainement pas ajouter une colonne avec une constante pour pouvoir le faire quand même, la question uuid ou non se repose. Toutefois, je pense que l’argument principal qui est pour moi, toujours la performance, me fera rayer de mon choix possible les jointures par uuid. Après, si dans ce cas, il y a mieux, je suis preneur !
Phare motard nimois… Laurent PIERRE
Bien évidemment l’utilisation du GUID est une excellente chose dans le monde objet, car cela permet de référencer un objet en mémoire dans un univers non indexé ni ordonné. Par exemple les mécanismes de réplications de données entre serveurs utilisent fréquemment un GUID pour identifier à coup sur une ligne dans des systèmes répartis. Imaginez trois serveurs qui se répliquent mutuellement… Il y aura bine un jour on l’on rentrera sur au moins deux bases un client Paul Dupont avec la clef 3254741… Comment savoir si c’est le même répliqué ou deux paul dupont différents ??? Grâce au GUID…
Autrement dit, le GUID a son utilité, mais il faut éviter que ce soit la clef primaire de la table !
A +
Bonjour Frédéric et merci pour cet éclairage très intéressant.
Le point de vu que vous apportez est lié coté serveur SQL, et très souvent, le coté programmeur dont je suis, ne regarde pas assez ou en tout cas pas souvent, cette perspective là . Merci pour cette piqure de rappel.
L’avantage que nous avons (nous programmeur) à utiliser les GUIDs est, que depuis l’interface utilisateur, nous n’avons pas à solliciter le système SQL pour obtenir la sacrosainte clé primaire. Lorsque dans une application tout à fait simple, de gestion de bon de commandes par exemple, l’utilisateur entre au fur et à mesure le détails des lignes de commandes (donc les articles) pour une nouvelle commande, il est bien plus simple de faire appel aux GUIDs à ce moment là , pour gérer les créations d’enregistrements plutôt que d’utiliser les triggers.
La question que je me pose est, quel pourrait être le compromis si on avait à prendre en compte ces 2 visions en sachant pertinemment que les miracles n’existent pas ?
A suivre…
Vu le nombre de lignes que certaines tables peuvent avoir, et la fréquence avec laquelle peuvent se faire les mise à jour, on risque avec le TIMESTAMP du télescopage, ce qu’évite le GUID en principe.
D’ailleurs la réplication de fusion MS SQL Server (seule à être par nature « full duplex ») utilise le GUID comme tag de version de ligne. Mais il ne s’agit en aucun cas d’une clef, mais d’une information complémentaire !
A +
Bonjour,
Je me posais la même question que sisqo60.
J’envisageais d’utiliser les Guids pour du partage de données entres bases.
L’utilisation du timestamp serait-il une solution « acceptable » en terme de perf et d’unicité?
Bonjour,
J’avais une précision à demander quand à la réplication. Les Guids ont ils leur place quand on travaille avec de la réplication sql server ou même d’autres technologies qui permettent de synchroniser des données?