Performance VS normalisation

Version imprimable

17/06/2011, 14h01
mikaeru

Performance VS normalisation

Bonjour,
Tout est dans le titre :aie:, 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.
17/06/2011, 18h31
SQLpro

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…

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

Citation:

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 +
17/06/2011, 19h49
Oishiiii

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.

;)
17/06/2011, 21h44
SQLpro
Citation:

Envoyé par Oishiiii

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:

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 +
16/08/2011, 23h15
Sergejack

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.
16/08/2011, 23h57
Oishiiii

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

Démoralisez jusqu'au bout (une vie sans NULL donc)

Ne soyez pas démoralisé, on vit très bien sans NULL :lol:

Citation:

Envoyé par Sergejack

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

(une jointure est moins rapide qu'un accès direct)

J'en mettrais pas ma main à couper :D
17/08/2011, 21h02
fsmrel

Bonsoir,

Citation:

Envoyé par Sergejack

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

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.
20/08/2011, 10h35
Luc Orient

Citation:

Envoyé par SQLpro

..
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 ...
25/08/2011, 16h34
fbms18

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 ?
25/08/2011, 17h59
fsmrel

Citation:

Envoyé par fbms18

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

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...
26/08/2011, 09h14
fbms18

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.