Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 13 sur 13
  1. #1
    Invité régulier
    Inscrit en
    août 2009
    Messages
    17
    Détails du profil
    Informations forums :
    Inscription : août 2009
    Messages : 17
    Points : 7
    Points
    7

    Par défaut Clé Primaire en Char[6] ou Integer ?

    Bonjour,

    Je ne sais pas exactement si c'est au bon endroit que je poste ça, mais il faut bien se lancer.

    Les tables de ma base de donnée ont des cléfs primaires qui sont des Char[6] ou Char[8] alors que ceux-ci sont des valeurs numériques.
    J'ai lu dans le tuto de SQLPro qu'il était préférable d'avoir des clefs primaires en Integer.

    Ma question : Avez vous une idée de l'ordre de grandeur du gain en temps de réponse de la base si l'on passe de Char[] à Integer ?

    (Je suis débutant en SQL/Oracle/BDD ^^ )

    merci
    Ivan

  2. #2
    Membre Expert

    Homme Profil pro François Durand
    Spécialiste Delivery Mainframe IBM
    Inscrit en
    octobre 2005
    Messages
    1 198
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Spécialiste Delivery Mainframe IBM
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 198
    Points : 2 151
    Points
    2 151

    Par défaut

    Pour moi, le choix d'une clé primaire est une problématique de modélisation. Si vous avez fait le choix d'une clé primaire numérique (un numéro auto-incrémenté par exemple), alors oui vous pouvez choisir de la décrire en INTEGER.

    Dans le cas contraire, une description en CHAR est tout à fait acceptable.

  3. #3
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 713
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 713
    Points : 25 575
    Points
    25 575

    Par défaut

    Si tu as lu SQLPro, tu as dû lire aussi ses arguments en faveur d'une clé de type entier.
    Un entier, c'est 4 octets, un CHAR(6), c'est 6 octets. Une clé primaire de type entier prend moins de place et la recherche dans les index est plus rapide.

    Chez-moi, inutile de se torturer l'esprit trop longtemps : une clé primaire est de type entier et auto-incrémentée. Sauf éventuellement si c'est une table de référence ne comprenant que quelques lignes et avec une clé plus courte que 4 octets mais à condition que le contenu de cette clé ne puisse jamais être modifié. Un code susceptible de changer est une mauvaise clé primaire car la clé primaire doit être invariable pour éviter les erreurs de mise à jour des clés étrangères qui en découlent.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  4. #4
    Membre Expert

    Homme Profil pro François Durand
    Spécialiste Delivery Mainframe IBM
    Inscrit en
    octobre 2005
    Messages
    1 198
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Spécialiste Delivery Mainframe IBM
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 198
    Points : 2 151
    Points
    2 151

    Par défaut

    Citation Envoyé par CinePhil Voir le message
    Si tu as lu SQLPro, tu as dû lire aussi ses arguments en faveur d'une clé de type entier.
    Un entier, c'est 4 octets, un CHAR(6), c'est 6 octets. Une clé primaire de type entier prend moins de place et la recherche dans les index est plus rapide ...
    Eternel débat ...

    Je ne suis pas tellement convaincu par ses arguments. Le CHAR(6) prend plus de place certes, mais quid d'un CHAR(4) ? Quand à la rapidité, il donne des exemples sur SQL-Server, donc sur Windows / Intel, moi je suis sur DB2 z/OS ( le mainframe ) et là ses explications ne sont plus valables.

    Enfin, argument ultime, si une clé "naturelle" est présente dans la table et qu'on fait quand même le choix de ne pas l'utiliser comme clé primaire, alors il faudra quand même créer un index sur cette colonne, car elle a de fortes chances d'être un critère de recherche. Au final, on aura deux index au lieu d'un et le gain en performances (à l'insertion bien sûr) et en place devient totalement illusoire.

  5. #5
    Membre émérite Avatar de Jester
    Inscrit en
    septembre 2003
    Messages
    806
    Détails du profil
    Informations forums :
    Inscription : septembre 2003
    Messages : 806
    Points : 850
    Points
    850

    Par défaut

    Il est noté que ce sont des valeurs numériques donc un char(4), ça va pas loin (9999 valeurs).

    Cela dit, ça ne change pas forcément grand chose.

  6. #6
    Responsable Livres

    Avatar de MaitrePylos
    Homme Profil pro Gérard Ernaelsten
    DBA & Dev PHP
    Inscrit en
    juin 2005
    Messages
    3 790
    Détails du profil
    Informations personnelles :
    Nom : Homme Gérard Ernaelsten
    Âge : 41
    Localisation : Belgique

    Informations professionnelles :
    Activité : DBA & Dev PHP
    Secteur : Service public

    Informations forums :
    Inscription : juin 2005
    Messages : 3 790
    Points : 9 163
    Points
    9 163

    Par défaut

    Une clé primaire doit-être unique, si dans sa modélisation il a repéré un champ unique de type char, alors pourquoi ajouter une colonne en int?

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 069
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 069
    Points : 29 231
    Points
    29 231

    Par défaut

    Citation Envoyé par Luc Orient Voir le message
    Je ne suis pas tellement convaincu par ses arguments. Le CHAR(6) prend plus de place certes, mais quid d'un CHAR(4) ? Quand à la rapidité, il donne des exemples sur SQL-Server, donc sur Windows / Intel, moi je suis sur DB2 z/OS ( le mainframe ) et là ses explications ne sont plus valables.
    Étant donné que les SGBDR fonctionnent tous de la même manière à de subtiles différences près (MySQL excepté) et que l'OS n'a aucune importance dans le cas d'un SGBDR puisqu'il possède le sien propre, la comparaison est valable et reproductible sous Oracle, DB2, PostGreSQL ou Sybase.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  8. #8
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 713
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 713
    Points : 25 575
    Points
    25 575

    Par défaut

    Hier, je rends visite à mes anciens collègues de l'INRA et celui qui m'a succédé dans l'étude de la base de données sur les bovins me demande mon avis sur le fait qu'une recherche d'un bovin sur son NUMNAT, de type VARCHAR(12) parmi 65 millions de lignes soit instantané et que la même recherche sur une autre base de contrôle laitier avec le même type de colonne prenne 7 secondes alors que la table ne compte "que" 5 millions de lignes.

    Table des bovins : clé primaire anonyme de type entier, NUMNAT indexé.
    Table du contrôle laitier : clé primaire triple sur deux colonnes VARCHAR (dont le NUMNAT) et une colonne en SMALLINT. Pas d'index séparé sur le NUMNAT.

    Je lui ai recommandé d'arranger ça !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  9. #9
    Membre Expert

    Homme Profil pro François Durand
    Spécialiste Delivery Mainframe IBM
    Inscrit en
    octobre 2005
    Messages
    1 198
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Spécialiste Delivery Mainframe IBM
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 198
    Points : 2 151
    Points
    2 151

    Par défaut

    Citation Envoyé par CinePhil Voir le message
    ... mon avis sur le fait qu'une recherche d'un bovin sur son NUMNAT, de type VARCHAR(12) ...

    Table des bovins : clé primaire anonyme de type entier, NUMNAT indexé.
    Je ne comprends pas ... Cette clé primaire est VARCHAR(12) ou INTEGER ?

    Par ailleurs, des clé primaires avec du VARCHAR, je trouve ça plutôt étrange ...

  10. #10
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 713
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 713
    Points : 25 575
    Points
    25 575

    Par défaut

    La table des bovins a une clé anonyme de type entier + un identifiant officiel appelé NUMNAT de type VARCHAR(12) et indexé séparément de la clé primaire.

    La table du contrôle laitier a une clé primaire triple composée de deux colonnes en VARCHAR, dont le NUMNAT + une colonne en SMALLINT. La table du contrôle laitier n'est pas optimisée du fait de la clé primaire avec des colonnes en VARCHAR et du fait que le NUMNAT n'a pas d'index séparé et n'est pas la première colonne de la clé primaire.

    Enfin bref... Ce n'est plus mon problème.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  11. #11
    Membre Expert

    Homme Profil pro François Durand
    Spécialiste Delivery Mainframe IBM
    Inscrit en
    octobre 2005
    Messages
    1 198
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Spécialiste Delivery Mainframe IBM
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 198
    Points : 2 151
    Points
    2 151

    Par défaut

    Citation Envoyé par CinePhil Voir le message
    ... et du fait que le NUMNAT n'a pas d'index séparé et n'est pas la première colonne de la clé primaire.
    Pour moi, le problème se situe bien là ... ça n'a aucun rapport avec le fait que l'autre table possède ou pas une clé primaire en INTEGER ...

  12. #12
    Membre régulier Avatar de zolive
    Profil pro olivier
    Inscrit en
    décembre 2003
    Messages
    152
    Détails du profil
    Informations personnelles :
    Nom : olivier
    Localisation : France, Isère (Rhône Alpes)

    Informations forums :
    Inscription : décembre 2003
    Messages : 152
    Points : 98
    Points
    98

    Par défaut

    si on a une table de 8 millions d'enregistrements.
    un enregistrement contient entre autre un champ char64, si on sait par le métier que ce champ (chaine) ne peut avoir qu'une centaine de valeur différentes.
    Table A
    idA (long Pk) , chaine (char64) , chaine 2 (char256)

    On a intérêt à créer une autre table qui recense cette centaine de valeur non ?
    Table de reference
    idChaine(char64 Pk)

    Cette table a même intérêt à avoir une clef primaire différente de la chaine et dire a la base que la idChaine est unique :
    Table de reference modifiée:
    idRef (int Pk), idChaine(char 64 unique)

    Au final la table A se trouve modifié ainsi :
    idA (long Pk) , idRef (int Fk) , chaine 2 (char256)

    Je me trompe si je dis que la Table A gagne 58 octets par enregistrement ?
    au final comme on a 8 Millions d'enregistrement le gain est substantiel non ?

    La table de référence ne contenant qu'une centaine de valeur elle risque fort d'être monté entièrement en mémoire et ne pas grever les requêtes.


    Olivier
    Ps je ne suis pas du tout expert en BDD donc si je dis une grosse betise ne m'en voulez pas

  13. #13
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 713
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 713
    Points : 25 575
    Points
    25 575

    Par défaut

    On a intérêt à créer une autre table qui recense cette centaine de valeur non ?
    Oh que oui !

    Table de reference modifiée:
    idRef (int Pk), idChaine(char 64 unique)
    Oui !

    Au final la table A se trouve modifié ainsi :
    idA (long Pk) , idRef (int Fk) , chaine 2 (char256)
    Oui !

    Un petit détail tout de même :
    si on a une table de 8 millions d'enregistrements.
    Un id de type INTEGER, codé sur 4 octets, est suffisant puisque la plage de valeur va de -2 147 483 648 à 2 147 483 647. Et si l'entier est non signé, vous avez de la place pour plus de 4 milliards d'identifiants !

    Table A
    idA (long Pk) , chaine (char64) , chaine 2 (char256)
    ...
    Au final la table A se trouve modifié ainsi :
    idA (long Pk) , idRef (int Fk) , chaine 2 (char256)
    ...
    Je me trompe si je dis que la Table A gagne 58 octets par enregistrement ?
    On passe d'un CHAR(64), soit 64 octets, à un INT, soit 4 octets. 64 - 4 = 60 octets gagnés si je ne m'abuse.
    Et avec un id de type INTEGER, on gagne encore 4 octets puisque je pense que LONG doit être sodé sur 8 octets non ?

    au final comme on a 8 Millions d'enregistrement le gain est substantiel non ?
    8 millions de lignes (et pas enregistrements) * 64 octets potentiels = 512 millions d'octets.
    La table ferait alors (4 + 4 + 256) x 8 000 000 = 2 112 000 000 octets, en gros 2Go au lieu de 2,5 Go auparavant.
    Ce n'est pas négligeable. D'autant plus que l'index sera également plus petit.

    La table de référence ne contenant qu'une centaine de valeur elle risque fort d'être monté entièrement en mémoire et ne pas grever les requêtes.
    Il y a des chances oui. Surtout si elle est souvent utilisée, elle y restera.

    Ps je ne suis pas du tout expert en BDD donc si je dis une grosse betise ne m'en voulez pas
    Ca me semble pas mal pour un "non expert" ! Ce que je ne prétends pas être encore d'ailleurs !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •