Petites tables ou grandes tables
... Quelles conséquences sur les performances des bases de données ? par SQLpro

Les rubriques (actu, forums, tutos) de Développez
Tags
SQL
Réseaux sociaux


 Discussion forum

Le , par SQLpro, Rédacteur
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 +
Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster une réponse

Avatar de fsmrel fsmrel
http://www.developpez.com
Expert Confirmé Sénior
le 25/11/2011 4:22
Bonsoir,

Vouloir tout mettre dans le minimum de tables et se retrouver avec un maximum de colonnes par table est évidemment la source de tous les ennuis (obésité, multiplicité des index (pensons aux conséquences des mises à jour...), mauvaise performance (y-compris pour les tâches de service : réorganisations, sauvegardes, j’en passe et des meilleures), redondances, incohérences, contentions, etc.) Pour assurer le coup, bannissons les « groupes répétitifs » et tant qu’à faire, normalisons au moins en 4NF, là au moins on est sûr de multiplier les tables de peu de colonnes, avec moins d’encombrement, de redondance, de perte de temps en mise à jour, on gagnera en cohérence, on économisera les ressources, etc.

Citation Envoyé par Luc Orient  Voir le message
Avec DB2 for z/OS, on choisit la granularité du verrouillage (ligne, page ou table) avec souvent une préférence pour la page.

Dans un contexte de forte concurrence que sont les systèmes OLTP sur Mainframe, il ne viendrait à l'idée de personne de verrouiller la table en mise à jour pour un seul processus ...

Je confirme !

Citation Envoyé par SQLpro  Voir le message
Pour le ROWID...
Citation Envoyé par Luc Orient  Voir le message
il semble que SQL Server ait fait le choix curieux de stocker la clé primaire comme référence à la ligne dans les index secondaires ...

Ce choix n'est pas curieux il est même très intéressant, c'est la notion d'index cluster...
Effectivement tous les index non cluster reposent sur la valeur de la clef de l'index cluster pour retrouver la ligne originale.

C’est le concept d’index cluster façon SQL Server. Chaque SGBD voit le clustering à sa façon. On a déjà évoqué le sujet ici ou à propos des soixante-sept millions de bovins chers à CinePhil.

Permettez-moi de me citer :

Citation Envoyé par fsmrel  Voir le message
Si j’ai bien compris, et pour résumer : physiquement parlant, avec SQL Server, les données d’une table sont intégrées à l’index cluster, dont elles constituent le niveau feuilles. A cette occasion et par comparaison, je rappelle qu’avec DB2, la table est hébergée dans un table space à part. Reprenons le cas de la recherche d’une vachette. Si l’on effectue un accès selon la séquence cluster, tout le monde a bon, en quatre entrées/sorties (symbolisées par des flèches rouges) on récupère les données de Zaza parmi soixante-sept millions de bovins :




Citation Envoyé par fsmrel
So far, so good. Si l’on effectue maintenant une recherche qui ne met pas directement en jeu l’index cluster, la stratégie n’est plus la même, cf. figure B ci-dessous. Dans tous les cas, le SGBD utilise les services de l’index non cluster qui va bien (s’il existe). Dans un cas, les feuilles de cet index adressent la racine de l’index cluster et dans l’autre cas directement le table space hébergeant la table, d’où un nombre d’entrées/sorties (ou de cailloux pour le Petit Poucet) égal à 8 dans un cas et à 5 dans l’autre cas (avec en l’occurrence un gain en I/O time plus que substantiel, conséquence du court-circuit de l’index cluster).




Citation Envoyé par fsmrel
Est-ce bien cela ? Si ce n’est pas le cas, je referai les dessins.

Si quelqu'un avait la bonté de confirmer ou infirmer...

Citation Envoyé par SQLpro  Voir le message
Cela permet beaucoup de choses, comme par exemple en cas de défragmentation de la table, ne pas toucher aux index !

On en a parlé ici et ce problème a toujours fait l’objet des plus grands soins de la part des SGBD, même bien avant l’arrivée des SGBD relationnels. Par exemple, ADABAS avec ses ISN numbers (~ RID) était exemplaire à ce sujet.
Avatar de pacmann pacmann
http://www.developpez.com
Expert Confirmé
le 25/11/2011 10:33
Salut,

C'est exactement ce que j'essayais de dire... mais c'est vraiment mieux avec les dessins

Et pour refaire un peu de pub, sous Oracle, tu peux choisir si tu veux le modèle SQL Server (Organization = index) ou DB2 (Organization = heap).
Avatar de CinePhil CinePhil
http://www.developpez.com
Modérateur
le 25/11/2011 10:51
Merci François de m'avoir raffraichi la mémoire sur ce vieux cours que tu avais donné lorsque je travaillais sur les bovins. On peut dire que j'avais carrément tout oublié !

Il reste cependant des points obscur mais je ne suis pas sûr que ce soit le lieu pour les aborder.

En fait, il faudrait carrément faire un article sur le fonctionnement des index dans les SGBD mais je n'ai pas le temps de m'y coller. En plus, je ne pourrais pas faire ça tout seul vu mon peu de connaissances en ce domaine.

Mais si quelqu'un veut lancer le projet, qu'il ne se gène pas !
Avatar de Waldar Waldar
http://www.developpez.com
Modérateur
le 25/11/2011 11:40
Citation Envoyé par pacmann  Voir le message
Et pour refaire un peu de pub, sous Oracle, tu peux choisir si tu veux le modèle SQL Server (Organization = index) ou DB2 (Organization = heap).

C'est pareil chez la concurrence, sur SQL-Server aussi tu peux choisir d'avoir des index cluster ou non cluster.
Avatar de pacmann pacmann
http://www.developpez.com
Expert Confirmé
le 25/11/2011 11:51
Ho, c'était donc ça...

Citation Envoyé par Waldar  Voir le message
là où SQL-Server le propose quasiment par défaut.

Avatar de Waldar Waldar
http://www.developpez.com
Modérateur
le 25/11/2011 14:13
Tout est dans le quasiment !
Avatar de SQLpro SQLpro
http://www.developpez.com
Rédacteur
le 28/11/2011 10:48
Citation Envoyé par fsmrel  Voir le message
Si quelqu'un avait la bonté de confirmer ou infirmer...

je te confirme....

Mais je rajouterais deux choses :
1) on n'est pas obligé de créer un index clustered sur chaque table dans MS SQL Server, même si c'est une bonne pratique.
2) il faut bien choisir son index cluster si l'on doit en implanter un : le plus petit possible, avec le plus grand nombre de valeur possible, invariant et si possible monotone en sus d'être UNIQUE. Bref, l'auto incrément ou un horodatage en PK est parfait
3) dans les SGBDR on s'aperçoit que les données les plus utilisées sont (à différentes échelles, mais c'est quasiment TOUJOURS vrai) les plus récentes :
  • pour les commandes, facture, bons de livraisons c'est extrêmement vrai
  • pour les produits c'est assez vrai (pense au téléviseur noir et blanc à tube cathodique),
  • pour les clients c'est aussi relativement vrai (un client d'il y a 10 ans à plus de chance d'être mort qu'un client qui viens tout juste d'être saisi.)

Or l’organisation d'une table en cluster avec un auto incrément ou un horodatage maximise la mise en cache des données les plus fréquemment scrutées puisqu'elle regroupe dans les mêmes pages les données les plus récentes... alors que pour une table en HEAP ce n'est jamais le cas.... ce qui oblige à farcir la mémoire de pages sous utilisées (et conduit donc à une durée de vie des pages en mémoire bien moins longues..., donc du Roll Over, donc des temps de réponse globalement moins bon !!!)

Bref, il n'y a pas que le nombre de page a lire qui a son importance !

Comme tu le sais, le système est global (théorie des systèmes) et le voir par un seul bout de la lorgnette peut s'avérer casse gueule !

A +
Avatar de fsmrel fsmrel
http://www.developpez.com
Expert Confirmé Sénior
le 30/11/2011 4:38
Bonsoir,

Citation Envoyé par SQLpro  Voir le message
je te confirme...


Merci...

Citation Envoyé par SQLpro  Voir le message
il n'y a pas que le nombre de pages à lire qui a son importance !


On est bien d’accord. Lire un maximum de pages dans un minimum de temps est une excellente chose, mais si c’est ce qui est lu est faux, on a tout perdu.

Mais inversement, avec des données saines, s’il faut dix jours pour effectuer un batch quotidien sensible (j’ai eu à auditer et réparer ça...) parce qu’il y a des I/O bound dans tous les sens, ça n’est pas mieux (voir « Le clustering selon DB2 for z/OS », plus précisément la partie « Le rôle crucial de l'index cluster. I/O bound vs CPU bound »).

Les données ne sont pas forcément toutes présentes en mémoire, et les performances désastreuses qui sont la conséquence des I/O bound incitent à faire en sorte que les index cluster de deux tables (et plus) à joindre soient synchrones pour éliminer ce phénomène.

Je suis évidemment d'accord que les données les plus utilisées sont les plus récentes et qu’en conséquence l’auto-incrément ou un horodatage pour une clé primaire est une bonne chose (avec un bémol si les INSERT provoquent des phénomènes de verrouillage) parce que cela évacue efficacement les I/O bound lorsqu’on exécute des SELECT.

Cela dit, il y a des situations dans lesquelles on peut avoir intérêt à ce que les lignes d’une table qui sont chronologiquement consécutives soient en fait éparpillées dans l’espace (façon puzzle bien sûr). Si une foule d’utilisateurs saisissent des commandes en même temps, il y aura risque de contention impliquant les tables des commandes et des lignes de commande, conséquence du mécanisme de verrouillage (je parle pour DB2 for z/OS où, comme le rappelle Luc Orient, on privilégie plutôt le verrouillage au niveau page, sachant que je ne connais pas les usages avec SQL Server...). Que les valeurs de la clé primaire de la table des clients soient hachées plutôt qu’incrémentées ne me gêne pas, au contraire, car les risques de contention en cas de mise à jour des clients deviennent très minimes (création, modification, suppression). En revanche, le hachage des valeurs prises par la clé de la table des commandes et de celle de la table des lignes de commande n’est évidemment pas recommandé, car si les risques de contention disparaissent les I/O bounds ne manqueront alors pas de se manifester en masse...

C’est là où la soute et la dunette se rejoignent : une des clés de la performance des applications est liée à l’utilisation de l’identification relative au niveau conceptuel (disons MCD Merise). Dans la soute, si en conséquence la clé (hachée) de la table des clients est le singleton {CliId}, celle de la table des commandes est composée du couple {CliId, CdeId}, celle de la table des lignes de commande est composée du triplet {CliId, CdeId, LigneId}, celle de la table des engagements sur ligne de commande est composée du quadruplet {CliId, CdeId, LigneId, EngId}, etc., façon poupées russes, alors les I/O bounds et les contentions seront absents, si pour chacune de ces tables la clé de l’index cluster est celle de la clé de la table.

Si l’on souhaite savoir quels camions livrent le client untel (cf. Dénormalisation vs amélioration (optimisation), le 4e exemple et la note concernant l’identification relative), grâce à l’identification relative répercutée sur le clustering, non seulement on élimine le phénomène d’I/O bound, mais en plus on élimine des jointures qui, si on utilisait l’identification absolue seraient inévitables (2 jointures contre 5 dans l’exemple).

Pour une entreprise dans laquelle les traitements qui tournent autour des commandes sont ceux qui méritent la plus grande attention, la mise en œuvre de l’identification relative associée au clustering aura un impact déterminant. La stratégie est du reste la même si l’on traite des appels de cotisation dans les caisses de retraite ou autres organismes, etc.

Il est évident que toutes les requêtes ne peuvent pas bénéficier de ce traitement de faveur, et qu’il restera des I/O bound quelque part. Néanmoins, lors de l’étape de conception, suite aux entretiens avec la maîtrise d’œuvre et les chefs de projets, en tant que DBA on sait quand même quelles seront les requêtes les plus fréquentes, les plus sensibles, celles qui si on n’y prend pas garde feront que les applications partiront en vrille (j’en reviens au batch lourd quotidien qui a duré dix jours lors de sa mise en production (sur une machine bases de données à cent-vingt-huit millions de francs à l’époque, et qui n’avait bien entendu jamais fait l’objet du moindre prototypage de performances...).

Citation Envoyé par SQLpro  Voir le message
Comme tu le sais, le système est global (théorie des systèmes) et le voir par un seul bout de la lorgnette peut s'avérer casse gueule !


Don’t worry! La théorie c'est bien, mais la pratique c'est pas mal non plus. Ça fait quarante cinq ans que la validité des données, la performance des applications et la satisfaction de mes clients font partie de mes préoccupations...
Avatar de SQLpro SQLpro
http://www.developpez.com
Rédacteur
le 01/12/2011 16:56
Note bien que tout ceci est en train de se remettre en cause notamment avec les SSD qui, agrégés sous forme RAID, donnent parfois des résultats spectaculaires...

Pour ce qui est de SQL Server, cela fonctionne de même (préférence au verrouillage page, mais descente à la ligne en cas de concurrence).

Pour ce qui concerne le hot spot de calcul des clefs auto incrémentées dans SQL Server, il a fait l'objet d'une excellente optimisation, mais au prix d'un bug rarissime, mais néanmoins gênant : celui de doublons (évidemment rejetés s'il y a contrainte PK ou UNIQUE).

A +
Avatar de fsmrel fsmrel
http://www.developpez.com
Expert Confirmé Sénior
le 09/12/2011 3:39
Bonsoir,

Citation Envoyé par SQLpro  Voir le message
Pour ce qui est de SQL Server, cela fonctionne de même (préférence au verrouillage page, mais descente à la ligne en cas de concurrence).

Qui détermine la préférence ? Le DBA ? SQL Server ?
Si le granule de verrouillage est la page, et s’il y a de la concurrence en mise à jour, SQL Server prend-il dynamiquement l’initiative de passer au verrouillage au niveau de la ligne, quitte à revenir plus tard à la page (en supposant qu'il sache inférer que ça vaut le coup) ?

D'après ce que je lis dans la doc, le SGBD se baserait plutôt sur les caractéristiques du schéma et des requêtes :
The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.
Avatar de fadace fadace
http://www.developpez.com
Rédacteur/Modérateur
le 21/01/2014 11:17
Il y a une notion d'escalade de verrouillage, faite par le moteur.

A un certain seuil (à l'époque éculée de mes derniers cours Internals, c'était 200 pages), le verrouillage page passe à un verrouillage table.

Visiblement, [source], les seuils ont changé mais le principe est resté au travers des ans...

On peut bien sûr forcer cela, si l'on se croit plus malin que lui .

Offres d'emploi IT
Ingénieur système middleware J2EE
CDI
CTS NORD - Ile de France - Paris (75001)
Parue le 02/07/2014
Chef de projet technique java/j2ee
CDI
Opensourcing - Ile de France - Paris (75000)
Parue le 22/07/2014
Responsable déploiement (informatique / réseau)
CDI
Paris Incubateurs - Ile de France - Paris (75000)
Parue le 07/07/2014

Voir plus d'offres Voir la carte des offres IT
 
 
 
 
Partenaires

PlanetHoster
Ikoula