Discussion :

[Halleck]

Bonjour tout le monde,

mes collègues et moi sommes en train de concevoir une application, plus précisément l'architecture de la base de données :

• beaucoup d'entrées (40 tables principales qui gravitent autour de 2 tables à 4 millions d'entrées au début)
• accès en lecture doit être très rapide
• accès en écriture doit être très rapide
• multi-sgbd (avec priorité à Oracle 11g)
• beaucoup d'accès concurentiels
• nombre d'users relativement conséquent (300 simultanés)

Notre question porte sur les tables intermédiaires:

• par expérience, une jointure ralentit une requête. Connaissez-vous les critères / volumes qui font qu'une jointure peut ralentir une requête ? On connaît les stats, index et différentes optimisations de BDD.
• Vaut-il mieux privilégier les tables intermédiaires ou démultiplier les colonnes ? (attention, la question ne porte pas sur le modèle théorique, car on a tous appris en cours qu'il vaut mieux des jointures. On a tous appris que la pratique est différente).
  
  Exemple : une table doit contenir 10 références au maximum : vaut il mieux une table intermédiaire permettant d'avoir un nombre de références infini, ou stocker les 10 références dans la première table dans 10 colonnes, sachant que certaines ne seront pas toujours utilisées.
• un index sur une chaine est-il aberrant ou faut il systématiquement créer une colonne numérique et l'indexer, quitte à devoir créer une table intermédiaire sur ce champ numérique ?
  Exemple :
  Table A
  clé Char(3) indexée
  
  ou faire plutôt
  
  Table A
  clé étrangère tableB.clé INT
  
  Table B en jointure
  clé primaire int
  colonne CHAR(3)
  
• Une requête sur 3 tables (relation 1,1) est-elle aussi rapide qu'une relation entre deux tables (1,1) ?

Je me répète, mais notre seul souci est la performance, nous préférons a priori dénormaliser au maximum (tout en gardant des tables maintenables).

Nous ne prévoyons pas d'attaquer directement nos tables en lecture, mais comptons privilégier les vues.

Bref, avec-vous des expériences de projets à gros volumes dans lesquels l'utilisation de tables intermédiaire vous a causé des problèmes de ralentissement ?

Merci d'avance !

[vmolines]

Voir cette discussion qui touche au même sujet : http://www.developpez.net/forums/d62...isation-table/

[SQLpro]

Bonjour tout le monde,

mes collègues et moi sommes en train de concevoir une application, plus précisément l'architecture de la base de données :

• beaucoup d'entrées (40 tables principales qui gravitent autour de 2 tables à 4 millions d'entrées au début)
• accès en lecture doit être très rapide
• accès en écriture doit être très rapide
• multi-sgbd (avec priorité à Oracle 11g)
• beaucoup d'accès concurentiels
• nombre d'users relativement conséquent (300 simultanés)

Qu'appellez vous beaucoup d'entrées ? Des lignes ?? Quel volume ???

Il est difficile d'optimiser les deux à la fois (lecture et écriture), mais il faut privilégier TOUJOURS la rapidité des écritures car elles sont bloquantes (verrous exclusif) alors que les lectures le sont moins (verrous partagé) voire pas du tout (READ UNCOMITTED, ou isolation SNAPSHOT).

Notez qu'en indexant correctement pour le SELECT on améliore aussi les performance des INSERT, UPDATE et DELETE, car avant de mettre à jour, il faut se positionner au bon endroit !!!

La seule manière d'être réellement multi SGBDR avec des performances est de tout développer sous forme de procédure stockées et vues. En effet, même de simples requêtes comme SELECT COUNT(*) / 2 ne donnent pas les mêmes résultats sur Oracle et sur les autres SGBDR ! (je peaufine un article là dessus...).

300 utilisateur simultané c'est pas grand chose pour un SGBDR actuellement. En concurrence de transactions aujourd'hui on est capable de monter à plusieurs millions de transactions par minutes.... !
Encore faut-il que l'architecture de la BD et les ressources de la machine aient été bien pensée ! Malheureusement c'est rarement le cas en pratique...

Notre question porte sur les tables intermédiaires:
[LIST][*]par expérience, une jointure ralentit une requête. Connaissez-vous les critères / volumes qui font qu'une jointure peut ralentir une requête ? On connaît les stats, index et différentes optimisations de BDD.

Là je ne peut pas vous laisser dire cela qui est TOTALEMENT FAUX !!!

Ce site regorge d'exemples de base aux performances catastrophiques parce que l'on a créé des tables fourre-tout croyant augmenter les performances !
Un seule exemple : http://www.developpez.net/forums/d62...le/#post957726

Lisez les articles que j'ai écrit sur l'optimisation : http://sqlpro.developpez.com/optimisation/ Ce sont des articles généraux qui s'appliquent à tous les SGBDR malgré que je travaille essentiellement sous MS SQL Server.

Le simple bon sens suffit en général à comprendre pourquoi :

Si je créé une table contenant 50 colonnes, avec par exemple les coordonnées de clients comme :
nom,
prenom,
tel1,
tel2,
tel3,
Fax,
GSM,
AdresseLivraison1,
AdresseLivraison2,
AdresseLivraison3,
AdresseLivraison4,
CPLivraison,
VilleLivraison,
AdresseFacturation1,
AdresseFacturation2,
AdresseFacturation3,
AdresseFacturation4,
CPFacturation,
VilleFacturation
...

Chaque fois qu'une mise à jour doit être faite, comme par exemple changer un n° de téléphone; il faut verrouiller TOUTE LA LIGNE de manière exclusive. Ainsi plus aucun processus concurrent ne peut accéder ni en lecture, ni en écriture.

D'autre part, les recherches sont beaucoup moins performantes dans un tel modèle... Par exemple, si vous voulez rechercher un client par son un n° de téléphone, la requête à écrire est complexe et systématiquement lente. la voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT ...
FROM   Matable
WHERE  tel1 = '0123456789'
   OR  tel2 = '0123456789'
   OR  tel3 = '0123456789'
   OR  Fax  = '0123456789'
   OR  GSM  = '0123456789'

En effet en présence du OR dans la clause WHERE le prédicat de recherche n'est pas "sargeable" (voir ce terme : http://sqlpro.developpez.com/cours/quoi-indexer/#LVI-B) et seul, un balayage complet de la table permet de répondre à cette requête. Vous pourrez poser tous les index de la terre, cela ne servira à rien !

En revanche si vous avez créé une table de téléphone avec un type de téléphone avec une jointure vers la table des clients, la requête, même avec sa jointure n'utilise que 2 index et donne instantanément le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT ...
FROM   MatableClient AS T
       INNER JOIN MatableTelephone AS T
             ON T.CLI_ID = T.CLI_ID --> "sargeable"
WHERE  tel = '0123456789' --> "sargeable"

Elle est plus simple à écrire et peut être rendue encore plus simple encore si vous utilisez des vues pour ce faire.

De plus, vous devez tenir compte d'un facteur plus important encore...
Les SGBDR travaillent exclusivement en RAM. Les données sont manipulées en mémoire, jamais depuis le disque. Or la mémoire n'est pas extensible. Pour ce faire le SGBDR dispose d'une gestion de cache, basé sur un algorithme, généralement LRU.
En faisant une seule grosse table, vous obligez à placer des lignes gigantesquement longues en RAM, alors qu'il y a peu de chance que que l'on requêtes simultanément les 80 colonnes...
Autrement dit vous encombrez le cache pour rien.

Pire...
Puisque beaucoup de requête ne seront pas "sargeable", il faudra placer la totalité de la table en RAM (du fait du balayage des lignes) au détriment de toutes les autres données.
Alors que si vous aviez fait des tables fines, il y aurait eu peu de données en cache : seule les quelques pages contenant les quelques lignes d'une recherche dans un index aurait été en RAM, ce qui permet paradoxalement à beaucoup plus de données réellement exploitées d'y tenir.
En sus, et 2e paradoxe, ce type de tables est moins gourmand en volume, car on ne stocke que peut de NULLs inutiles. Par exemple si un client n'a pas de téléphone, le modèle ne stocke rien, alors que la table obèse, oui !

Vous avez dit que les jointures sont couteuses... C'est vrai si vous faites n'importe quoi à ce sujet. Mais c'est complément faux si vos clef sont biens pensées.

Par exemple si vous utilisez des clefs primaires de type varchar ou GUID alors oui, les performances seront catastrophique, mais dans tous les cas quelque soit le modèle (table obèses ou fine) car la fragmentation des index sous-jacent aux clefs va être immédiate et le poids de la jointure avec une telle longueur de clef, très pénalisante.

En revanche si vous utilisez de l'entier auto incrémenté (avec une longueur calée sur la longueur du mot du processeur - donc 32 ou 64 bits) vous n'aurez ni fragmentation ni cout de jointure prohibitif.
Pour information, sur MS SQL Server, le cout d'une jointure de 30 tables de 100 millions de lignes sur des clefs 32 bits devant ramener une ligne en final, ne coute que 180 pages, c'est à dire au plus 3 pages par index (et comme il y a 30 paires d'index à joindre : 30 * 2 * 3 = 180) ce qui est très modeste...

[*]Vaut-il mieux privilégier les tables intermédiaires ou démultiplier les colonnes ? (attention, la question ne porte pas sur le modèle théorique, car on a tous appris en cours qu'il vaut mieux des jointures. On a tous appris que la pratique est différente).

Visiblement vous n'avez jamais mis en pratique correctement !

Pour ma part, lorsque je fais des audits, je pointe du doigt les tables de plus de 40 colonnes.

Lorsque je modèlise je m'interdit, sauf cas très exceptionnel des tables de plus de 25 colonnes...

Exemple : une table doit contenir 10 références au maximum : vaut il mieux une table intermédiaire permettant d'avoir un nombre de références infini, ou stocker les 10 références dans la première table dans 10 colonnes, sachant que certaines ne seront pas toujours utilisées.

Même pour 2 lignes, le table de référence est toujours payante !

[*] un index sur une chaine est-il aberrant ou faut il systématiquement créer une colonne numérique et l'indexer, quitte à devoir créer une table intermédiaire sur ce champ numérique ?
Exemple :
Table A
clé Char(3) indexée

ou faire plutôt

Table A
clé étrangère tableB.clé INT

Table B en jointure
clé primaire int
colonne CHAR(3)

Une clef de type chaine de caractères est plus complexe et plus lente à gérer car il faut tenir compte de certains paramètres logique que les nombres n'ont pas. Par exemple quid de la casse ? Quid des accents ??? C'est la gestion de la collation qui va ralentir la comparaison.

Une requête sur 3 tables (relation 1,1) est-elle aussi rapide qu'une relation entre deux tables (1,1) ?

Difficile de vous comprendre...

Je me répète, mais notre seul souci est la performance, nous préférons a priori dénormaliser au maximum (tout en gardant des tables maintenables).

Non, c'est la mauvaise technique. Lisez la longue discussion que nous avons eu sur ce sujet il y a déjà quelques années.
http://www.developpez.net/forums/d62...isation-table/
Au final tout le monde est d'accord pour dire que que la dé-normalisation préventive est le meilleur gage d'une catastrophe en terme de performances !
Avant de dé normaliser il faut prouver par des mesures sur des volumes de données proche de la réalité que le gain est supérieur à la perte que vous allez immanquablement avoir d'autres côtés, et de pas le faire que si le SGBDR est capable de l'automatiser par exemple à l'aide de colonnes calculées, index calculées, vues indexées, partitionnement de données et autres techniques que le SGBDR assume, de manière synchrone.

Nous ne prévoyons pas d'attaquer directement nos tables en lecture, mais comptons privilégier les vues.

C'est une bonne chose. Tant qu'a faire, faites aussi vos mise à jours directement dans ces vues...
Et si vous voulez de réelles performances, alors développez en SGBDR épais.

Bref, avec-vous des expériences de projets à gros volumes dans lesquels l'utilisation de tables intermédiaire vous a causé des problèmes de ralentissement ?

[/quote]
Non, c'est le contraire...
Quelques unes de mes expériences

1) modélisation de la gestion des crues du grand delta du Rhône pour la DDE du Gard

Système critique de gestion des cours d'eau de Lyon à la méditerranée devant intervenir en cas d'épisode de crues.
Insertion de mesures physique toute les 5 minutes de 300 stations comportant de1 à 3 capteurs (antécédent de 10 ans) => table comportant plusieurs centaines de millions de lignes.

Aucune table ne fait plus de 25 colonnes.
Moyenne du nombre de colonne 7.
Temps de réponse moyen : de l'ordre d'une centaines de ms, même pour des requêtes portant sur des millions de lignes (utilisation de vues indexées)

Toutes les clefs sont en INT32 sauf la table des mesure INT64.

2) modélisation de l'ERP de Santé Service (Hospitalisation à Domicile, équivalent d'un CHU en nombre de patient)

Nombre de tables : 370
Nombre moyen de colonnes : 8
Développement en SGBDR, épais à base de vue et proc Stock uniquement


3) Modélisation d'un moteur de text mining pour la recherche (Sté Intellixir)

indexation de mots et d'expression pour l'établissement de recherches complexes.
Résolution de la recherche du LIKE '%toto%' en performance linéaire (presque aussi rapide qu'une recherche de mot sans joker.)

Moyenne de la longueur des tables 5 colonnes !

Merci d'avance !

La seule chose important : modéliser des données, c'est un métier... Cela s'apprend et il faut les outils adéquats !

A +

[Halleck]

Merci beaucoup pour vos réponses, on est épaté ! Très grand merci à toi SQLpro !

On va bien prendre le temps de lire tout ça, et si besoin on reviendra ici.

Je passe le sujet en résolu pour l'instant.

Encore merci !