Conseil clé primaire pour les meilleurs performances ?

[ites]

Salut,

Je désirerais avoir un conseil sur le choix de clé primaire.
Par ex:
Il préférable d'avoir
1. une clé primaire naturelle
table CLIENT (
code_cli (alphanumérique) clé primaire
libellé_cli
...
)
ou
2. une clé primaire auto et clé primaire qui ne sera pas employé (code_cli)
table CLIENT (
id_cli (numérique) clé primaire
code_cli (alphanumérique)
libellé_cli
...
)

Est ce que point de vue vitesse cela change ou autre performance ?
Faites moi part de vos avis car j'ai lu plusieurs documents et ce n'est pas trop clair.

Merci
Ites

[Swoög]

il est toujours plus rapide de mettre une clée primaire sur un champ numérique qu'un champ alpha numérique : rapidité de comparaison, de tri, etc...

[yizashi]

Dans le second cas, tu dis que code_cli n'est pas utilisé alors pourquoi ne pas le supprimer et laisser uniquement la PK auto-incrémentée?

Il faut le mettre s'il t'arrive de l'afficher ou de l'imprimer dans ton application et dans ce cas-ci il est employé.

Enfin bref, moi je crois que le plus simple c'est de mettre systématiquement une clé primaire dans les tables, à part peut-être dans celles qui ne servent que de liaison entre 2 tables (tables associatives).

[Luc Orient]

Pour moi c'est plus un choix de conception qu'un strict point de vue de performance ...

2. une clé primaire auto et clé primaire qui ne sera pas employé (code_cli)
table CLIENT (
id_cli (numérique) clé primaire
code_cli (alphanumérique)
libellé_cli...

Attention par définition une table n'a qu'une clé primaire ...

Si on a un identifiant naturel, il a de fortes chances d'être utilisé dans des recherches (alors que l'exemple semble curieusement dire le contraire ...) et donc d'être présent dans un index secondaire. Avec l'utilisation d'un identifiant automatique on risque de se retrouver avec deux index, ce qui n'est pas forcément top pour les performances en insertion ...

[ites]

Merci pour toutes vos réponses

Ites

[Monstros Velu]

Je te conseille de lire cet article de SQLPro : http://sql.developpez.com/clefs/

Les 2 premiers paragraphes expliquent tout d'abbord quelles sont les qualités d'une clef, et pourquoi il faut éviter les clefs naturelles, et donc répondra à ta question. Ce qui n'empêche pas de lire la suite de l'article qui est fort interressant ;o)

[SQLpro]

Pour compléter mon propre papier, toute clef autre qu'un entier de la longueur du mot du processeur est par nature moins performante.
Par exemple un CHAR(32) sera à peu près 16 fois moins performant qu'un entier...
Quant au VARCHAR(32) si tout va bien il peut être 16 à 16 * n moins performant (n étant le nombre de ligne de la table). Donc dans le pire des cas dans une table de 1000 lignes, il pourra être 16000 fois moins performant qu'un entier...

C'est pas rien !

A +

[Arvulis]

Envoyé par Monstros Velu

Je te conseille de lire cet article de SQLPro : http://sql.developpez.com/clefs/

Les 2 premiers paragraphes expliquent tout d'abbord quelles sont les qualités d'une clef, et pourquoi il faut éviter les clefs naturelles, et donc répondra à ta question. Ce qui n'empêche pas de lire la suite de l'article qui est fort interressant ;o)

Bon article !

D'ailleurs SQLPRO, tu peux rajouter le concepts des Séquences sous Oracle

[SQLpro]

D'ailleurs SQLPRO, tu peux rajouter le concepts des Séquences sous Oracle

je l'évoque en point 4 mais j'ai choisit de montrer celui d'Interbase (très proche).

Sachez que la norme SQL:2003 à standardisé l'usage à la fois du IDENTITY et de l'objet SEQUENCE.

A +

[Luc Orient]

Si on choisit on clé primaire "non naturelle" (une clé numérique par exemple) comme clé primaire faudra-t-il lui associer un index (au sens CREATE INDEX) pour en assurer l'unicité ?

[Arvulis]

Envoyé par SQLpro

je l'évoque en point 4 mais j'ai choisit de montrer celui d'Interbase (très proche).

Sachez que la norme SQL:2003 à standardisé l'usage à la fois du IDENTITY et de l'objet SEQUENCE.

A +

Oki merci, je ne savais pas, pour la normalisation

Ce tuto est du bon boulot en tout cas.

[ites]

Envoyé par Monstros Velu

Je te conseille de lire cet article de SQLPro : http://sql.developpez.com/clefs/

Les 2 premiers paragraphes expliquent tout d'abbord quelles sont les qualités d'une clef, et pourquoi il faut éviter les clefs naturelles, et donc répondra à ta question. Ce qui n'empêche pas de lire la suite de l'article qui est fort interressant ;o)

Merci pour l'article

[SQLpro]

Si on choisit on clé primaire "non naturelle" (une clé numérique par exemple) comme clé primaire faudra-t-il lui associer un index (au sens CREATE INDEX) pour en assurer l'unicité ?

Pas un index, mais une contrainte SQL d'unicité. l'un n'empêche pas l'autre... mais la clef candidate peut ne pas être valuée au moment de l'insertion. Or la norme SQL prévoir qu'une contrainte UNIQUE peut avoir plusieurs lignes à NULL ce qui est normal car NULL = NULL est toujours faux !

A +

[Luc Orient]

Envoyé par SQLpro

Pas un index, mais une contrainte SQL d'unicité. l'un n'empêche pas l'autre...

Et comment le SGBD va-t-il assurer l'unicité de manière performante ?
Ben ... par un index évidemment ...

[fsmrel]

Bonsoir SQLpro,


Désolé de revenir sur un point traité il y a un moment, mais vous me connaissez, je ne peux pas m’empêcher...

Envoyé par SQLpro

Or la norme SQL prévoir qu'une contrainte UNIQUE peut avoir plusieurs lignes à NULL

Donc, concernant cette contrainte UNIQUE, SQL Server 2005 ne serait-il pas un tantinet délinquant par rapport à la norme ? Je cite :

UNIQUE :

Contrainte fournissant à l'aide d'un index unique l'intégrité d'entité pour une ou plusieurs colonnes spécifiques. Les colonnes d'une contrainte UNIQUE peuvent être NULL, mais une seule valeur NULL est autorisée par colonne.

Par ailleurs, SQL Server n’assure pas l’intégrité d’entité, contrairement à ce qu’il prétend, mais seulement une contrainte d’unicité car, par définition l’intégrité d’entité interdit les valeurs nulles.

En outre, mêler conceptuel (intégrité d’entité) et physique (index, tuyauterie pour booster les performances) relève de la confusion mentale. Je rappelle que le terme "index" est étranger au Modèle Relationnel de Données et Ted Codd est clair à ce sujet :

In the context of the relational model this coupling with the DBMS of semantic properties of the data with performance in making index decisions is an abuse of the index concept and a DBMS design error. Uniqueness of values within any column should be specified as one of the properties of that column, not as a property of any index ("The Relational Model for Database Management: Version 2 (Reading, Mass.: Addison-Wesley, 1990", page 162).

(Ceci relève de l’indépendance physique).

Envoyé par SQLpro

NULL = NULL est toujours faux !

Vraiment ?

Je cite Codd à nouveau :

What is the truth value of x = y if x or y or both are null? An appropriate result in each of these cases is the unknown truth value, rather than true or false. ("Extending the Database Relational Model to Capture More Meaning, 1979", paragraphe 2.3 "Extensions of the Algebra for Null Values").

Je fais aussi référence à Chris Date, qui va plus loin. Date rappelle qu’en logique binaire, il y a deux valeurs de vérité : true et false et qu’en logique ternaire, elles sont trois, true, false et unk, et que dans ces conditions, si v est une variable logique dont la valeur de vérité est unk, alors "v = v" donne true. En revanche, comme dans ce qu’expose Codd, si la valeur de vérité de v est inconnue, alors "v = v" donne unk ("Relational Database Writings 1985-1989 (Reading, Mass.: Addison-Wesley, 1990", page 224).

[ylarvor]

Tout ce que tu dis est peut être valable pour la version standard mais le lien que tu aurais du fournir est le suivant :
http://msdn2.microsoft.com/fr-fr/library/ms174979.aspx

UNIQUE Contrainte assurant l'intégrité d'entité d'une ou plusieurs colonnes spécifiées au moyen d'un seul index. Une table peut comprendre plusieurs contraintes UNIQUE.

[fsmrel]

Bonsoir,

Envoyé par ylarvor

Tout ce que tu dis est peut être valable pour la version standard mais le lien que tu aurais du fournir est le suivant :
http://msdn2.microsoft.com/fr-fr/library/ms174979.aspx

Je ne connais malheureusement pas tous les liens MSDN, mais peu importe, j'accède donc bien volontiers à la rubrique proposée et je lis :

UNIQUE

Contrainte assurant l'intégrité d'entité d'une ou plusieurs colonnes spécifiées au moyen d'un seul index.

Je répète que mêler conceptuel (intégrité d’entité) et physique (index, tuyauterie pour booster les performances) relève de la confusion mentale. Le terme "index" est étranger au Modèle Relationnel de Données et ce que dit Codd à ce sujet continue à s’appliquer aux SGBD, quels qu'ils soient.

Je lis à la suite :

Une table peut comprendre plusieurs contraintes UNIQUE.

Cette phrase ne figure pas dans l’énoncé que j’ai mis en cause, et elle est tout à fait pertinente ! En effet, on doit pouvoir définir librement des clés alternatives, ce dont je fais personnellement une assez grande consommation.

Maintenant, si partant du lien proposé en citation, on clique sur Contraintes, puis sur Contraintes UNIQUE, on retrouve le couplet sur l’unicité de la valeur nulle :

contrairement aux contraintes PRIMARY KEY, les contraintes UNIQUE autorisent la valeur NULL. Cependant, comme pour toute valeur participant à une contrainte UNIQUE, une seule valeur NULL est autorisée par colonne.

N'étant pas personnellement adepte des valeurs nulles, je ne suis donc pas concerné par ce qu'écrit le rédacteur de la documentation en ligne de SQL Server 2005. Quoi qu’il en soit, les énoncés des différentes documentations ne sont pas contradictoires, ce qui est heureux ! Et en plus elles arrivent à se compléter...

[al1_24]

Envoyé par SQLpro

NULL = NULL est toujours faux !

Non !
On peut dire que NULL = NULL n'est jamais VRAI, éventuellement, mais il n'est jamais FAUX non plus, puisque que NULL = NULL est UNKNOWN

[fsmrel]

Envoyé par al1_24

On peut dire que NULL = NULL n'est jamais VRAI, éventuellement, mais il n'est jamais FAUX non plus, puisque que NULL = NULL est UNKNOWN

Vous êtes donc en phase avec MM. Codd, D & D (alias Date & Darwen, ou Dupond & Dupont, comme vous voudrez) que j'avais cités dans un précédent message concernant la formule NULL = NULL.

Les SGBDR sont aussi tous en phase sur ce point (au moins peut-on l’espérer...) Par exemple, comparons le salaire et la commission d’un employé :

Code :

1
2
3
4
5
6
7
8
SELECT EmpId, Salaire, Commission, 
               Case 
                  When Salaire = Commission Then 'Oui'
                  When NOT (Salaire = Commission) Then 'Non' 
                  Else 'Joker !'    
               End
               AS 'Salaire = Commission ?' 
FROM   Emp

Si pour un employé le salaire et la commission sont à NULL, à la question "Salaire = Commission ?" La réponse est : "Joker !" (même chose du reste dès que Salaire ou bien Commission sont à NULL).

Ceci met en évidence l’erreur de résultat que produirait la requête suivante, selon laquelle on raisonne binairement dans le contexte d'une logique ternaire :

Code :

1
2
3
4
5
6
7
SELECT EmpId, Salaire, Commission, 
               Case 
                  When Salaire = Commission Then 'Oui'
                  Else 'Non'    
               End
               AS 'Salaire = Commission ?' 
FROM   Emp

Voire celle-ci, selon laquelle si un employé a un salaire de 1000 et une commission à NULL, le salaire est égal à la commission...

Code :

1
2
3
4
5
6
7
SELECT EmpId, Salaire, Commission, 
               Case 
                When Salaire <> Commission Then 'Non'
                else 'Oui'    
               end
               AS 'Salaire = Commission ?'
FROM   Emp

[SQLpro]

fmsrel

Donc, concernant cette contrainte UNIQUE, SQL Server 2005 ne serait-il pas un tantinet délinquant par rapport à la norme ?

Oh que oui, et c'est le principal point qui me fait chier avec SQL Server ! Cela oblige a faire un index et un trigger...

Sur NUL = NULL est faux...
OK, cela donne UNKNOWN, mais le UNKNOWN se comporte comme faux dans tous les cas (NOT ou pas).

A +