Choix du type de la clef primaire

**Kropernic** · 27/09/2012, 10h17

Bonjour,

Jusqu'ici, en tant que neuneu qui ne comprenais qu'une partie de ce qu'il faisait, je mettais consciencieusement une colonne de type int autoincrémentée comme clef primaire de mes tables histoire d'éviter tous problèmes futurs.

Aujourd'hui, maintenant que j'en sais un peu plus (grâce à la communauté de ce forum), je me pose la question pour les tables dont je sais que le nombre de ligne sera réduit.

Un int étant stocké sur 4 octets, pour des tables dont on est CERTAIN que le nombre de ligne ne dépassera JAMAIS 26, ne peut-on pas utiliser un type char(1) ?

Par exemple, je suis actuellement en train de modéliser une nouvelle DB où il y aura notamment une table transaction donc chaque ligne sera préciser par un type de transaction où le type de transaction fait l'objet d'une table à part entière.

Les types de transactions sont connus et aux nombres de 3. Dans ce cas, n'est-il pas dommage d'utiliser un type int qui sera "sous-utilisé". Vu que les transactions vont être très nombreuses (la table la plus volumineuse de la DB), ne serait-il pas mieux de mettre un Char(1) ?

Qu'en est-il de l'avis des experts que vous êtes ?

Merci d'avance.

**Serguei_TARASSOV** · 27/09/2012, 10h52

Bonjour,

On est en OLTP ou OLAP ?

En OLAP la volume est significative et on est en mode consultation uniquement. La bonne pratique est d'utiliser le type de la taille plus petit possible : tinyint (< 256 lignes), smallint (< 32K lignes ou 64K y compris les valeurs négatives), int, puis bigint et guid.

En OLTP les volumes sont relativement petites par contre les couches applicatives sont nombreuses au-dessus, les ORM peuvent mettre certaines contraintes sur la structure, la BDD peut être distribuée (i.e. réplication p2p) etc. Dans ce cas le type des IDs doit être homogène/uniforme pour tous les entités afin de faciliter le développement.

Concernant ton exemple, le type "tinyint" est plus pertinent puisque les types "chaine de caractères" sont impactés par les collations en plus de comparaisons binaires. Mais en Oracle ce n'est pas ça, i.e. la bonne pratique pour les types "bool" est d'utiliser varchar(1) et check ('T', 'F')

**Kropernic** · 27/09/2012, 11h00

OLTP... OLAP...

Aucune idée de ce que cela veut dire

.

Cependant, tu parles des collations et c'est une chose à laquelle je n'avais pas songé et qui penche en faveur du type numérique...

Je vais encore attendre une ou deux opinions avant d'arrêter mon choix.

**Serguei_TARASSOV** · 27/09/2012, 11h27

OLTP - -transactionnel, OLAP - décisionnel.
Voici une exemple des pb avec les collation

**Kropernic** · 27/09/2012, 11h32

J'imagine alors que je suis en OLTP

**SQLpro** · 27/09/2012, 11h36

Un type de connées littéral gère en sus une collation, à moins de le prévoir en binaire. Il y a donc plus d'effort à, faire lors des compaaisons.
Même le SMALLINT ou TINYINT coute plus cher en traitement que le INT sur OS 32 bits ou le BIGINT sur sytème 64 bits du fait des conversions.
Brf, ne vous emmerdez pas les poils du cul, car même un BIGINT sur 26 lignes, cela ne représentera que 7 octets de moins par ligne, soit 182 octets, à l'heure ou la moindre base fait quelques Go, soit une économie réelle de 0,0000084750354290008544921875 %

A +

**Kropernic** · 27/09/2012, 11h41

Ok donc je laisse mes types numériques où ils sont ^^.

INT pour 32 bits et BIGINT pour 64 bits.

C'est noté !!

**Serguei_TARASSOV** · 27/09/2012, 12h03

Envoyé par SQLpro

Même le SMALLINT ou TINYINT coute plus cher en traitement que le INT sur OS 32 bits ou le BIGINT sur sytème 64 bits du fait des conversions.

Existent-ils les tests au ce sujet ?
A ma connaissance, les types tinyint/smallint/int/bigint sont les types logiques et ne concerne que le stockage mais en interne (cache des données et traitement) ce sont les types optimisés pour l'architecture de plateforme cible. Mais je ne peux pas en confirmer en 100%.

Envoyé par Kropernic

INT pour 32 bits et BIGINT pour 64 bits.

Ce n'est pas la règle de choix tout à fait.

**iberserk** · 27/09/2012, 14h19

Un type de connées littéral gère en sus une collation, à moins de le prévoir en binaire. Il y a donc plus d'effort à, faire lors des compaaisons.
Même le SMALLINT ou TINYINT coute plus cher en traitement que le INT sur OS 32 bits ou le BIGINT sur sytème 64 bits du fait des conversions.
Brf, ne vous emmerdez pas les poils du cul, car même un BIGINT sur 26 lignes, cela ne représentera que 7 octets de moins par ligne, soit 182 octets, à l'heure ou la moindre base fait quelques Go, soit une économie réelle de 0,0000084750354290008544921875 %

Vous partez du principe que cette colonne ne sera pas référencée dans des tables filles!

S'il y a des millions de transactions alors on perd 7* des millions d'octets...

**SQLpro** · 27/09/2012, 14h25

Pour chaque ligne de chaque table fille cela ne fera que 7 octet au plus par ligne... De plus, si le quidam ne ramène pas cette colonne, quelque soit le nombre de ligne, cela ne changera rien !

A +

**Kropernic** · 27/09/2012, 14h26

Envoyé par iberserk

Vous partez du principe que cette colonne ne sera pas référencée dans des tables filles!

S'il y a des millions de transactions alors on perd 7* des millions d'octets...

*ne sait plus à quel saint se vouer*

D'un côté, les collations qui demandent du boulot en plus (lors de la comparaison et leur de l'écriture de la requête si celles des deux colonnes à comparer ne correspondent pas).
D'un autre côté, les types numériques prenant plus de place que le char(1), on perd 7*n octets (ce sera sur un OS 64 bits) où n est le nombre de ligne référençant une ligne de la table mère.

Rapidité de traitement ou espace de stockage... Le choix se pose là en fait non ?

**SQLpro** · 27/09/2012, 15h13

Si votre table n'évoluera pas en cardinalité OK. par exemple table des jours de semaines ou des noms de mois...

A +

**Kropernic** · 27/09/2012, 15h33

A ce niveau là, pas de souci. Pour l'exemple que j'ai donné, c'est une table de type de transaction caisse. Or y a pas des tonnes de transaction caisse surtout que la DB ne concernera que les "gifts" (les trucs genres bons d'achat qu'on offre pour un annif ou autre...).

Bref, il devrait y avoir en tout et pour tout 3 lignes dans cette table.
Une pour la vente d'un gift, une pour l'utilisation d'un gift et une pour la recharge.

Quand bien même d'autres viendrait se rajouter, cela ne dépassera jamais les 26 possibilités offertes par l'alphabet latin.

De plus, la table des transactions caisses contiendra des millions de lignes. Chaque ligne référençant la table des types de transaction caisse.

Cette histoire de collation est-elle vraiment pénalisante d'un point de vue performance ? Je ne me rends pas encore bien compte de comment fonctionne sql server lors de la comparaison d'une condition ...

**iberserk** · 27/09/2012, 15h47

Cette histoire de collation est-elle vraiment pénalisante d'un point de vue performance ?

Pas si la collation est BINAIRE (EX: FRENCH_BIN)

**Kropernic** · 27/09/2012, 16h02

Envoyé par iberserk

Pas si la collation est BINAIRE (EX: FRENCH_BIN)

Donc si j'ai bien tout compris, un type char(1) est ok si on est certain à 100% que le nombre de ligne restera restreint, que les lignes de la table seront référencées des millions de fois et que la collation pour cette colonne est binaire.

N.B. : Ne pas oublier de paramétrer la même collation sur les clefs étrangères des tables filles.

J'ai oublié quelque chose ?