IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Optimisations SGBD Discussion :

Performance VS normalisation


Sujet :

Optimisations SGBD

  1. #1
    Membre régulier
    Homme Profil pro
    Inscrit en
    Novembre 2007
    Messages
    125
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Japon

    Informations forums :
    Inscription : Novembre 2007
    Messages : 125
    Points : 92
    Points
    92
    Par défaut Performance VS normalisation
    Bonjour,
    Tout est dans le titre , A quel moment doit-on pousser vers la normalisation ou vers la simplification d'une requête.

    Prenons le cas d'un client qui aurait pourrait avoir potentiellement 4 prénoms.

    Bien qu'on pourrait avoir un gain en espace en normalisant le tout (CAD : avoir une table client, une table prénom et une table qui lierait les deux [avec une colonne ‘ordre’]

    Est-ce vraiment plus performant de complexifier la requête de lecture?

    A vos clavier, et comme d’habitude je vous remercie du plus profond de mon cœur pour vos lumière.


    Et bon w-e.

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 741
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 741
    Points : 52 454
    Points
    52 454
    Billets dans le blog
    5
    Par défaut
    Contrairement à une idée reçue, la normalisation implique la performance. Ce n'est donc pas l'un contre l'autre !

    Voici ce que j'ai mis dans un audit récent et que je répète dans presque tous mes audits :

    *********

    La normalisation d'une base de données (c'est-à-dire le respecte des règles de modélisation) n'est pas une figure de style. C'est, avant tout, une question de performance !

    Le non respect des formes normales, ce qui est le cas ici, conduit systématiquement à décrochage des performances dès que le volume des données de la base dépasse la quantité de RAM.
    L'application du processus de normalisation, conduit à un grand nombre de tables avec très peu de colonnes (en moyenne moins d'une dizaine) et de nombreuses jointures doivent être réalisées pour retrouver les données. Les jointures, tout particulièrement lorsqu'elles portent sur des "petites" clefs (comme un entier avec l'auto incrément IDENTITY) sont des processus d'une extrême rapidité (c'est l'opération la plus courante dans un SGBDR, donc la plus optimisée). Par exemple, avec deux tables de 100 millions de lignes, une jointure qui au final renvoie une ligne ne coutera que la lecture de 6 pages, ce qui est très peu.


    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.
    • Dans un jeu de plusieurs petites tables représentant une seule et même grosse table, les opérations d'écriture se succéderons séquentiellement et tandis que l'un est mis a jour avec un verrou exclusive, les autres peuvent être utilisés en lecture comme en écriture.
    Finalement plus d'utilisateurs peuvent travailler simultanément sans être victimes de temps d'attente importants dans une base constituée de nombreuses petites tables.


    Différences entre des petites tables et une grosse table pour les opérations de lecture :
    • Dans une table, qu'elle soit petite ou grande, une seule méthode doit être choisie pour accéder aux données :
    >>> balayage de toutes les lignes de la table;
    >>> balayage de toutes les lignes d'un index;
    >>> recherche dans un index;
    • Une fois qu'une grande table a été morcelée en plusieurs petites tables, l'optimiseur peut choisir la meilleure méthode d'accès entre balayage et recherche en préférant la recherche aussi souvent que possible.
    • S'il existe plusieurs prédicats de recherches ou plusieurs conditions dans le prédicat, la seule manière d'opérer dans une grande table est de balayer toute la table.
    • Bien entendu les recherches sont incommensurablement plus rapides que les balayages parce que leur coût est logarithmique.
    Finalement et malgré le coût des jointures, une requête avec plusieurs prédicats ou conditions s'exécutera beaucoup plus rapidement dès que le volume de données commence à peser d'un poids important. Et plus les requêtes "roulent" vite, plus un grand nombre d'utilisateurs peuvent utiliser simultanément le système…

    Toutes les opérations relationnelles dans une base de données sont faites exclusivement en mémoire. Aussi quelque soit la méthode d'accès aux données, toutes les données nécessaires à la requête doivent être placées en RAM avant d'être lues.
    • Avec un balayage, toutes les lignes de la table doivent être placées en mémoire
    • Avec une recherche, très peu de pages doivent être placées en mémoire, parce qu'un index est un arbre et qu'il suffit d'y placer la page racine, les pages de navigation et la page feuille contenant les données finales.
    Bien entendu un balayage place un grand nombre de page en mémoire, tandis qu'une recherché en place très peu.
    Dans une base mal modélisée, le résultat est une consommation anormale de RAM du fait de nombreux balayage. Dans un tels cas, la solution consiste à avoir une RAM sur le serveur égal à la taille de la base ou bien de restructurer la base !
    Le problème est que restructurer la base nécessite une refonte complète du développement, si les développeurs n'ont pas prévu au départ d'utiliser des vues…

    ***********************

    Est-ce vraiment plus performant de complexifier la requête de lecture?
    Point n'est besoin de la complexifier pour le développeur si vous avez créé des vues. Le développeur n'y VERRA que du feu !
    C'est fait pour ça les vues....


    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre éprouvé Avatar de Oishiiii
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2009
    Messages
    508
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Août 2009
    Messages : 508
    Points : 1 104
    Points
    1 104
    Par défaut
    Bonsoir,

    Comme l'explique SQLPro, normalisation est très souvent (pour ne pas dire toujours) synonyme de performance.

    Mais au-delà de l'aspect performance, on ne "normalise" pas ses tables uniquement pour le plaisir.

    Une conception avertie de la base de données, suivie d'un contrôle des tables potentiellement critique à la lumière du processus de normalisation permet d'avoir un contrôle total sur la qualité des données.
    On élimine d'éventuelles redondances d'information ainsi que des anomalies lors des opérations de mises à jour. Sans oublier la grande évolutivité qu'offre ces petites tables ayant chacune un sens bien précis.

    La "complexité" des requêtes est toute relative. Une fois que l'on a bien apprivoisé le langage SQL, en travaillant avec rigueur (je pense notamment aux conventions de nommage des objets) il n'y a pas vraiment de difficulté à manipuler de nombreuses tables.
    L'utilisation de vues masquera cette "complexité" aux utilisateurs moins familier ou allergiques au SQL.


  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 741
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 741
    Points : 52 454
    Points
    52 454
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Oishiiii Voir le message
    Une conception avertie de la base de données, suivie d'un contrôle des tables potentiellement critique à la lumière du processus de normalisation permet d'avoir un contrôle total sur la qualité des données.
    Et pour complété, la maîtrise de la qualité des données induit automatiquement de bien meilleures performances.

    Un simple exemple...
    Si vous ne maîtrisez pas la saisie que font les utilisateurs dans un "champs" n° de téléphone, vous aboutirez au final à ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    01 47 58 42 23
    0645145874
    01-22-44-25-36
    +33 6 52418552
    Et maintenant la requête qui tue....
    Retrouvez moi le client qui à comme n° de téléphone 06 52 41 85 52...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Membre éprouvé

    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Juillet 2006
    Messages : 1 448
    Points : 1 234
    Points
    1 234
    Par défaut
    Je ne suis pas d'accord.

    Quant il y a une relation 1 - 1 entre une ligne (un utilisateur par exemple) et une valeur simple (le second prénom facultatif par exemple), il y a bien lieu de se poser la question.

    Je ne suis pas convaincu qu'il y ait forcément une perte de performance à ne pas normaliser à ce point (note: ce point c'est le point où vous n'utilisez jamais NULL), je pense même le contraire (une jointure est moins rapide qu'un accès direct).
    En plus de l'intérêt aux performances, il faut aussi s'intéresser au code : avons nous vraiment envie de se compliquer le query à ce point pour du nano tunning (dont, j'ai mis en doute le bien fondé) ?


    Démoralisez jusqu'au bout (une vie sans NULL donc) n'est ni une chose que je voudrais faire, ni même une chose avec laquelle je voudrais faire.


    Si je me trompe, j'invite un volontaire à faire une table d'utilisateur dont chaque champs à sa propre table.
    Càd : table Prénom, table nom, table date de naissance...
    Si un gain de performance se vérifiais, alors j'aurais eu tort (et je mange mon chapeau), en cas de perte par contre, j'aurais alors vraisemblablement raison.
    Most Valued Pas mvp

  6. #6
    Membre éprouvé Avatar de Oishiiii
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2009
    Messages
    508
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Août 2009
    Messages : 508
    Points : 1 104
    Points
    1 104
    Par défaut
    Il arrive que l'on "sorte" une colonne d'une table afin d'éviter que celle-ci soit polluée par des NULL. C'est une méthode de gestion de l'information manquante tout à fait légitime permettant d'éviter NULL qui est source d’ambiguïté, d'erreur.

    Est-ce qu'une requête comportant une jointure peut réellement être qualifiée de "complexe" ?

    Si réellement la requête est trop complexe, faites en une vue comme on l'a déjà évoqué.

    Citation Envoyé par Sergejack Voir le message
    Démoralisez jusqu'au bout (une vie sans NULL donc)
    Ne soyez pas démoralisé, on vit très bien sans NULL

    Citation Envoyé par Sergejack Voir le message
    Si je me trompe, j'invite un volontaire à faire une table d'utilisateur dont chaque champs à sa propre table.
    Càd : table Prénom, table nom, table date de naissance...
    Si un gain de performance se vérifiais, alors j'aurais eu tort (et je mange mon chapeau), en cas de perte par contre, j'aurais alors vraisemblablement raison.
    J'aurais la démarche inverse...
    - La conception de ma base de donnée peut m’amener à créer des tables pour des informations qui aurait pu être NULL.
    - J'estime la volumétrie de la base de donnée après 3-4 ans de vie par exemple.
    - Je crée un jeu d'essai, je réalise un prototype.
    - Je test les requêtes potentiellement problématiques
    - Je pose les indexs pertinents

    Seulement après avoir diagnostiqué un problème de performance, et seulement en étant certain que ramener une colonne dans une table avec introduction de NULL est nécessaire, je modifierai une table.

    Je ne ferais pas de compromis sur la qualité des données et des requêtes parce qu'a priori les performances seraient moins bonnes (je ne suis pas à quelques microsecondes près...).

    PS: NULL n'est-il pas à l'origine d'une grande complexité par rapport à l'opérateur de jointures dans nos requêtes? Utilisation de l'opérateur IS NULL à la place de l'égalité; fonctions particulières (COALESCE(), etc), comportement particulier avec certains opérateurs (Count(), etc)....

    Citation Envoyé par Sergejack Voir le message
    (une jointure est moins rapide qu'un accès direct)
    J'en mettrais pas ma main à couper

  7. #7
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 787
    Points
    30 787
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par Sergejack Voir le message
    une jointure est moins rapide qu'un accès direct
    Par « accès direct » vous voulez probablement dire accès à une table unique par opposition à un accès à deux tables, conséquence de la jointure. S’il en est ainsi, vous pouvez avoir raison comme vous pouvez avoir tort et SQLpro dit bien pourquoi.

    Oishiiii accepte à juste titre de perdre quelques microsecondes et il a bien raison. Maintenant, quand vous dites qu’« une jointure est moins rapide qu'un accès direct », votre jugement est pour le moins subjectif, à l’instar de tout ce qui a été écrit à ce sujet depuis trente ans dans la presse du cœur informatique et se répète dans les cafétérias. Rien de tel que d’expérimenter et je vous renvoie à ce que j’ai pu observer dans une banque d'une ville méridionale, voyez ici.


    Citation Envoyé par Sergejack Voir le message
    Si je me trompe, j'invite un volontaire à faire une table d'utilisateur dont chaque champs à sa propre table.
    Càd : table Prénom, table nom, table date de naissance...
    Si un gain de performance se vérifiais, alors j'aurais eu tort (et je mange mon chapeau), en cas de perte par contre, j'aurais alors vraisemblablement raison.
    Commencez par fournir la structure précise de vos tables. Vu ce que vous écrivez, on peut subodorer que vous vous embarquez plein pot dans de sordides problèmes d’I/O bound.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  8. #8
    Membre expert
    Homme Profil pro
    Retraité
    Inscrit en
    Octobre 2005
    Messages
    1 473
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 65
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Finance

    Informations forums :
    Inscription : Octobre 2005
    Messages : 1 473
    Points : 3 283
    Points
    3 283
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    ..
    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.
    Tout dépend de la granularité du verrouillage ...

    Si le verrou est posé sur la page (cas le plus fréquent en DB2 for z/OS par exemple), l'argument ne tient plus voire même s'inverse ...

    Pour un verrouillage sur la ligne, les deux cas me semblent équivalents ...

  9. #9
    Membre à l'essai
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2011
    Messages
    16
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Service public

    Informations forums :
    Inscription : Avril 2011
    Messages : 16
    Points : 20
    Points
    20
    Par défaut
    J'apporte ici plus un questionnement qu'une réponse, n'étant pas expert.

    J'ai suivi plusieurs formations sur la mise en place d'entrepôt de données et ce que j'en ai retenu de chacune et que tous ont rabâchés c'est que pour augmenter les temps de traitements et donc l'accès aux données il faut complétement dé-normaliser ses schémas et multiplier les références dans les tables pour accélérer les traitements. Je pense pas qu'ils ont sortis tout cela de leurs chapeaux et qu'ils doivent bien se baser sur des faits ? Ce qui se vérifierait donc pour les entrepôts de données ne serait-il pas applicables pour nos bases de données ?

  10. #10
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 787
    Points
    30 787
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par fbms18 Voir le message
    J'ai suivi plusieurs formations sur la mise en place d'entrepôt de données et ce que j'en ai retenu de chacune et que tous ont rabâchés c'est que pour augmenter les temps de traitements et donc l'accès aux données il faut complètement dénormaliser ses schémas et multiplier les références dans les tables pour accélérer les traitements.
    Ceux qui vous ont asséné cela vous ont-ils apporté les preuves de ce qu’ils avancent ? Savent-ils au moins modéliser aux niveaux (a) conceptuel, (b) logique, (c) physique ? Pour que nous en jugions objectivement, montrez-nous leurs supports de cours à ce sujet.

    En attendant, je vous renvoie à « Normaliser, une obligation ? », à « Retour sur la dénormalisation a priori » , ou encore à « Performance des applications ».


    Citation Envoyé par fbms18 Voir le message
    Ce qui se vérifierait donc pour les entrepôts de données ne serait-il pas applicables pour nos bases de données ?
    Les entreprises qui mettent en place des entrepôts de données (base de données décisionnelles, data warehouse) gèrent normalement deux bases de données, une qui contient les données opérationnelles, l’autre qui contient les données « d’aide à la décision » qui en sont dérivées (agrégats, totaux, moyennes...) et rafraîchies périodiquement.

    La base de données opérationnelle n’a pas à être dénormalisée. Pour ma part, les prototypages de performance que j’ai réalisés pendant plus de trente ans m'ont convaincu qu'il fallait normaliser à fond. Le seul adversaire à m'avoir toujours donné du fil à retordre s'appelle I/O bound et face à lui, dénormaliser ne résout rien...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  11. #11
    Membre à l'essai
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2011
    Messages
    16
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Service public

    Informations forums :
    Inscription : Avril 2011
    Messages : 16
    Points : 20
    Points
    20
    Par défaut
    Je ne pense pas que je puisse diffuser les supports de cours, en attendant rien ne m'empêche de donner le nom, il s'agit de la société DeciVision (http://www.decivision.com)

    Edit : En effet je ne parlais pas de la base de données opérationnelle, mais de la décisionnelle.

Discussions similaires

  1. Performances : normalisation ou pas
    Par aemag dans le forum Administration
    Réponses: 11
    Dernier message: 12/05/2009, 10h27
  2. Hautes exigences de performance et normalisation
    Par grosFab dans le forum Oracle
    Réponses: 9
    Dernier message: 25/08/2006, 12h15
  3. [ POSTGRESQL ] Problème de performance
    Par Djouls64 dans le forum PostgreSQL
    Réponses: 6
    Dernier message: 26/05/2003, 17h18
  4. [JDBC][connexion persistante] performances avec JDBC
    Par nawac dans le forum Connexion aux bases de données
    Réponses: 6
    Dernier message: 06/05/2003, 11h37
  5. performance entre 3DS, ase, asc ...
    Par amaury pouly dans le forum OpenGL
    Réponses: 3
    Dernier message: 24/03/2003, 12h41

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo