Discussion :

[Ikebukuro]

Bonjour,

Je suis en train de lire un bouquin sur la modélisation de bases de données et on mentionne la réticence de certains informaticiens à créer N tables car cela crée M jointures et ils disent que les performances en souffrent. Si un développeur me dit que les jointures c'est mal et qu'il préfère une table fourre-tout avec 200 colonnes, je vais lui répondre que ce sera mauvais car :
1) certaines données seront redondantes et donc cela prendra plus de temps pour les mettre à jour
2) lors d'un update un verrou sera posé sur chaque ligne mise à jour et donc ça va bloquer les autres transactions qui veulent mettre à jour les autres colonnes alors qu'avec N tables, on bloque seulement 1 table et donc N-1 tables sont accessibles elles aussi pour les mises à jour.

En revanche, s'il me dit qu'une jointure entre deux champs VARCHAR2(20) sur deux tables est très consommateur de ressources car il faut vérifier chaque caractère et qu'il préfère une seule table, je lui réponds quoi?
Je pourrais dire qu'on va pas vérifier 40 caractères à chaque jointure car dès qu'un caractère n'est pas correct on arrête la comparaison et on passe à la ligne suivante; du moins j'espère que c'est codé comme cela.

Bon, si vous avez des idées, des arguments imparables je suis preneur.

[SQLpro]

C'est évidemment une grosse connerie mais Internet permet aux crétins d'exposer leurs points de vue...
Or comme je l'ais dit dans un audit chez un opérateur de telecom, les moteurs de recherches indexant les articles selon leurs popularité, on trouve évidemment plus d'article traitants des "poils de cul de Paris Hilton" (259 000 réponses) que des "jointures sql et performances (159 000).
Bref, même avec cette dernière requête Google, vous n'aurez sans doute pas en premier les réponses les plus adéquates, mais les plus lues....

Lisez donc de que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p1...mances_petites

A +

[Ikebukuro]

Salut SQLpro, je te remercie vivement pour ta réponse car il tape en plein dans ma question
J'imprime de suite ton article et je te fais un retour au cas où je ne comprendrais pas certaines choses.

[EDIT]
Ca y est, je l'ai lu et ton article est passionnant; j'ai beaucoup aimé l'argument disant que la jointure étant l'opération la plus fréquente, c'est la plus optimisée dans un SGBDR.

Mais bien entendu j'ai plusieurs questions :
1) Je ne comprends pas cette phrase : "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"
J'ai tab1(col1, col2, col3) avec col1 en PK et tab2(col3, col4, col5) avec col3 en PK et que je fais un SELECT T1.col2, T1.col3, T2.col4 FROM tab1 T1, tab2 T2 WHERE T1.col3=T2.col3 AND T1.col1 = 500000;
Pour cet ordre SQL je comprends qu'il y a les opérations suivantes :
- parcours de l'index de tab1 sur col1 : lecture du bloc racine, des blocs branches puis du bloc feuille où se trouve col1=500000 et lecture du rowid. Je pense qu'on lit 4 blocs : racine + 2 branches + feuille pour un index de profondeur 4.
- lecture de la table tab1 par rowid pour récupérer col2 et col3. On lit un bloc.
- une fois col3 récupéré dans tab1, je parcours cette fois l'index de tab2 sur col3, soit le chemin racine, branches puis du bloc feuille où se trouve tab2.col3=tab1.col3 et lecture du rowid. Je pense qu'on lit 4 blocs : racine + 2 branches + feuille pour un index de profondeur 4.
- lecture de la table tab2 par rowid pour récupérer col4. On lit un bloc.

Pour une table de 100 000 000 de lignes, je trouve que le chiffre de 6 pages est très optimiste car moi j'en suis à 10... Par "page" tu entends "bloc Oracle" ou "bloc du système d'exploitation"?

2) "Toutes les opérations relationnelles dans une base de données sont faites exclusivement en mémoire." : par l'expression "opérations relationnelles", tu entends quoi SQLpro? J'ai l'impression que c'est un terme flou qui peut englober beaucoup de choses.

3) Quand on fait un FULL TABLE SCAN, es-tu sur que TOUTES les pages (bloc?) doivent être placées en mémoire? ET doivent-elles TOUTES le rester le temps de traitement de la requête?

4) "Le principe est qu’une ligne d’une table doit impérativement tenir intégralement dans une page." "En effet si vous construisez une table avec une taille de ligne moyenne de 4500 octets, alors vous n’aurez jamais plus d’une ligne par page et perdrez par conséquent plus de 3500 octets qui seront inutilisés."
Je travaille sur Oracle et il y a le mécanisme de chaînage de lignes qui, pour une ligne ne tenant pas dans un bloc, permet de la répartir sur N blocs. Dans le cas où une ligne fait 4500 octets et qu'on a un DB_BLOCK_SIZE de 8ko et un PCTFREE de 10% (0.8Ko), il reste donc 8000 - 4500 - 800 = 2700 octets pour stocker une partie du deuxième enregistrement donc même avec une table obèse on peut maximiser le remplissage d'un bloc.

Désolé pour la longueur du post mais à article intéressant, commentaires nombreux

[SQLpro]

1) Je ne comprends pas cette phrase : "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"
J'ai tab1(col1, col2, col3) avec col1 en PK et tab2(col3, col4, col5) avec col3 en PK et que je fais un SELECT T1.col2, T1.col3, T2.col4 FROM tab1 T1, tab2 T2 WHERE T1.col3=T2.col3 AND T1.col1 = 500000;
Pour cet ordre SQL je comprends qu'il y a les opérations suivantes :
- parcours de l'index de tab1 sur col1 : lecture du bloc racine, des blocs branches puis du bloc feuille où se trouve col1=500000 et lecture du rowid. Je pense qu'on lit 4 blocs : racine + 2 branches + feuille pour un index de profondeur 4.
- lecture de la table tab1 par rowid pour récupérer col2 et col3. On lit un bloc.
- une fois col3 récupéré dans tab1, je parcours cette fois l'index de tab2 sur col3, soit le chemin racine, branches puis du bloc feuille où se trouve tab2.col3=tab1.col3 et lecture du rowid. Je pense qu'on lit 4 blocs : racine + 2 branches + feuille pour un index de profondeur 4.
- lecture de la table tab2 par rowid pour récupérer col4. On lit un bloc.

Pour une table de 100 000 000 de lignes, je trouve que le chiffre de 6 pages est très optimiste car moi j'en suis à 10... Par "page" tu entends "bloc Oracle" ou "bloc du système d'exploitation"?

A l'époque ou j'ai écrit cet article mes tests étaient sur SQL Server.
1) taille des pages (appelés bloc dans Oracle, mais page dans tous les autres SGBDR - encore une singularité à la Oracle...) 8 Ko et 3 pages suffisent pour indexer 100 millions d'id de type INT
2) particularité de SQL Server : par défaut les tables sont des IOT sur la PK donc pas de double lecture => 3 + 3 = 6 !

2) "Toutes les opérations relationnelles dans une base de données sont faites exclusivement en mémoire." : par l'expression "opérations relationnelles", tu entends quoi SQLpro? J'ai l'impression que c'est un terme flou qui peut englober beaucoup de choses.

Toutes les opérations de l'algèbre relationnelle, c'est à dire tout ce qui fait le SELECT à l'exception du tri (ORDER BY) qui est une opération physique.

3) Quand on fait un FULL TABLE SCAN, es-tu sur que TOUTES les pages (bloc?) doivent être placées en mémoire? ET doivent-elles TOUTES le rester le temps de traitement de la requête?

Comme toutes les opérations logiques sont faites en mémoire, oui, toutes les pages d'un FULL SCAN doivent être montées en RAM. Cependant cela ne veut pas dire qu'elle vont y résider ni même qu'elles doivent toutes être simultanément en mémoire !
Il y a bien longtemps que les SGBDR savent faire des opérations asynchrone d'IO et même du parallélisme (par défaut dans SQL Server mais payant dans Oracle !!!)

4) "Le principe est qu’une ligne d’une table doit impérativement tenir intégralement dans une page." "En effet si vous construisez une table avec une taille de ligne moyenne de 4500 octets, alors vous n’aurez jamais plus d’une ligne par page et perdrez par conséquent plus de 3500 octets qui seront inutilisés."
Je travaille sur Oracle et il y a le mécanisme de chaînage de lignes qui, pour une ligne ne tenant pas dans un bloc, permet de la répartir sur N blocs. Dans le cas où une ligne fait 4500 octets et qu'on a un DB_BLOCK_SIZE de 8ko et un PCTFREE de 10% (0.8Ko), il reste donc 8000 - 4500 - 800 = 2700 octets pour stocker une partie du deuxième enregistrement donc même avec une table obèse on peut maximiser le remplissage d'un bloc.

Je ne sais pas exactement ce qu'il en est d'Oracle, mais je pense que c'est similaire à SQL Server.
1) oui une taille de ligne peut dépasser la taille de la page et il y a chainage. Mais c'est une aberration en particulier pour les performances. Il vaudrait mieux prévoir des LOBs dont la gestion est particulière
2) le PCT FREE (appelé FILL FACTOR dans tous les autres SGBDR - encore une singularité à la Oracle...) n'est appliqué que pour la fin de la page et ne sera d'aucune utilité dans un cas pareil.
3) non, le début de la nouvelle ligne sera dans une nouvelle page car cela permet de réduire le nombre d'IO lorsque l'on recherche 1 seule ligne, voir quelques lignes éparses (cas les plus fréquents).

Désolé pour la longueur du post mais à article intéressant, commentaires nombreux

Pas de quoi...

Faites des tests !

A +

[pachot]

Bonjour,

Déjà la réponse est dans votre question. Vous parlez des problèmes d'update lorsqu'on dénormalise, et les histoires de performances parlent de requêtes (lecture).
Effectivement, devoir faire une jointure est plus couteux que d'avoir tout le résultat dans dans une table. Mais avant de dénormaliser le modèle il faut voir les structures qui permettent de stocker des résultats dénormalisés pour optimiser les requêtes qui en ont besoin.

Avec Oracle, il y a le cluster qui permet de stocker ensemble les lignes de plusieurs tables. Et il y a les vues matérialisées qui stockent le résultat d'une jointure, en plus des tables.

Mais avant il faut savoir si c'est nécessaire. Des jointures sur quelques lignes, en passant par index, c'est quelques blocs supplémentaires à lire. On peut déjà optimiser les index pour ne pas avoir à aller voir la table. On peut utiliser result cache pour les parties de requêtes qui touchent des tables statiques.
Des jointures entre grosses tables, on peut déjà optimiser la jointure avec assez de mémoire pour le hash join.

Ce qu'il faut comprendre, c'est que dénormaliser permet d'optimiser une seule requête (celle qui a besoin de toutes ces lignes et toutes ces colonnes). Mais d'autres requêtes qui ont besoin de moins de colonnes seront moins performantes.
Le modèle normalisé, avec les bons index et éventuellement des vues matérialisées pour le reporting, permet d'être optimal pour un grand nombre de requêtes. et en général c'est ce qu'on veut sur une base qui est utilisée par de nombreux use-cases différents.

Cordialement,
Franck.