Petites tables ou grandes tables. . . Quelles conséquences sur les performances ?

[SQLpro]

Bonjour,

La plupart des développeurs sont persuadés que mettre toutes les informations dans une même table rendra leur base de données plus rapide... Et l'on voit apparaître dans la base de nombreuses tables de plusieurs dizaines de colonnes. C'est une vue à court terme, car dés que la base de données commence à croitre ou que le nombre d'utilisateur augmente, les performances deviennent vite catastrophique... Cet article explique pourquoi...

http://blog.developpez.com/sqlpro/p1...ances-petites/

Vos commentaires sont les bienvenus !

A +

[Waldar]

Bon article, j'y mettrai deux bémols dont on peut discuter.

Les verrous
Avec Oracle, un verrou exclusif ne bloque que la ligne en écriture, pas toute la table. On peut toujours lire la table, insérer de nouvelles données, mettre à jour des données sur d'autres lignes.
Donc ce n'est pas pénalisant outre mesure.

Le type de base de données
Par type j'entends utilisation. Une base de données OLTP ou OLAP n'ont pas du tout les mêmes contraintes d'utilisation. La première fait beaucoup de petites opérations fortement transactionnées multi utilisateurs, la seconde est mono utilisateur, lecture seule en journée et mise à jour la nuit.
Les pros de la dénormalisation en OLAP auront sûrement quelques arguments à avancer (mais je n'en fais pas partie).

Edit : 21% sur ma base OLAP ! Je vais me faire disputer !

[iberserk]

Edit : 21% sur ma base OLAP ! Je vais me faire disputer !

Bon c'est du OLAP alors ca va

[Luc Orient]

Je ne comprends pas cette phrase de l'article :

Différences entre des petites tables et une grosse table pour les opérations de mises à jour (écriture) :

• Dans une grosse table contenant un grand nombre de colonnes, chaque écriture (INSERT, UPDATE or DELETE) pose un verrou exclusif tant est si bien que personne d'autre ne peut l'utiliser.

Mais pour l'auteur de l'article le verrou exclusif est posé sur quoi ?

[zOS19]

ca me semble vraiment un raccourci rapide

ce que je retiens de l'article, c'est qu'il faut bien respecter la normalisation...

Apres honnetement, ca me semble un raccourci rapide de dire si vous avez plein de tables avec plein de colonnes vous aurez des problemes de performances...

Soyons serieux 5 minutes, par exemple il y a des milliers de systèmes SAP dans le monde avec des bases de données de plusieurs To , avec des dizaines de milliers de tables contenant parfois des dizaines de colonnes et ces systèmes fonctionnent très bien...

[SQLpro]

Vous avez raison, je perle de base de données relationnelles, pas décisionnelles.
Sur le nombre de colonnes par table, même SAP reste dans les standards.
En sus il faut aussi relativiser... 50 colonnes dont 40 de type booléen, ce n'est pas grand chose.

A +

[nathalie.laudun]

Ca m'a l'air bien théorique et bien coloré "noir ou blanc"... Ou bien on est "normalisé" ou bien on met "tout dans une seule table"... Un peu exagéré comme commentaire, non?

Si on parle Oracle, je ne vois pas comment joindre des tables de millions de lignes ne coûte "presque rien".
exemple:
CLIENT( id, nom, ... )
PROFESSION( id, flag_liberale, description, ... )
PROFESSIONS( client_id, flag_prof_principale, profession_id )
ADRESSES( client_id, adresse_no, code_postal, rue, ... )
Et maintenant je cherche les gens ayant une profession libérale et habitant dans "75...."

Code :

1
2
3
4
5
6
7
SELECT DISTINCT c.nom AS prospect, ...
FROM client c, profession p, professions cp, adresses a
WHERE c.id = cp.client_id
AND c.id = a.client_id
AND a.code_postal LIKE '75%'
AND cp.profession_id = p.id
AND p.flag_liberale = 1

Cet exemple est simple et un peu naif mais il va quand même "ramer"...
Siebel par exemple a fait un peu de dénormalistation en entrant l'adresse principale dans la table "client". Ca devient

Code :

1
2
3
4
5
6
7
SELECT c.nom AS prospect, ...
FROM client c, profession p, professions cp
WHERE c.id = cp.client_id
AND c.code_postal LIKE '75%'
AND p.flag_liberale = 1
AND p.id = cp.profession_id
AND cp.flag_prof_principale = 1

(oui oui, pas tout à fait le même "result set", il faut sacrifier un peu sur l'autel de la performance)
Et on peut mettre aussi un flag "prof_liberale" dans CLIENT pour arriver à

Code :

1
2
3
4
SELECT c.nom AS prospect, ...
FROM client c
WHERE c.code_postal LIKE '75%'
AND c.flag_liberale = 1

Quand on doit faire ce genre de recherche en temps réel pour des millions et des millions de clients, la théorie retourne dans les livres.
J'ai des tables qui ont plus d'un milliard d'enregistrements
J'ai des tables qui ont plus de 800 colonnes ((heureusement, pas les mêmes ;-))
J'ai des utilisateurs qui ne se plaignent pas (enfin, pas toujours) de la performance.

[CinePhil]

Et maintenant je cherche les gens ayant une profession libérale et habitant dans "75...."

Code :

La syntaxe normalisée pour les jointures utilise depuis 19 ans l'opérateur JOIN ; il serait temps de s'y mettre !

Code :

1
2
3
4
5
6
7
SELECT DISTINCT c.nom AS prospect, ...
FROM client c
INNER JOIN professions cp ON c.id = cp.client_id
  INNER JOIN profession p cp.profession_id = p.id
INNER JOIN adresses a ON c.id = a.client_id
WHERE a.code_postal LIKE '75%'
  AND p.flag_liberale = 1

Cet exemple est simple et un peu naif mais il va quand même "ramer"...

Peut-être en partie à cause du LIKE '75%'

Quand on doit faire ce genre de recherche en temps réel pour des millions et des millions de clients, la théorie retourne dans les livres.

Ou bien on se repenche sur son modèle de données, surtout quand :

J'ai des tables qui ont plus de 800 colonnes

Quelle entité peut bien avoir 800 propriétés différentes, toutes non nulles ?

J'ai des utilisateurs qui ne se plaignent pas (enfin, pas toujours) de la performance.

Heureusement que c'est du Oracle, probablement sur un serveur correctement dimensionné !

[Luc Orient]

Envoyé par CinePhil

La syntaxe normalisée pour les jointures utilise depuis 19 ans l'opérateur JOIN ; il serait temps de s'y mettre !

Syntaxe normalisée ou pas, et malgré ce que certains martèlent ici, ça n'a aucune influence sur les performances ...

Quelle entité peut bien avoir 800 propriétés différentes, toutes non nulles ?

800 colonnes ça commence à faire beaucoup effectivement ... Mais entre 20 et 800 on doit pouvoir trouver un juste équilibre. Par ailleurs je trouve cette pseudo-limite des 20 colonnes totalement absurde.

[SQLpro]

Luc, donne moi plus de 20 attributs directement lié à :
1) une personne
2) une facture
3) un produit
...

A +

[vmolines]

Des coordonnées dans un espace à 20 dimensions

[punkoff]

Envoyé par nathalie.laudun

Quand on doit faire ce genre de recherche en temps réel pour des millions et des millions de clients, la théorie retourne dans les livres.
J'ai des tables qui ont plus d'un milliard d'enregistrements
J'ai des tables qui ont plus de 800 colonnes ((heureusement, pas les mêmes ;-))
J'ai des utilisateurs qui ne se plaignent pas (enfin, pas toujours) de la performance.

Et où est votre table code_postal + la table d'association entre votre "personne" et celle-ci ?

Vous vous mettez des battons dans les roues tout seul si je ne m'abuse ?


Là vous connaissez votre volumétrie, vos besoin fonctionnelles et vous ne vous adaptez pas ..

[Yanika_bzh]

Envoyé par SQLpro

Luc, donne moi plus de 20 attributs directement lié à :
1) une personne
2) une facture
3) un produit
...

A +

Par exemple une voiture :

Année Production
Poids
Répartition AV/AR (kg)
Longueur
Largeur
Hauteur
Empattement
Jantes
Pneumatique
Type (Nb Cylindres)
Position
Materiaux (Culasse/Bloc)
Nombre de soupapes par cylindre
Distribution
Alimentation allumage
Suralimentation
Cylindrée (Cm3)
Alésage x course (mm)
Rapport Volumétrique
Régime Maximum (tr/min)
Puissance Maxi (ch à tr/min)
Puissance au litre (ch)
Couple maxi (mkg a tr/min)
Couple au litre (mkg)
Boite de vitesse
Nombre de vitesse
Transmission
suspension Avant
Suspension Arriere
Direction
Type de Freins
Diametres des Freins (mm
Etrier
Piston

non ?

[Yanika_bzh]

Envoyé par CinePhil

La syntaxe normalisée pour les jointures utilise depuis 19 ans l'opérateur JOIN ; il serait temps de s'y mettre !

Comme Luc, cette remarque perpétuelle a le don de m'agacer...
En effet, la syntaxe normalisée pour les jointures est de deux types : EXPLICITE et IMPLICITE. Si effectivement SQL92 definit la jointure explicite, La BNF SQL92 ne bannit pas la jointure implicite.
En effet vous trouverez dans la regle de la grammaire SQL (BNF Grammar for ISO/IEC 9075:1992 - Database Language SQL (SQL-92)) pour la clause FROM :

FROM <from_clause>

<from_clause> ::= FROM <table_reference>[{<comma><table_reference>}...]

<comma> ::= ,

En suivant donc cette grammaire, rien n'empeche d'ecrire

FROM TableA, TableB

Donc la norme d'une jointure n'est pas uniquement celle d'une jointure explicite.
Vous pouvez (et j'en suis le premier) vous fixer une norme de developpement en n'utilisant que cette derniere, mais vous n'avez en aucun cas le droit de dire que c'est la norme SQL92 !!

[CinePhil]

Peut-être, je ne connais pas la norme par coeur.
Mais je ne compte plus le nombre de requêtes que j'ai débugguées dans nos forums rien qu'en récrivant les jointures avec la syntaxe explicite. C'est pas pour rien qu'on est passé du mélange des conditions de jointures et des conditions de restriction à leur séparation je pense ? Si cela a été fait, c'est que la syntaxe explicite est meilleure que la syntaxe implicite et c'est pour ça que je rappelle cette meilleure syntaxe et que j'encourage vivement tout le monde à l'utiliser et à bannir la vieille syntaxe qui présente tant de défauts.

[iberserk]

Yanika_bzh concernant votre exemple (certes je chipote):
toutes les données liées au moteur seraient dans une table à part puisque ces moteurs sont communs à plusieurs modèles...
Les "embarquer" dans votre table principale entraîne une redondance de données...

[Yanika_bzh]

Envoyé par iberserk

Yanika_bzh concernant votre exemple (certes je chipote)...
Mais par exemple toutes les données liées au moteur serait dans une table a part puisque ces moteurs sont communs à plusieurs modèles...
Les "embarquer" dans votre table principale entraîne une redondance de données...

Cela depend la encore de votre expression de besoin.
Imaginons que vous geriez des véhicules de courses, ceux ci possedent des caractéristiques modifiées par rapport a celles des constructeurs et des moteurs d'origine (ex, rabaissage de la cylindrée, rabottage des blocs, modification de la courses de pistons). Les modifications n'etant pas sérialisées, les caractéristiques non plus...

Maintenant, vous avez raison, je veux gerer des voitures standards de série, elles peuvent posseder le meme modele de moteur, donc, hop normalisation, et je me retrouve avec une entitée moteur... Maintenant que vais je pouvoir mettre dans cette nouvelle table ?? Essayons pour voir :

Identifiant : Necessaire bien sur
Description
Encombrement
Energie
Aspiration
Nombre de cylindres
Position des cylindres
Alesage
Cylindrée
Rapport volumétrique
Ordre d'allumage
Sens de rotation
Poids du moteur Equipé
Couple Maxi
Regime couple Maxi
Regime Maxi a vide
Regime mini ralenti
Capacité d'eau
Pression de pressurisation maximum
Valeur de reglage thermostat
...
...

[SQLpro]

De la même manière, le couple va dépendre de la boite de vitesse/cylindrée. Donc, sortez les attributs de couple dans une table de jointure moteur/BV.

Vous commencez donc à, comprendre l'art de la modélisation !

A +

[Yanika_bzh]

Envoyé par SQLpro

De la même manière, le couple va dépendre de la boite de vitesse/cylindrée. Donc, sortez les attributs de couple dans une table de jointure moteur/BV.

Vous commencez donc à, comprendre l'art de la modélisation !

A +

Vous devez confondre couple MAXI et couple TRANSMIS ...
Votre boite de vitesse ne modifiera pas le couple MAXI, mais le couple TRANSMIS.

[iberserk]

Vous devez confondre couple MAXI et couple TRANSMIS ...
Votre boite de vitesse ne modifiera pas le couple MAXI, mais le couple TRANSMIS.

J'allais le dire....

SQL PRO: le couple affiché sur la fiche technique est le couple développé par le moteur... il ne dépend donc pas de la boite de vitesse.

Mais nous chipotons...