Le type "guid" (uniqueidentifier) a été introduit dans la version 7/2000 de SQL Server. Etant utilisé pour les clés primaires ce type avait apporté plusieurs avantages (et inconvénients, bien sur) in implémentation de BDD. En 2002, j'ai fait un essais de performance en comparaison les différents types des données (ancienne article en russe, traduction google) qui montre la différence non-significative entre l'approche "guid" et celle "classique" des valeurs entières. En 2012 il est temps de rappeler le sujet et refaire ce test.

Environment

Infra

J'ai pris le poste de travail en x86.

CPU Intel Xeon 6 cœurs (avec HT) 2.8 GHz RAM 4 Go Windows 7 x86 SQL Server 2012 x86 Enterprise (limité par 2 Go de mémoire à utiliser)

BDD

Nous avons un schéma de 3 tables:

Documents --- 1:M ---< Lignes de documents >--- M:1 --- Produits

Il y a donc 3 tables dont les clés primaires/étrangères ont le type "int" et 3 tables dont les clés primaires/étrangères ont le type "uniqueidentifier".

Le script "00-createdb.sql" crée la BDD et ces tables.

Données

Comme nous sommes dans OLTP (transactionnel), le volume ne devrait être gros. 1 millions de document dont le nombre de lignes est 20 environ (soit 20M lignes) sera suffisant.

Le script "01-init-data.sql" fait l'initialisation des données.

Les données sont identiques sauf les valeurs de clés.

Les requêtes

J'ai retenu 3 type de requêtes :

Récupérer la clé primaire d'une ligne aléatoire de la table de documents (requêtes Q1.1 et Q2.1) Récupérer une ligne de la table de documents par la clé primaire (requêtes Q1.2 et Q2.2) Calculer le montant de ventes d'un document groupé par les produits (requêtes Q1.3 et Q2.3) Chaque requête se tourne 1000 fois, puis on calcul les valeurs moyennes des indicatrices importantes (temps, CPU, I/O).

Le batch "start-queries.cmd" (à customiser) lance le script "02-queries.sql" qui tourne les requêtes.

Résultats

Pas de nouvelles par rapport des résultat de 2002 : en termes de performance l'utilisation des guid ne dégrade pas significativement toujours. C'est évident, puisque le stockage de "guid" proche de Int128 (binary(16)) vs Int32 pour le type "int".

On peut sembler, que la différence de 10% est grande, mais il ne s'agit que de la différence entre 30 et 33 micro(!)secondes ou entre 6 et 8 millisecondes en cas de 21%.

Par contre, le stockage est plus affecté puisqu'on ordonne la table en cluster par sa clé primaire. Ensuite, tous les index non-cluster seront plus volumineuse en 2-3 fois. D autre part, l'optimisation du stockage des guids en SQL Server permet éviter l'augmentation de 4 fois (128 vs 32 bits).

Mais n'oublie pas la compression. Après avoir compressé les données des tables "doc_line2" et "doc_line1" les tailles ont devenus 430136 et 338712 Ko au lieu de 1115840 et 628464. Voyez la différence entre 20% et 90% ? :)

Également, pour les index.

Conclusions

En OLTP (transactionnel) le choix de type "guid" peut être une solution. Au point de vue d’architecture du système cette solution supprime les problèmes récurrentes tels que génération des ID des objets, conflits des clés dans les BDD en réplication/consolidation, unicité des ID dans une système distribué sans avoir le service d'identification supplémentaire (remplace les sacrés ID pseudo-composites = ID node + ID ligne).

Sachant, qu'en OLTP le temps moyen de réponse de BDD ne dépasse pas 20% du celui total, il faut étudier cette option au moins.

Par contre, en OLAP (décisionnel) nous avons les contraintes de grosses volumes des données en mode "lecture seul" et donc le choix des types numériques correspondantes (tinyint/smallint/int/bigint) est évident.

Test source codes, ZIP