Optimisation des bases de données SQL Server Troisième partie

Lorsque j’aborde les cours sur le langage SQL et que nous en sommes aux types de données, je pose souvent l’innocente question "quel intérêt y a-t-il à avoir un type de chaîne de caractères à longueur variable (VARCHAR) et un autre à longueur fixe (CHAR) ?" A ce jour, aucun de mes élèves ne m’a jamais donné la réponse attendue… Or l’intérêt, c’est le choix ! Tantôt je vais utiliser tel type pour telle raison et tantôt ce sera un autre.

 Pour bien comprendre les caractéristiques des différents types de données il faut à nouveau se plonger au coeur du fonctionnement d’un SGBDR et tenter d’imaginer comment sont stockées les données dans les pages de la base. La différence fondamentale entre un CHAR de longueur n et un VARCHAR de même longueur, c’est que les données stockées dans le premier sont complétées à droite par un caractère de remplissage (le blanc, référence 20 en code ASCII), tandis que pour le second, on stocke la longueur vraie.

Mais si la ligne de la table comporte plusieurs colonnes de longueur variable, comment savoir ou commence et ou se termine chaque élément de données ? Avec une taille fixe, c’est facile, il suffit de compter les octets et l’on sait tout de suite quel est le découpage des données composant la ligne. Avec des éléments de taille variable, il faut rajouter une information supplémentaire afin d’indiquer ou se termine chaque donnée. En l’occurrence, dans SQL Server il faut ajouter deux octets pour chaque colonne de taille variable, car ces dernières peuvent aller jusqu’à 8000 octets.

Avantage du VARCHAR, son économie de place lorsque la longueur des données à y stocker peut varier dans d’importantes proportions, par exemple comme les lignes d’une adresse. Inconvénient, la nécessité d’un calcul supplémentaire pour trouver l’emplacement de la donnée, ainsi qu’une augmentation de la longueur des lignes pour y stocker l’information du nombre de caractères. Une des caractéristiques perverse du VARCHAR est sa forte propension à fragmenter les structures de données lors des modifications de valeurs. Pour obtenir de bonnes performances et gagner de la place, les données d’une ligne sont structurées de la manière suivante : toutes les colonnes de taille fixe sont placées au début, celles de taille variable à la fin.

En effet, avec cette organisation, seules les colonnes de taille variable ont besoin d’une information supplémentaire pour la longueur. Mais que se passe t-il si une donnée de longueur n est modifiée en une données de longueur n + i ? Il n’est plus possible de stocker la donnée dans son emplacement original. C’est d’ailleurs le même phénomène qui s’est produit avec moi quand lors de mon premier été d’installation en Provence, je me suis laissé aller à la sieste, la pétanque, le pastaga3 et le rosé : à l’automne, aucun de mes pantalons n’accueillait plus ma bedaine !

Il en va de même dans SQL Server, le "pantalon" ligne ne peut plus recevoir la donnée et cette dernière est rejetée sur une autre page, provoquant des lectures en zig-zag. Seul moyen de correction : la réindexation. Seul moyen de l’éviter : adopter du CHAR! Résumons-nous : si le littéral est de longueur faible ou fortement mis à jour ou encore si la donnée est d’une taille proche du maximum, préférez une taille fixe. Dans le cas contraire : grande taille, peu de mise à jour, données de longueurs très fluctuantes, alors la taille variable vous fera économiser de la place, donc du volume, donc de la mémoire, donc plus de lignes seront traitées directement dans le cache…

Dans un genre différent, on trouve la problématique du stockage des données littérales de type UNICODE en comparaison à l’ASCII. En unicode, chaque caractère est représenté par deux octets permettant ainsi 65 536 symboles, ce qui permet de marier les alphabets latin, grec, cyrillique, hébreu, asiatiques et bien d’autres encore. En ASCII 84 bits, il n’y a bien évidemment qu’un seul octet par caractère ! D’ou une économie de coût de stockage de moitié si l’on travaille en ASCII (donc CHAR, VARCHAR) comparé à la version UNICODE (NCHAR, NVARCHAR, le N voulant dire NATIONAL).

Il y a donc une sacrée différence de performance entre 1 et 2 octets. Le double tout simplement. Or il est rare qu’une application soit à la fois internationale et internationalisée. Internationale au sens ou tout être humain quelles que soient sa nationalité, sa culture et en particulier sa langue soit en mesure de l’utiliser, et internationalisée, au sens où des utilisateurs de cultures différentes et donc de langues différentes vont devoir utiliser la même application en même temps. Les rares cas que je connaisse consiste en des sites web interactifs planétaires où l’on s’est simplifié la vie en imposant finalement l’anglais comme langue commune !