Discussion :

[samirCA007]

bonjour a tous

Question sur la Performance

j'ai un index cluster sur un colonne de Type varchar(500)

si je procède a le faire diminuer vers varchar(5) peut on avoir un effet positif sur la Performance ou c'est pareil

merci pour vos conseils

[Michel.Priori]

Bonjour,

Les échanges entre le disque et la mémoire (dans les 2 sens) se fait par page de 8Ko.
Plus une ligne est petite, plus il en rentre dans une page, moins on brasse des pages, plus c'est rapide.
Ça ne vaut pas que pour la clé mais pour toute la ligne !

Ce qui est étonnant votre proposition de passer de varchar(500) à varchar(5) n'est pas tant l'écart de l'indice (quoique !) mais le non choix de char(5)
Lire : https://blog.developpez.com/sqlpro/p...uel_difference

Le fait que ce soit un index cluster a aussi son importance.
Les index non cluster "pointent" la ligne par une @physique si la table est un "heap" ou par une valeur logique si la table est cluster.
Et la valeur logique c'est l'ensemble des colonnes de l'index cluster.
Donc l'économie va se répercuter sur les autres index de la table.
Note : le jour du changement faudra prévoir des délais car cela signifie aussi un bouleversement au niveau feuille de tous les index non cluster...

Pour finir, on opte en général pour une clé non signifiante (donc pas un char non plus).
L'avantage est :
-la pérennité du modèle : si la clé ne porte pas de signification alors on n'a pas de raison de faire un update de la PK et ça passera les différentes organisations métier (ex : le code barre). Le prix à payer est l'ajout d'une colonne clé candidate. C'est relativement couteux pour les tables avec très peu de colonnes.
-un gain de performance du au type numérique (int = 4 octets), et ce d'autant plus lors des jointures : évaluer un tableau de correspondance entre 2 colonnes est d'autant plus rapide que les colonnes sont "étroites". Imaginez la taille nécessaire pour évaluer la jointure entre la table FACTURE et FACTURE_DETAIL pour une enseigne de grande surface...

[SQLpro]

De manière interne un VARCHAR est stocké avec la longueur réelle de l'information + 2 octets. Ainsi dans une VARCHAR(500) si vous stockez 'toto' cela occupera 6 octets.
CEPENDANT… dans certaines opérations (DISTINC, GROUP BY, ORDER BY…) les algorithmes utilisés nécessitent un alignement des valeurs. Le moteur SQL prend par défaut 50% de la longueur maximale… Dans votre cas 250 octets !
Si toutes vos lignes comportent par exemple un code postal de 5 caractères au maximum dans un VARCHAR(500) et que vous avez 1 million d'adresses, certaines opérations consommeront 400 Mo inutilement…. Cela impliquera que d'autres données en mémoire devront être éjectées du cache pour satisfaire l'opération…

Peu d'impact sur le stockage, mais impact non négligeable sur les performances !

A +

[Michel.Priori]

Bonsoir,

@SQLpro Merci de ces précisions sur le moteur.

Peut-on avoir quelques précision sur :

dans certaines opérations (DISTINC, GROUP BY, ORDER BY…) les algorithmes utilisés nécessitent un alignement des valeurs. Le moteur SQL prend par défaut 50% de la longueur maximale…

J'imagine que l'algo est un poil plus complexe que ça car prendre la moitié d'un NVARCHAR(1) (ne pas rire, je croise cette ignominie trop fréquemment, pourquoi n'est-ce pas interdit par le moteur ???) ça devient complexe.
De même que ce passe t'il si on a un varchar(500) et que 1 ligne dépasse les 250 caractères ? Réallocation mémoire ? pour chaque exception ?
Si c'est le cas alors le "par défaut" devient "à minima".

Comment peut-on faire la "démo" de ce comportement ?

[SQLpro]

J'avais trouvé le moyen de voir cette emprunte mémoire pour un de mes clients lors d'un audit…. Mais je ne me souviens plus ce que j'ai utilisé comme outil…. Je pense qu'il y a une DMV qui peut montrer cela….

A +