Discussion :

[frankchabin]

Bonjour

Débutant, j'essaye de prendre les bonnes bases pour réaliser mon projet actuel.

J'aurais besoin de traiter une base de données étant alimentée par des messages issus d'un protocole X (type protocole réseau couche application) pouvant me générer des millions de messages uniques par jour.
J'ai pour l'instant décidé de prendre pour clés principales la date et trois autres clés étrangères : l'engin qui génère le message, le type de message et le modem qui le génère, ces 4 clés combinées me forment un identifiant unique pour chaque message. Ce header de message pour ma base sera repris en clé étrangère dans les tables des types de messages etc... Je retrouverais donc ces 4 clés dans plusieurs tables....

Ai-je bien compris comment optimiser, et ainsi ai-je bien fait ici de ne pas choisir de clé autoincrementée ?
Est-ce bien optimisé ?

J'avoue que le fait de retrouver autant de fois ces 4 clés dans plusieurs tables me fait peur, j'ose penser qu'une clé étrangère n'est pas dupliquée bêtement dans la base, sinon à quoi servent-elle à part répercuter les mises à jours de clés sur plusieurs tables, mais d'un autre côté il faut bien stocker la référence à cette clé étrangère...

Si quelqu'un peut m'aider, merci

[SQLpro]

Vous avez raison d'avoir peur.... Plus une clef est longue et complexe, moins elle est efficace. Le but d'une clef est de retrouver avec certitude une et une seule occurrence dans un ensemble. Comparons la notion de clef avec la combinaison d'un coffre fort... Et faisons l'analogie suivante :

• clef composée d'une une seule colonne => coffre avec un seul bouton rotatif pour la combinaison
• clef composée de 2 colonnes => coffre avec deux boutons rotatifs pour la combinaison
• clef composée de 3 colonnes => coffre avec trois boutons rotatifs pour la combinaison
• clef composée de 4 colonnes => coffre avec quatre boutons rotatifs pour la combinaison

Si vous avez décidé que c'est la vitesse de recherche (donc d'ouverture du coffre) qui est votre but... Alors quel coffre préférez vous ?

Dans votre cas, le meilleur compromis est d'utiliser une clef purement informatique de type auto incrément sur un BIGINT et de faire de votre clef sémantique naturelle une clef subrogée...

A +

[frankchabin]

Merci pour les indications

Hors mon problème est plus sur la place de stockage mémoire, plus que sur la complexité de l'accés.
Je me disais bêtement que si je combinais les données en 4 clé primaires, alors je n'aurais pas besoin de clé primaire bigint en plus des données déjà existante, et donc que cela ferait gagner de la place, mais suis je sur un fausse route?

[Waldar]

À moitié, mais vous l'avez déjà compris par vous-même : si votre clef primaire doit être introduite dans une autre table comme clef étrangère dès lors l'économie de la clef technique en terme de place est perdu.

Quant aux clef étrangères, elle ne servent pas à économiser de la place (oui, les valeurs sont physiquement réécrites dans toutes les tables), mais elles servent à garantir l'intégrité référentielle de votre base.

[aieeeuuuuu]

J'avoue que le fait de retrouver autant de fois ces 4 clés dans plusieurs tables me fait peur,

Quand vous dites ceci, on peut penser que votre table va être référencée par d'autre.
Aussi les 8 octets (du BIGINT en clef primaire) par ligne que vous allez "perdre" en ajoutant cette clef primaire, seront sans doute vite compensés par ceux que vous allez "récupérer" dans vos tables référençantes : vous n'avez pas précisé le type de vos données, mais un DATETIME2, c'est déjà minimum 6 octets... et dans votre cas( plusieurs millions de message par jour), je pense qu'il vous faudra une grande précision sur la colonne DATETIME2 pour éviter les collisions de clefs... donc certainement déjà 8 octets !
Ajoutez à cela les trois autres colonnes, allez disons 3 INT soit 12 octets, ça nous fait 20 octets pour la clef...
Soit 12 octets perdus pour chaque ligne de chaque table référençante.

En plus de la complexité de jointure indiquée par SQLPro, le gain de place dû à l'absence de clef primaire asémantique est donc loin d'être démontré...

[SQLpro]

Merci pour les indications

Hors mon problème est plus sur la place de stockage mémoire, plus que sur la complexité de l’accès.
Je me disais bêtement que si je combinais les données en 4 clé primaires, alors je n'aurais pas besoin de clé primaire bigint en plus des données déjà existante, et donc que cela ferait gagner de la place, mais suis je sur un fausse route?

Justement !!! Dans ce cas optez pour la clef auto incrémentée et la contraintes unique sur vos quatre colonne.
Le moteur SQL de votre SGBDR aura donc plusieurs choix d'index à disposition et utilisera celui qui correspond le mieux pour les performances en sus de diminuer le volume des données en mémoire. En effet la volumétrie de mise en mémoire d'un index pour des recherches est extrêmement faible en regard de ce que cela couterait si vous deviez utiliser uniquement la table !
L'explication est simple : pour faire une recherche dans une table, il faut monter toute la table en RAM. Pour faire une recherche en index, il ne faut remonter que :
1) la page racine
2) les pages de navigation
3) la page feuille contenant les données recherchées
soit au plus 2 à 5 pages !
Quelle économie de RAM !!!

Bref, plus vous mettez d'index, plus vous augmentez la volumétrie globale des données stockées sur le disque, mais plus vous diminuez, paradoxalement, la contention de la RAM.

A +

[SQLpro]

Quand vous dites ceci, on peut penser que votre table va être référencée par d'autre.
Aussi les 8 octets (du BIGINT en clef primaire) par ligne que vous allez "perdre" en ajoutant cette clef primaire, seront sans doute vite compensés par ceux que vous allez "récupérer" dans vos tables référençantes : vous n'avez pas précisé le type de vos données, mais un DATETIME2, c'est déjà minimum 6 octets... et dans votre cas( plusieurs millions de message par jour), je pense qu'il vous faudra une grande précision sur la colonne DATETIME2 pour éviter les collisions de clefs... donc certainement déjà 8 octets !
Ajoutez à cela les trois autres colonnes, allez disons 3 INT soit 12 octets, ça nous fait 20 octets pour la clef...
Soit 12 octets perdus pour chaque ligne de chaque table référençante..

Sans parler des problèmes de statistiques dont l'acuité est inversement proportionnel au nombre des colonnes de la clef et qui vont avoir pour effet d'induire des plans de requêtes potentiellement catastrophiques pour les performances (estimations faussées des cardinalités).

A +