Cela fait depuis 2005 que j’interviens pour du conseil, architecture, tuning et formation sur des bases de données SQL Server. Et Il n’y a pas un mois qui passe sans que survienne le fameux débat d’experts et d’architectes sur le choix du type de colonne pour les clés primaires. On retrouve le plus souvent sur la table deux choix possibles qui sont :

• Le type GUID (UNIQUEIDENTIFIER en SQL)
• Le type auto-incrémentée (INT ou BIGINT avec l’option IDENTITY en SQL)

Alors quel type de colonne est le plus performant ?

Et bien la réponse est toute simple : Le type auto-incrémentée ! Pourquoi ? Et bien l’entier auto-incrémenté possède les avantages suivants :

• Il est monotone croissant : c’est donc le type parfait pour l’insertion dans des indexes et limiter la fragmentation !
• Tient sur 4 octets pour le INT et 8 octets pour le BIGINT : Il ne prend que peu de place dans les données, mais aussi au niveau des indexes (chose que beaucoup personnes oublient !).
• Permet d’avoir 2^32 ou 2^64 valeurs possibles : Ce qui est largement suffisant car cela nous fait un peu moins de 18 x 10^18 lignes en base (18 avec 18 zéros) ! Au delà je pense que vous serez déjà le client n°1 de SQL Azure !
• Facilement lisible et débuggable : La personne n°55152 et associé au toutou n°132948.

Mais alors quels sont les inconvénients avec le type GUID ?

• Les valeurs sont aléatoires et très réparties : c’est donc le type idéal pour fragmenter ses indexes !
• Tient sur 16 octets : La place disque ne coûte pas cher de nos jours lorsqu’on habite à côté de la rue Montgallet, en revanche croyez moi sur parole que vous payerez votre place avec SQL Server !
• Difficilement lisible et débuggable : La personne n°923A38C9-A27F-4A10-966E-D6DAFCF646FD est associé au toutou n°7DD21EF9-9108-4F2A-89ED-D1AF72472E63 ???
• Le calcul est long : La génération d’un GUID, contrairement à une incrémentation qui se résume à x + 1, est coûteuse en temps !

De plus, l’opération la plus coûteuse de manière générale dans les SGBD est la jointure. Avec les GUID, vous mettez 2 ou 4 fois plus de temps pour comparer 2 valeurs qu’un simple BIGINT ou INT ! C’est donc non négligeable…

Pour vous convaincre, je vais faire 3 petites démo qui permettront de clouer le bec des « architectures » et « experts » du dimanche (vous savez ceux qui justifient leur gros salaire d’expert mais qui ne connaissent réellement rien aux bases de données et qui se charge de la développer…) :

• La première démonstration comparera le temps d’insertion en base pour 20 000 lignes (20 000 personnes).
• La deuxième démonstration comparera les performances de requêtage d’une table avec une simple jointure (entre des personnes et des toutous).
• La troisième démonstration vous montrer l’état des indexes (et surtout l’étendu des dégats).

Construction de la base de données pour les tests

-- Créer la base de données
USE master
CREATE DATABASE GuidVsIdentity
GO

-- Créer les tables Personne et Toutou avec l'utilisation des GUID
USE GuidVsIdentity
GO
CREATE TABLE PersonneAvecGuid
(
	Id UNIQUEIDENTIFIER NOT NULL,
	Data VARBINARY(150)	NOT NULL,
	CONSTRAINT PK_PersonneAvecGuid PRIMARY KEY (Id)
)

CREATE TABLE ToutouAvecGuid
(
	Id UNIQUEIDENTIFIER NOT NULL,
	PersonneId UNIQUEIDENTIFIER NOT NULL,
	Data VARBINARY(150)	NOT NULL,

	CONSTRAINT PK_ToutouAvecGuid PRIMARY KEY (Id),

	CONSTRAINT FK_ToutouAvecGuid_PersonneAvecGuid FOREIGN KEY (PersonneId)
		REFERENCES PersonneAvecGuid (Id)
)

-- Créer les tables Personne et Toutou avec l'utilisation des IDENTITY
CREATE TABLE PersonneAvecIdentity
(
	Id INT IDENTITY(1,1),
	Data VARBINARY(150)	NOT NULL,
	CONSTRAINT PK_PersonneAvecIdentity PRIMARY KEY (Id)
)

CREATE TABLE ToutouAvecIdentity
(
	Id INT IDENTITY(1,1),
	PersonneId INT				NOT NULL,
	Data VARBINARY(150)			NOT NULL,

	CONSTRAINT PK_ToutouAvecIdentity PRIMARY KEY (Id),

	CONSTRAINT FK_ToutouAvecIdentity_PersonneAvecIdentity FOREIGN KEY (PersonneId)
		REFERENCES PersonneAvecIdentity (Id)
)

GO

-- Créer les indexes sur les colonnes des clés étrangères PersonneId
CREATE INDEX IDX_ToutouAvecGuid_PersonneId ON ToutouAvecGuid (PersonneId)
CREATE INDEX IDX_ToutouAvecIdentity_PersonneId ON ToutouAvecIdentity (PersonneId)

Le script précédent créer 4 tables :

• PersonneAvecGuid et ToutouAvecGuid qui sont 2 tables jointes avec une clé étrangère via des UNIQUEIDENTIFIER (Guid).
• PersonneAvecIdentity et ToutouAvecIdentity qui sont elles aussi 2 tables jointes avec une clé étrangère mais cette fois-ci via une colonne auto-incrémentée (INT IDENTITY).

1ère démonstration : Comparaison des performances d’insertion de 20 000 lignes (20 000 personnes dans notre exemple).

------------------------------------------------------------------
-- Génération des données
------------------------------------------------------------------
DECLARE @nbPersonneMax INT = 20000

DECLARE @date1 DATETIME2
DECLARE @date2 DATETIME2

DECLARE @nbPersonne INT = 0
DECLARE @data VARBINARY(200) = 0x01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789

-- Insertion de @@nbPersonneMax lignes dans la table 'PersonneAvecGuid'
SET @date1 = CURRENT_TIMESTAMP;
WHILE @nbPersonne < @nbPersonneMax
BEGIN
	INSERT INTO PersonneAvecGuid (Id, Data) VALUES (NEWID(), @data)

	SET @nbPersonne = @nbPersonne + 1;
END

CHECKPOINT
SET @date2 = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond, @date1, @date2) AS InsertTimeGuid

-- Insertion de @@nbPersonneMax lignes dans la table 'PersonneAvecIdentity'
SET @nbPersonne = 0

SET @date1 = CURRENT_TIMESTAMP;
WHILE @nbPersonne < @nbPersonneMax
BEGIN
	INSERT INTO PersonneAvecIdentity (Data) VALUES (@data)

	SET @nbPersonne = @nbPersonne + 1;
END

CHECKPOINT

SET @date2 = CURRENT_TIMESTAMP AS InsertTimeIdentity;

SELECT DATEDIFF(millisecond, @date1, @date2)

J’ai volontairement mis sur chacune des tables une colonne binaire de 200 octets de données afin de remplir et simuler du contenu…
Afin de comparer ce qui doit être comparable, les 2 familles de tables (Guid et Identity) disposent exactement des mêmes données (à part les valeurs des clés primaires bien évidemment !).
On mesure le temps d’exécution des 20 000 insertions en faisant la différence entre 2 dates (via la fonction CURRENT_TIMESTAMP).

Résultats des courses :

5 863 millisecondes pour insérer 20 000 personnes dans la table contenant une clé primaire de type UNIQUEIDENTIFIER (Guid) et 2 890 millisecondes pour insérer 20 000 personnes dans la table contenant une clé primaire de type auto-incrémentée. L’insertion avec les colonnes de type UNIQUEIDENTIFIER (Guid) prend donc 2 fois plus de temps qu’avec les colonnes auto-incrémentés !

2ème démonstration : Requêtage des tables avec une jointure

-- Insertion en masse dans les tables des Toutous
GO
INSERT INTO ToutouAvecGuid (Id, PersonneId, Data)
SELECT NEWID(), Id, Data FROM PersonneAvecGuid

INSERT INTO ToutouAvecIdentity(PersonneId, Data)
SELECT Id, Data FROM PersonneAvecIdentity
GO

-- Execution d'une requête contenant une jointure entre la table Personne et la table Toutou (via la colonne PersonneId).
GO
SET STATISTICS IO ON;
GO
SELECT *
FROM PersonneAvecGuid INNER JOIN ToutouAvecGuid
	ON (PersonneAvecGuid.Id = ToutouAvecGuid.PersonneId)

SELECT *
FROM PersonneAvecIdentity INNER JOIN ToutouAvecIdentity
	ON (PersonneAvecIdentity.Id = ToutouAvecIdentity.PersonneId)

GO

Le script précédent insert des toutous dans les tables ToutouAvecGuid et ToutAvecIdentity qui sont liés avec les personnes présentent dans les tables PersonneAvecGuid et PersonneAvecIdentity respectiviement.
2 requêtes sont alors faites pour récupérer les personnes et les toutous associés via une jointure et on récupère via l’instruction SET STATISTICS IO ON les informations sur le nombre de pages lues pour chacune des requêtes.

Résultats :

(20000 ligne(s) affectée(s))
Table 'ToutouAvecGuid'. Nombre d'analyses 1, lectures logiques 368, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'PersonneAvecGuid'. Nombre d'analyses 1, lectures logiques 466, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

(20000 ligne(s) affectée(s))
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'ToutouAvecIdentity'. Nombre d'analyses 1, lectures logiques 306, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'PersonneAvecIdentity'. Nombre d'analyses 1, lectures logiques 292, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

On remarque que pour les toutous il y a 368 pages lues pour les UNIQUEIDENTIFIER contre 306 avec les colonnes auto-incrémentées (soit un coût supplémentaire d’environ de 17 % !). C’est à dire qu’il faudra charger sur disque 17 % de page en plus, que SQL Server devra consommer 17 % d’espace mémoire en plus pour mettre les pages en cache, empêchant la mise en cache d’autres pages pour d’autres requêtes, et ainsi de suite… Ce qui engendra par effet de spirale une baisse des performances de votre serveur de données !

3ème démonstration : Etat des indexes

SELECT
	Indexes.name AS [Name],
	StatIndexes.avg_fragmentation_in_percent AS [Avg_Fragmentation_In_Percent],
	StatIndexes.page_count AS [PageCount]
FROM
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS StatIndexes
INNER JOIN
	sys.indexes AS Indexes ON (StatIndexes.OBJECT_ID = Indexes.OBJECT_ID AND StatIndexes.index_id = Indexes.index_id)
WHERE
	StatIndexes.database_id = DB_ID() AND Indexes.name LIKE 'PK_%'
ORDER BY
	Indexes.name

Dans la requête précédente, on récupère les informations sur la fragmentation moyenne des indexes des clés primaires ainsi que le nombre de pages utilisées sur disques pour les stocker. Voici les résultats :

Ici il n’y a pas besoin de calculer des ratios, on remarque que l’index de la clé primaire de la table PersonneAvecGuid est fragmenté à plus de 98% alors qu’elle est de 1,3 % pour la table PersonneAvecIdentity !!! Les clés primaires de type UNIQUEIDENTIFIER sont donc pourris au niveau fragmentation (c’est le terme à utiliser !). La performance au niveau de la consultation des indexes (et donc de la recherche) sera fortement réduite ! Vous pouvez aussi constater que l’index nécessite environ 40 % d’espace disque (page) supplémentaire ! Ce qui par effet de spirale aura des conséquences néfastes sur les performances de votre base de données.

Conclusion

A travers ce petit article j’ai voulu vous montrer les différences entre le type UNIQUEIDENTIFIER (Guid) et les colonnes INT ou BIGINT auto incrémentée (IDENTITY) et vous prouver que l’utilisation des colonnes de type UNIQUEIDENTIFIER est catastrophique et à proscrire et à ne pas utiliser !!!

Encore une fois, j’insiste sur le fait que la conception d’une base de données est un métier ! Et quelle doit être fait par une personne qui maîtrise les bases de données (par exemple un DBA)… Si ne connaissez pas bien les bases de données, laissez donc faire les professionnels des bases de données et concentrez vous sur vos belles applications Windows Store ou alors formez-vous via une formation de qualité ou alors changez de métier !!

Je tiens à signaler aussi au passage que j’interviens souvent sur des missions de tuning (disons « pompiers ») sur des problèmes de performances dans de grosses tables où il y a des clés primaires de type UNIQUEIDENTIFIER, et d’expérience il suffit juste de changer la colonne de type UNIQUEIDENTIFIER en INT pour avoir des temps de requêtes et de mise à jour divisés par 10 voir plus ! Et le tout par effet de spirale, augmenter les performances de votre serveur de base de données !

Note concernant la fusion des bases de données

J’entends souvent dire de la part des experts et architectes qui savent tout sur tout : « Moi j’utilise le type UNIQUEIDENTIFIER (Guid) afin de pouvoir faire un mécanisme de réplication/fusion quand je suis dans un environnement distribué ». Le plus souvent, les personnes qui disent ce genre de propos sont les personnes qui bataillent dure pour acheter une version de SQL Server Standard ou Enterprise et qui ne savent pas le rentabiliser ! En effet, ces 2 éditions de SQL Server vous permettent de faire de la réplication entre base de données ! C’est inclus dedans, performant, simple à mettre en oeuvre et s’utilise avec SQL Server Express pour les SGBD abonnés… Et c’est lui qui fait tout ! Et disponible depuis la version 2005 de SQL Server… Pour + de 5000 € la licence Standard, pourquoi s’en priver au lieu de réinventer la roue !