Bonnes pratiques SQLServer [Résolu]

[Ptchat33]

Bonjour,
Je débute en SQLServer et j’ai quelques interrogations sur les « bonnes pratiques » dont je ne trouve pas vraiment de réponse sur le net. Il ne s’agit pas vraiment de problème technique mais plus de retour d’expérience pour ce qui est bien et pas bien :

- Les jointures entre toutes mes tables se font sur des varchar dont la taille varie entre 20 et 250 caractères. Y-a-t-il un réel intérêt de performance à l’ajout de clés générées automatiquement ? Y-a-t-il des contre indication ou problème lié à la génération de clés automatiques.

- Apparemment la taille maximum d’un nom de colonne est de 30 caractères, du coup avoir des noms de colonne de 20 caractères pour des raisons de compréhension, semble t’il idiot ou au contraire utile. Y a-t-il des contre-indications à cela ? De même sur les noms de colonne de 1 ou 2 caractères, cela peut-il poser des problèmes ?

- Utilisation de varchar(n) ou Text, à partir de quelle valeur de n est-il judicieux d’utiliser un Text plutôt qu’un varchar, si celui-ci ne sert que pour un select. Y-a-t-il mieux que varchar ou text ?

Merci de vos réponses.

[elsuket]

Bonjour,

Les jointures entre toutes mes tables se font sur des varchar dont la taille varie entre 20 et 250 caractères.

Effectuer des jointures sur des chaînes de caractère, de quelque longueur qu'elles soient, est mauvais en termes de performances pour plusieurs raisons :

- D'abord, si la base de données n'a pas une collation sensible à la casse et aux accents, imaginez la quantité de travail que le CPU et le moteur de base de données doivent abattre pour comparer les clés ...
- En termes d'indexation c'est très très mauvais, puisque comme vous pouvez stocker n'importe quelle chaîne (ou presque) dans une table transactionnelle qui référencerait une table maître, alors l'index qui est sur cette table transactionnelle subira de nombreuses "scissions" de page dans l'index, ce qui va ralentir l'exécution de la requête

Y-a-t-il un réel intérêt de performance à l’ajout de clés générées automatiquement ?

Oui puisque dans ce cas non-seulement vous avez un index plus petit :

- en int vous consommez 4 octets
- en bigint vous consommez 8 octets (conseillé si votre serveur est en 64 bits)

C'est à dire bien moins que 20 caractères (1 octet par caractère en varchar et deux en nvarchar, plus 2 octets qui indiquent la fin de chaîne).

En plus de cela les processeur est bien plus à l'aide avec des entiers qu'avec des chaînes

Y-a-t-il des contre indication ou problème lié à la génération de clés automatiques.

Je n'en connais pas ... peut-être en réplication ...

Apparemment la taille maximum d’un nom de colonne est de 30 caractères, du coup avoir des noms de colonne de 20 caractères pour des raisons de compréhension, semble t’il idiot ou au contraire utile. Y a-t-il des contre-indications à cela ? De même sur les noms de colonne de 1 ou 2 caractères, cela peut-il poser des problèmes ?

Non, la taille maximum d'un nom de colonne est de 128 caractères.

Pour les noms de colonnes, comme pour les noms d'objets, on proscrit tous les caractères diacritiques (style dollar, accents, ...) et ils ne peuvent pas commencer par un chiffre.

Aucun problème pour des noms de colonne à 1 ou deux caractères , on obtient le même comportement qu'avec 128 caractères.
Peut-être au niveau de la taille des plans de requête, mais bon là c'est chercher les poils en train de pousser sur les œufs ...

- Utilisation de varchar(n) ou Text, à partir de quelle valeur de n est-il judicieux d’utiliser un Text plutôt qu’un varchar, si celui-ci ne sert que pour un select. Y-a-t-il mieux que varchar ou text ?

Le type text est déprécié et remplacé depuis SQL Server 2005 par varchar(max), qui est bien plus manipulable
Pas de valeur explicite, mais à respecter, mais je dirai qu'au delà de 200 caractères, ce n'est peut-être pas un mauvais choix.

Rappelez vous qu'une page peut contenir jusqu'à 8060 octets de données utilisateur.
Quand vous utilisez varchar(max), la chaîne est stockée dans des pages spécifiques à cet usage, en dehors des pages de la table.
La page contient un pointeur vers les pages qui stockent la longue chaîne.

@++

[7gyY9w1ZY6ySRgPeaefZ]

Envoyé par Ptchat33

- Apparemment la taille maximum d’un nom de colonne est de 30 caractères

C'est oracle, la limitation à 30 caractères (ce qui fort frustrant...)
MS Sql Server est bien plus conciliant sur le coup.

[zinzineti]

Envoyé par Ptchat33

- Les jointures entre toutes mes tables se font sur des varchar dont la taille varie entre 20 et 250 caractères. Y-a-t-il un réel intérêt de performance à l’ajout de clés générées automatiquement ? Y-a-t-il des contre indication ou problème lié à la génération de clés automatiques.

Les jointures avec les colonnes Clé primaire (PK)/Clé étrangère (FK) c'est mieux. Généralement des index sont posés sur ces colonnes ce qui permet un gain de performances de requêtes...

- Apparemment la taille maximum d’un nom de colonne est de 30 caractères, du coup avoir des noms de colonne de 20 caractères pour des raisons de compréhension, semble t’il idiot ou au contraire utile. Y a-t-il des contre-indications à cela ? De même sur les noms de colonne de 1 ou 2 caractères, cela peut-il poser des problèmes ?

Je ne comprends pas bien la question. Je suppose que tu veux savoir comment sizé les colonnes des tables afin d'éviter de gaspiller d'espace disque .. Je te propose de lire ceci

- Utilisation de varchar(n) ou Text, à partir de quelle valeur de n est-il judicieux d’utiliser un Text plutôt qu’un varchar, si celui-ci ne sert que pour un select. Y-a-t-il mieux que varchar ou text ?

- le type text est déprécié
tu peux utiliser à la place du varchar(max)

[SQLpro]

Envoyé par Ptchat33

- Les jointures entre toutes mes tables se font sur des varchar dont la taille varie entre 20 et 250 caractères.

C'est ce qu'il y a de pire. Si l'on compare un VARCHAR ayant une moyenne de 16 caractères cela fait 18 octets à stocker (16 caractères + la longueur réelle de la données exprimée sur 2 octets). En comparaison un INT auto incrémenté fait 4 octets. Votre solution est donc 5 fois plus consommatrice. or comme une clef sert systématiquement à retrouver toutes lignes, toutes vos opérations sur vos tables seront au moins 5 fois plus longues car il faut passer 5 fois dans le processeur pour lire une valeur de clef (le processeur faisant 32 bits soit 4 octets).
De plus sur 256 caractères que permet le VARCHAR, la plupart sont inexploitable. En dehors des 26 lettres et des 10 chiffres les autres caractères sont soit difficile à manipuler (accents par exemple) soit incompréhensible pour un utilisateur (sensibilité ou non à la casse) soit carrément impossible à taper (la sonnette par exemple, code 7), donc vous ne pouvez exprimer grosso modo que 36 symboles.... quel gâchis !
En revanche avec un entier de 4 octets vous pouvez exprimer 4 milliards de valeur différentes juste en saisissant un chiffre composé de 10 symboles différents tout au plus.
En sus ajoutons que la gestion de la collation fait que le CPU passe plus de temps à faire des rapprochement sans tenir compte de la casse ou des accents (tout dépend de la collation que vous avez mis, car je suppose que vous n'avez pas pensé à utiliser une collation binaire).
Bref, vous êtres au minimum 10 fois plus lents dans les traitements.
Enfin comme la clef primaire est un index cluster, c'est elle qui est utilisée comme repère de ligne pour tous les autres index. Outre la fragmentation de vos clef du fait de l'aléatoirité des nouvelles entrées, tous vos index secondaires vont grossir du fait de la reprise de cette valeur comme repère de ligne.
Ce sont aussi vos index secondaires qui vont en pâtir....
Bref, pour pourrir les performances d'une base e données il n'y a pas mieux !

Enfin si vos index sont sémantiques, c'est encore pire....
A lire : http://sqlpro.developpez.com/cours/clefs/

Y-a-t-il un réel intérêt de performance à l’ajout de clés générées automatiquement ? Y-a-t-il des contre indication ou problème lié à la génération de clés automatiques.

Oh que oui, comme précédemment dit !
Il existe une contre indication si vous avez de très nombreuses insertions massives en parallèle. Mais là faut commencer à y aller très fort. Du genre insertion de 100 000 lignes dans la même table par des processus parallèle en moins d'une seconde.....
Je doute que cela vous arrive aujourd'hui et même pour cela il y des des techniques comme la réservation de plages de clefs...

- Apparemment la taille maximum d’un nom de colonne est de 30 caractères, du coup avoir des noms de colonne de 20 caractères pour des raisons de compréhension, semble t’il idiot ou au contraire utile. Y a-t-il des contre-indications à cela ? De même sur les noms de colonne de 1 ou 2 caractères, cela peut-il poser des problèmes ?

Je ne sais pas d'ou vous avez tiré cette référence mais elle est totalement fausse. La norme SQL prévoir des noms d'objets ayant 128 caractères. SQL Server respecte parfaitement la norme sur ce point. Donc les noms des tables, colonnes, contraintes, procédures, fonctions, index, trigger... peuvent avoir 128 caractères. En sus, ils peuvent être mis dans des schémas SQL qui eut même ,peuvent avoir 128 caractères....

- Utilisation de varchar(n) ou Text, à partir de quelle valeur de n est-il judicieux d’utiliser un Text plutôt qu’un varchar, si celui-ci ne sert que pour un select. Y-a-t-il mieux que varchar ou text ?

Merci de vos réponses.

Il est TOUJOURS judicieux de prévoir le type le plus adapté. Et l'utilisation systématique du VARCHAR est une imbécilité. Pensez à utiliser aussi le CHAR !
A lire : http://blog.developpez.com/sqlpro/p9...el-difference/
Le type TEXT est d'ailleurs obsolète et les types LOB (Large Object Binary) comme VARCHAR(max), NVARCHAR(max) et VARBINARY(max) ne peuvent pas être manipulés comme des colonnes relationnelles. Par exemple il est impossible de faire un tri (ORDER BY) ou un groupage (GROUP BY) sur un LOB.

Bref, j'ai l'impression que vous avez besoin d'une sérieuse mise à niveau sur le sujet. Mon site web, comme mon livre peuvent vous y aider !

Commencez donc par lire ceci :
1) http://blog.developpez.com/sqlpro/p6...-sur-ms-sql-s/
2) http://sqlpro.developpez.com/SGBDR/ReglesCodd/

A +

[Ptchat33]

Merci pour toutes vos réponses super détaillées, c'est exactement les informations que je voulais.

Merci aussi pour les liens je vais effectivement étudier tout ça, je crois que j’en ai besoin.