Discussion :

[Bernardos]

Bonjour,
Je suis convaincu de mon coté qu'il faut systématiquement utiliser une colonne auto incrémentée comme clé primaire. je m'en convainc d'autant plus en lisant ces 2 articles.
article 1
article 2
si on regarde la requête 15 dans ce pdf,
la conclusion est

Des clefs de longue taille nécessitent de nombreuses lectures pour les CPU, dès que ces clefs dépassant la taille du mot du processeur (8 cotets dans un OS
64 bits) Or les clefs servent de recherche et de jointure pour une très grande
majorité de requêtes.
Microsoft recommande d'utiliser assez systématiquement des entiers (INT ou
BIGINT) auto incrémentés pour servir de clef.
Des clefs obèses provoque systématiquement des problèmes de performances.

Dans le cadre d'un projet, une équipe de ma boite me demande de convaincre un client "en 5 minutes par téléphone" du bien fondé de notre position.
Ce client veut "imposer" d'utiliser une clé naturelle et semble difficile à convaincre, je vais donc devoir arriver avec des certitudes bétonnées.
j'ai donc besoin de confronter mon avis avant ce "match".
Quel est votre avis sur la question?
quel pourrait être la contre argumentation du client et comment lui démontrer qu'il se trompe.
Merci d'avance,
Loïc

[Oishiiii]

Ce qui est important pour une clé primaire c'est d'être invariante.
Donc une clé "naturelle" ne doit pas être utilisée comme clé primaire.

De l’invariance des clés primaires

[elsuket]

Bonjour,

Comme on vous demande de le prouver, prenez deux tables sur lesquelles vous savez que de nombreuses jointures se font sur la clé naturelle.
Copiez leur structure (SELECT * INTO maNouvelleTable FROM maTableSource), et rajoutez ensuite toutes les contraintes, sauf celle de clé primaire, qui devrait normalement être sur la clé naturelle.
Ensuite ajoutez une contrainte d'unicité sur le tuple constituant la clé naturelle, et ajoutez ensuite une colonne de type entier avec la propriété d'auto-incrémentation, puis la clé primaire sur celle-ci.
Ensuite prenez la requête à étudier, puis faites-en une copie dans laquelle vous remplacez la jointure sur les colonnes de la clé naturelle par une jointure spécifiant les nouvelles tables, sur la colonne de type entier avec la propriété d'auto-incrémentation supporté par la contrainte de clé primaire.

Entre les deux requêtes, ajoutez un PRINT '-----'.
En première ligne de ce lot de deux requêtes, écrivez SET STATISTICS IO, TIME ON. Il vous suffira alors de comparer les résultats en empreinte IO, en temps CPU consommé, et en durée d'exécution.
En général, avec des chiffres à l'appui, ça passe mieux, sauf si votre auditoire est de mauvaise foi ... évidente dans ce cas-là

Pour aller plus loin, mais cela demande plus de travail, vous pouvez générer une charge de travail avec des outils comme Selenium, sur les deux bases de données, l'une étant une copie de la base actuelle, l'autre la base modifiée avec des clés primaire subrogées de type entier. Pendant que la charge s'exéccute, vous pouvez prendre une trace SQL Profiler côté serveur.
Ensuite on peut utiliser un outil comme ClearTrace pour standardiser les requêtes et calculer des agrégats sur plusieurs métriques. Il suffit alors de comparer les résultats.

@++

[aieeeuuuuu]

Bonjour,

quel pourrait être la contre argumentation du client et comment lui démontrer qu'il se trompe.

De mémoire, la seule argumentation que j'ai pu voir/entendre/lire est qu'il est inutile d'ajouter une colonne pour la clef primaire alors que la clef naturelle peut jouer ce rôle.
C'est somme toute un argument facile a faire tomber en premier lieu avec des exemples

1/ les concepteurs d'un logiciel utilisant l'immatriculation pour identifier un véhicule ont dû s'en mordre les doigts quand le format des immatriculations a changé.
2/ identifier une personne avec un numéro de sécu peut sembler une bonne idée de prime abord. jusqu'au jour ou il faut enregistré un nouveau né qui n'a pas de n°, ou pire, qui a celui d'un de ses parents. une clef primaire non unique, ça la fout mal !
Si en plus vous avez définit la colonne en type numérique pour gagner encore en taille de ligne... alors vous ne pourrez pas enregistrer de personnes nées en Corse...
3/ plein d'autres exemples du même type à adapter a votre situation particulière....

pour ces raisons, une clef primaire ne doit pas porter de sens pour le SI : elle doit être asémantique.

toujours sur l'idée répandue qu'ajouter une clef primaire identity est une "perte" de place inutile si une clef naturelle est disponible :
ça peut sembler vrai aussi sur une simple table. Mais la clef primaire est propagée dans les index. Or, une clef naturelle a toutes les chances d'être plus lourde qu'un simple [BIG]INT. tous les index seront donc plus lourds également. Ce qui est gagné d'un coté, est perdu de l'autre. Et il y a souvent plusieurs index sur une même table.

par ailleurs, il est courant que l'index cluster soit posé sur la clef primaire, car idéal dans la plupart des cas (et c'est ce qui est fait par défaut sous SQL Server). Si les valeurs de clef ne sont pas croissantes, alors chaque insertion sera pénalisée par une mise à jour plus couteuse de l'index cluster et il en résultera un index fragmenté.

En plus de ce qui a déjà été dit, ça devrait suffire (surtout si vous n'avez que 5 minutes )


Si vous pouvez fournir un peu plus d'informations, on pourra aussi avance d'autres arguments :

1/ est-ce que toutes les tables sont concernées
2/ quel sera la type des clefs naturelle
3/ quelle sera leur provenance fonctionnelle (si toutefois vous pouvez détailler ce point...)

N'hésitez pas à nous faire un retour sur les arguments qui seront avancés par le client.

[SQLpro]

1) citation : "les cons, ça ose tout.... C'est même à ça qu'on les reconnait" ! (Michel Audiard)

2) pourquoi c'est con...

a) parce que une clef qui est sémantique (donc "naturelle), possède une signification. Cette signification peut évoluer dans sa valeur ou dans son format.
Un exemple : immatriculation d'un véhicule... Excellente clef naturelle ? Nous sommes passé du format français au format européen... Pour toutes les personnes qui avaient des base de données avec omme clef l'immatriculation, cela a été un cauchemar car il a fallut changer le type de données de la colonne clef primaire et le type de colonne de chacune des colonnes de clef étrangère lié !!!
Une voiture peut changer d'immatriculation... Lorsqu'elle change de main ! Immaginez que vous soyez garagiste...
Vous pouvez avoir à traiter la voiture de madame durand qui l'a vendu à Monsieur dupont....

Il est donc conseillé que la clef soit donc asémantique et arbitraire (par exemple une valeur aléatoire, une valeur séquentielle, un GUID...)

b) parce que la clef doit être la plus petite possible... En effet, la clef sert pour certaines recherches, mais plus encore pour des jointures. Plus une clef sera longue ou complexe, plus couteux sera la jointure... Quel est donc l'intérêt d'utiliser un CHAR(13) (par exemple un n° de sécurité social) qui dont fait 13 octets, alors qu'un entier de type INT fait 4 octets et permet de représenter 4 milliards de lignes ? Dans le cas du code secu en CHAR(13) sa simple lecture dans un processeur 64 bits nécessite deux passes... L'une pour lire les 8 premiers caractères (8x8 = 64), l'autre pour lire la fin, soit 5 octets. Alors que l'entier INT c'est du 32 bits donc une seule passe du processeur.
En sus une données de type littérale (et le char en est une) fait de l'extra overhead du fait de la collation !
En effet il faut gérer le fait que les littéraux peuvent être sensible ou non à la casse, aux diacritique à la largeur de pas ou au kanatypes... Ce qui oblige le moteur relationnel à un traitement plus complexe.
Donc même un littéral de taille équivalente en octet est plus couteux !

A +


plus une clef est

[Bernardos]

Un grand merci à tous,
avec ça je devrais plus que m'en tirer
Petite précision : on est au début d'un projet. donc pas de bases ou de tables à tester. de plus mon entretien est téléphonique. donc pas le temps ni de moyen pour faire une démonstration.
L'équipe projet a juste pondu et proposé au client un MPD.
C'est moi même qui suis en charge d'écrire le script de création de la base qui sera déroulé par le client.
Je n'ai pas encore vu ce MPD mais j'imagine bien que j'aurai d'autres points de "discussion"

Je vous tiens au courant.
Cordialement,
Loïc

[StringBuilder]

A mon humble avis, la clé "naturelle" ne doit pas être totalement abandonnée.

Rien n'empêche de créer une clé primaire classique (entier auto-incrémenté), et de rajouter une clé alternative (simple contrainte d'unicité) sur la (ou les) clé secondaire.

Ceci permet de bénéficier des avantages de la première, tout en permettant de conserver la "souplesse" de la clé naturelle pour certains traitements.

Je pense notamment à un ERP sur lequel j'ai beaucoup travaillé.

Une cadence de livraison a pour clé (CODSOC, ACHVTE, TYPEVE, NUMEVE, NUMPOS, NUMLIG)

Ça fait mal aux yeux.

En revanche, quand on sait que CODSOC est l'entité juridique à laquelle est rattachée le BL (par exemple, la filliale bèlge), que ACHVTE indique si c'est un bon de Vente (livraison) ou Achat (Réception), que TYPEVE permet de savoir si c'est une LIVraison, une CommanDE, ou une FACture, NUMEVE est le numéro de bon de livraison, NUMPOS le numéro de la ligne dans le bon, et NUMLIG le numéro de cadencement pour ce numéro de ligne, l'avantage, c'est qu'en une seule lecture de la table des cadencements, on est capable :
- De retrouver tous les cadencements d'une société donnée
- D'un sens donné
- D'un type donné
- D'un numéro de bon donné
- D'un numéro de ligne de bon donné

Si on ne conserve pas ces champs (qui provoquent cependant de la redondance et peuvent poser problème dans les cas énumérés par Frédéric) alors pour retrouver toutes les cadences d'une entité donné, il faudra liée 6 tables en cascade là où un simple filtre sur une colonne suffit.

[elsuket]

Rien n'empêche de créer une clé primaire classique (entier auto-incrémenté), et de rajouter une clé alternative (simple contrainte d'unicité) sur la (ou les) clé secondaire.
Ceci permet de bénéficier des avantages de la première, tout en permettant de conserver la "souplesse" de la clé naturelle pour certains traitements.

Je plussoie, et je pense que je ne suis pas le seul

@++

[Bernardos]

Bon voilà c'est plié.
un grand merci à tous.
Pour vous mettre dans le contexte, je me suis retrouvé avec 3 personnes "fonctionnelles" qui ne connaissent donc pas grand chose à la technique
mais qui sont capable(ou pas) d'écrire des requêtes et de comprendre voir de créer un MPD.
Je me réjouis d'ailleurs de voir un mpd pondu juste par des fonctionnels.

L'argumentaire du client était effectivement

1. la crainte de complexifier le modèle et le requêtage,
2. la crainte d' une volumétrie plus grande.

Ma contre argumentation a été

1. La clé primaire doit être la plus petite possible(<= 8octet)
2. La clé primaire doit être asémantique et invariante (J'ai sorti notamment l'exemple de la plaque d'immatriculation)
3. Le fait d'utiliser un entier autoincrémenté comme clé est une recommandation Microsoft

j'ai du expliquer et réexpliquer plusieurs fois en utilisant les différents exemples que vous m'avez donné mais j'ai réussi à les faire pencher du bon coté.
en tout cas un grand merci à tous
Cordialement,
Loïc

[Oishiiii]

Si jamais "l'équipe projet" a besoin d'aide, elle peut poster son MCD ou son MPD dans le forum ALM/Modélisation/Schéma.
On pourra l'aider

[aieeeuuuuu]

Merci pour le retour.

la crainte de complexifier le modèle et le requêtage,

Cela peut se comprendre aussi, surtout venant de la part de fonctionnels qui font de temps à autres des requêtes SQL.
Il est logique qu'ils préfèrent retrouver des codes qui leur parle plutôt que des ID abscons.
Il suffit alors de leur proposer des vues, qui seront pour eux bien plus faciles a manipuler.

[Bernardos]

Si jamais "l'équipe projet" a besoin d'aide, elle peut poster son MCD ou son MPD dans le forum ALM/Modélisation/Schéma.
On pourra l'aider

J'y penserai dans un autre projet le cas échéant mais là c'est pas envisageable une seconde pour cause de confidentialité.
Ceci dit je me débrouille bien en la matière