Pourquoi choisir les types de données des colonnes ?
Aujourd'hui l'espace disque ne coûte plus grand chose et on peut se demander à quoi sert de s'occuper du choix des types de données dans une base SQLServer.
A ceci : on a beau avoir un coût par octet très faible, il n'en reste pas moins que les débits des disques n'ont pas progressé à la même vitesse que les autres composants, mémoire et CPU.
On y verra donc un intérêt côté performances, le canal disque étant toujours un goulot d'étranglement pour une base de données relationnelle.
Bien sûr, seront surtout concernés par ce point celles et ceux qui ont à manipuler des volumes importants , disons au delà du Giga octet.
Globalement, on peut dire que l'on est face à deux stratégies :
• Des données à taille fixe telles que les int, money, char(n)
• Des données à taille variable telles que les varchar(n)
Choix des données à taille fixe :
Une des premières règles à prendre en compte est le choix de la taille minimale en taille fixe.
En effet, il ne faut pas oublier que les lignes des tables SQLServer sont stockées dans des pages de 8Ko : plus la taille de la ligne est grande, moins il y en aura par page et donc plus d'Entrées/Sorties seront générées.
Exemple 1 :
La colonne FERIE est un indicateur qui vaut 0 ou 1 ; pour son type on pourrait choisir
• Bit
• Smallint
• Int
• Bigint
Ceci avec des conséquences sur la taille du stockage utilisé :
Bit s'il est seul de ce type occupera un octet, en effet, SQLServer utilise un octet pour stocker un paquet de 8 bits.
Des données telles que Lundi, Mardi, Mercredi, Jeudi,… auront donc tout intérêt à être stockées sous cette forme.
Smallint quand à lui occupera deux octets
Int passera à quatre octets
Bigint, enfin utilisera huit octets.
On aura donc tout intérêt à choisir le type qui permet de parcourir toutes les valeurs nécessaires en utilisant la taille de stockage la plus petite possible.
Exemple 2 :
On utilise une table de 86000 livres dont la structure est la suivante :
create
table Livre (
IDLivre int
IDENTITY
(1, 1)
NOT
null
,
Titre varchar
(200)
collate French_CI_AS NOT
null
,
IDEditeur int
null
,
IDFamille int
null
,
Theme nvarchar
(20)
collate French_CI_AS null
,
PrixAchat money
null
,
NbeEmprunts smallint
null
,
Largeur smallint
null
,
Hauteur smallint
null
,
Sorti bit
NOT
null
)
ON
primary
Choix du type de la colonne Sorti :
Il s'agit d'un indicateur binaire dont la valeur à vrai signale que le livre est sorti (de la bibliothèque)
Le test de lecture donne le nombre de pages accédées pour faire la requête
select
COUNT(*)
from LIVRE where Sorti = 0
Conclusion :
Un mauvais type de données entraine un volume de stockage plus (ou beaucoup) plus important et donc des accès beaucoup plus lents en lecture.
Ici l'écart peut paraître faible, maintenant, si nous nous mettons dans les cas où l'on a besoin de plusieurs indicateurs booléens sur une ligne de table, le gain peut être plus important
Par exemple, avec 4 indicateurs booléens
Choix des données à taille variable :
Nous arrivons ici dans le domaine des chaines de caractères, et la grande question est char ou varchar ?
Comme leur nom l'indique, les données de type varchar ont une taille variable, elles seront donc bien adaptées à des chaines dont on ne maitrise pas d'avance la taille courante mais dont on peut prévoir une taille maximum.
Par exemple, les noms des employés peuvent atteindre 60 caractères.
Exemple 1 :
Nous voulons stocker des livres qui ont en particulier un titre dont la taille est éminemment variable.
On utilise une table de 86000 livres dont la structure est la suivante :
create
table Livre (
IDLivre int
IDENTITY
(1, 1)
NOT
null
,
Titre varchar
(200)
collate French_CI_AS NOT
null
,
IDEditeur int
null
,
IDFamille int
null
,
Theme nvarchar
(20)
collate French_CI_AS null
,
PrixAchat money
null
,
NbeEmprunts smallint
null
,
Largeur smallint
null
,
Hauteur smallint
null
,
Sorti bit
NOT
null
)
ON
primary
Choix du type de la colonne titre :
Le test de lecture donne le nombre de pages accédées pour faire la requête
select
COUNT(*)
from LIVRE where TITRE IS
NOT
null
Conclusion :
Un mauvais type de données entraine un volume de stockage plus (ou beaucoup) plus important (ici 3 fois plus) et donc des accès beaucoup plus lents en lecture.
Le facteur 3 obtenu ici est donné en nombre brut de lectures, sur une machine un peu chargée, ce facteur pourra être dépassé en fonction de la place disponible dans le buffer des données à ce moment là et des accès disques faits pour les autres process.
Explication :
Ceci est dû au mode de stockage des données fixes de SQLServer (elles occupent une place constante en tête de ligne, et également au fait que les char sont complétés de blancs si nécessaire)
Impact des colonnes à indicateur NULL en varchar :
Nous allons mettre à NULL l'indicateur de toutes les colonnes THEME et mesurer l'impact de cette mise à jour sur la taille de la table.
Conclusion :
En varchar, les colonnes utilisent de la place en fonction de leur taille, celles dont l'indicateur NULL est vrai occupent le minimum de place.
Explication :
SQLServer utilise une table de bitmaps pour les indicateurs NULL : dans le cas où une colonne se voit marquée à NULL, aucune donnée supplémentaire n'est stockée dans la base.
Il n'y pas de marqueur spécial, ou de valeur spéciale pour les NULL (du style '', ou autre)
Conclusion
Je considère SQLServer comme un très bon moteur de bases de données relationnelles.
Il serait dommage de gâcher ses performances par un mauvais choix de types de données.
La puissance disponible peut être utilisée pour des fonctionnalités très intéressantes telles que les index full text, les jointures d'index, le cache des données et du code, l'optimisation intelligente des requêtes,....