Clés étrangères et performances

[NiHiL]

Bonjour,

j'aimerai savoir si la déclaration de champs en temps que clés étrangères à un impact sur les performances d'une base de données (surtout si celle ci est énorme).

Merci.

[qi130]

Bien sûr...

En mise à jour, il y a le temps de vérification que la FK existe; et, si une option "ON DELETE" est positionnée, il y a le coût de traitement du CASCADE.

De plus, les FK font souvent l'objet d'un index , ce qui induit aussi un coût.

[NiHiL]

Donc en clair les clés étrangères ont forcément un impact négatif sur les performances.

Merci.

[al1_24]

Elles sont un impact négatif sur les performances lors des mises à jour.

Il faut aussi prendre en compte l'apport des index sur ces clés étrangères lors des restitutions et, srutout, ne pas oublier l'intérêt de ces contraintes sur la conservation de l'intégrité des données.

[SQLpro]

Oui et non !

En fait mes collègues ont raison mais dites moi comment vous feriez autrement ?

Soit vous utiliser une "mono table" avec 214561620654156 colonnes et donc l'insertion comme la suppression sera épouvantablement lente...
Soit vous laissez toutes vos tables sans clefs étrangères et vous avez toutes les chances de vous retrouver avec des données incohérente et ce sera donc les requêtes SELECT qui vont devenir très lentes....

A vous de choisir quel est le pire des maux !

A +

[NiHiL]

Donc en clair l'ultime solution c'est de pas utiliser les clés étrangères et de bétonner les requêtes de suppressions et de modifications pour respecter l'intégrité référentielle.

[SQLpro]

Non, c'est l'inverse : toujours utiliser des FK afin de reporter l'effort à l'insertion, car les insertions sont généralement monolignes et le delta insignifiant. En revanche lorsque vous allez devoir faire des SELECT sur des millions de ligne, le surcout engendré pour nettoyer à la volée sera très important et surtout reproduit à chaque SELECT.
Autrement dit 10% de traitement en plus sur 1ms à chaque insert (c'est à dire 1000 fois pas jour par exemple) ce n'est pas sensible.
En revanche 50% de traitement en plus sur un select de 100 000 lignes qui dure 3 secondes, c'est long surtout si ce select est joué 30 fois par jour....

A +

[yador]

Bonjour,

Est-ce qu'il y a un moyen, une méthode ou une étude qui a été faite afin de pouvoir chiffrer le coût en performances de l'utilisation de contraintes d'intégrité référentielle (FK) dans une base et ce, bien entendu, lors d'update et d'insert.

Car si l'utilité de telles contraintes n'est plus à prouver, bcp de personnes pensent encore que le coût en performances est trop important sur un système hautement transactionnel.

Merci d'avance.

[fsmrel]

Envoyé par NiHiL

en clair l'ultime solution c'est de pas utiliser les clés étrangères et de bétonner les requêtes de suppressions et de modifications pour respecter l'intégrité référentielle.

Je vous renvoie à la discussion Contraintes d'intégrités obligatoires ou inutiles. En procédant manuellement, vous n’arriverez jamais à bétonner, que ce soit pour les requêtes de modification, suppression et ajout. Pardonnez-moi, mais votre béton aura tout du gruyère, pour un tas de raisons. Le fil auquel je vous renvoie traite d’un petit échantillon de constats, suite à 20 ans de pratique des SGBDR (et de plus de 35 ans de SGBD pré-relationnels). Je ne déroulerai pas la litanie de tous mes constats, ça serait à la nausée.

Je rappelle souvent que lorsque DB2 (et a fortiori les autres SGBDR) ne gérait pas encore l’intégrité référentielle, le G.U.I.D.E. (Groupement des utilisateurs IBM) se faisait l’écho de la multitude qui la réclamait à cor et à cri. Quand au bout de quatre ans, en 1988, IBM nous la livra, changement de chanson : "Finalement, est-ce bien utile ? Est-ce que ça ne coûterait pas la peau des fesses quant aux performances ?" Concernant l’utilité, il n’y a pas photo, l’intégrité des données n’a pas de prix. Quant à la performance, plutôt que se poser des questions, il faut retrousser ses manches et en quantifier les bienfaits à coups de prototypage. C’est ce que je fis à l’époque, et engageai mon entreprise, auprès d’un très grand industriel, réputé pour son intransigeance sur le sujet de la performance et de l’intégrité des données. L’application fut déployée à l’échelon national, puis européen et elle est en train de l’être à l’échelon mondial.

Maintenant, si vous préférez ne pas utiliser l’intégrité référentielle et bétonner vos requêtes par programmation, vous serez dans l’obligation d’effectuer tous les contrôles que vous auriez pu sous-traiter au SGBD avec une simple clause Foreign Key : par exemple, en cas d’ajout d’une facture, vérifier que le client est présent, même chose pour les produits figurant sur les lignes de facture. En cas de suppression d’un client, vérifier l’existence de ses factures et plus généralement aller explorer toutes les tables dépendant directement et indirectement de la table Client : non seulement cela finit par représenter beaucoup de code à développer, mais surtout, au fil du temps et de l’évolution de la base de données, il ne faut pas vous leurrer, vous ne bétonnerez plus grand chose. Quant à la performance des contrôles : par curiosité, j’ai voulu me mesurer à DB2, lequel s’est avéré (à l’époque) aller en moyenne cinq fois plus vite que moi (en tant qu'ingénieur système, je programmais directement en assembleur et en utilisant toutes les ficelles et astuces aux frontières du soft et du hard). Avec le temps, les algorithmes d'un SGBDR sont de plus en plus raffinés et efficaces alors que les nôtres sont figés pour un bon moment : autant vous dire que depuis cette époque, j’ai renoncé à la compétition.

[fsmrel]

Envoyé par yador

Est-ce qu'il y a un moyen, une méthode ou une étude qui a été faite afin de pouvoir chiffrer le coût en performances de l'utilisation de contraintes d'intégrité référentielle (FK) dans une base et ce, bien entendu, lors d'update et d'insert.

Si vous êtes utilisateur d’IBM, nombre de "Redbooks" traitent du sujet. Pour les autres SGBD, posez la question sur les forums dédiés. A défaut, prototypez (Cf. mon précédent message).

Envoyé par yador

Car si l'utilité de telles contraintes n'est plus à prouver, bcp de personnes pensent encore que le coût en performances est trop important sur un système hautement transactionnel.

Laissez-les penser et faites plutôt comme SQLPro, agissez. Devenez pointu sur le sujet et démontrez aux dubitatifs qu’ils pensent de travers.

[yador]

Merci pour votre réponse fsmrel!
Pour ma part, j'ai continué mes recherches et également fait des tests.
Mes résultats ne montrent pas une énorme différence entre un schéma avec contraintes d'intégrité référentielle et un schéma sans. En faisant tout de même la moyenne de tous mes tests, j'arrive avec de meilleurs résultats pour le schéma sans contraintes. Mais la différence est de l'ordre de < 1% pour 4000 opérations d'update. Plus d'opérations seraient les bienvenues ainsi qu'une machine dédiée à ces tests (pas d'autre utilisation).

J'ai trouvé par contre de l'information dans l'excellent bouquin de Tom Kyte "Effective Oracle By Design" où il traite du sujet et donne même les résultats de ses propre tests. Il conclut par dire qu'il faut compter entre 10 et 15% de overhead dû aux contraintes d'intégrité référentielle! Il n'oublie pas de citer les nombreux avantages que ce léger overhead apporte !!!

En définitive, ces quelques tests et recherches confortent mon idée de départ: les FK coûtent en performance, certes, mais sont ABSOLUMENT NECESSAIRES !!!

[fsmrel]

Envoyé par yador

les FK coûtent en performance, certes, mais sont ABSOLUMENT NECESSAIRES !!!

Et rappelez à ceux qui estiment inutile de mettre en oeuvre l'intégrité référentielle :

• Est-il utile d'aller toujours plus vite, quand l’intégrité des données est violée ?
  
  
• Au-delà de leur performance (que l’on peut mettre en doute) les contrôles applicatifs ne sont-ils pas faillibles ?

L'assurance ça coûte cher avant l'accident. Quand j'ai rattrapé (grâce aux sauvegardes des mois précédents) une base de données dans laquelle des milliers de contrats faisaient référence a des clients ayant disparu, j’en ai tiré un certain nombre de leçons (cf. à nouveau la discussion parmi d'autres sur l'intégrité référentielle).

Keep the good work.

[SQLpro]

Sans compter que lorsque je fait des audits de BD si je constate l'absence de FK, je cris au scandale. Sur le plan juridique c'est condamnable au titre du non respect des règles de l'art.

La question dans l'univers du maçon pourrait être : faut-il faire un mur droit ou peut-il être penché ? La plupart du temps le devis que va faire le maçon ne précise pas si le mur est droit ou penché... Or s'il est penché le maçon peut être condamné sur le plan juridique.
Il en est de même de l'informaticien qui ne respecterait pas les règles de l'art en matière de développement. En tant que patron j'aurais tendance à virer un employé qui s'affranchirait des FK sans demander approbation à son supérieur !

A +