Combien votre base de données contient-elle de tables avec plus de 20 colonnes ? (une seul choix pos
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...
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.
Envoyé par Luc Orient
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 !
Envoyé par SQLpro
Pour le ROWID...
Envoyé par Luc Orient
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 là à propos des soixante-sept millions de bovins chers à CinePhil.
Permettez-moi de me citer :
Envoyé par fsmrel
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 :
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).
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...
Envoyé par SQLpro
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.
Et elle remplace la clé pourrie auto-incrément dénuée de sens qu'on crée habituellement sur les autres SGBD pour "optimiser" la base.
Pas du tout une clé technique sert à palier la faiblesse des clés fonctionnelles sujettes à modification.
Une clé fonctionnelle peut être immuable, elle peut alors être une PK.
Mais si la clé fonctionnelle immuable est composée de multiples colonnes et également FK dans d'autres tables, alors utiliser une séquence pour auto-incrémentée une clé allège l'écriture des jointures ainsi que la quantité de données à stocker.
Le rowid n'a strictement rien à voir avec les clés.
Je dis aîe pour la citation que je fais juste au dessus pas pour votre requête.
Le problème ici n'est pas la performance mais l’intérêt d'une telle table?
La table est du coups énorme, l'ajout/modification d'une commande (exemple) met à jour tous vos indexes, y compris pour les autres 'types' qui n'ont rien à voir, problème que vous n'auriez pas en éclatant cette table.
Vous nous parlez de performances en lecture... quel est le but de la requête? une REPORT? Vous avez des bases OLAP pour ça pourrais je vous répondre...
Quid des performances en ajout?en modification voir suppression?
Honnêtement StringBuilder, sortir un exemple concret que seul vous pouvez analyser et utiliser n'a pas énormément d'intérêt, tout simplement parce qu'il est impossible de vous challenger que ce soit sur votre base ou votre applicatif.
Les différents intervenants ont montré que votre modélisation n'est pas parfaite, néanmoins pas parfait n'est pas synonyme de complètement inutilisable et comme Yanika_bzh le concluait précédemment il reste de nombreuses applications très robustes qui ne sont pas normalisées.
N'oubliez pas de prendre en compte que SQLPro est très soucieux de la différenciation des modèles physiques (tables) et externes (vue).
Je m'avance certainement, mais votre grosse table repartie sur une dizaine de tables et regroupée dans une (ou plusieurs) vue(s) vous donnerait à minima plus de souplesse et probablement plus de performances.
Vos problématiques de recherches seraient aussi bien gérées par une vue, les jointures sur des clefs coûtent très peu dès lors que vous avez filtré une table fille sur un certain nombre de critères.
Sur le coups des index, s'ils accélèrent les sélections ils ralentissent fortement les mises à jour et suppressions, cherchez dans le forum vous trouverez des exemples de batch qui passent de plusieurs heures à quelques minutes juste par la suppression des index.
Enfin sur la performance d'un index en fonction de son type (littéral ou numérique), chez Oracle c'est similaire, chez SQL-Server c'est très différent, il n'y a pas de bonne réponse universelle.
Sur une seule et même table ou sur une seule et même ligne ?
Dans le premier cas, ça ne concerne encore une fois que SQL Server..
En tous cas sous Oracle, dans le cas général, une modification d'une ligne ne lock que cette ligne...
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...).
Envoyé par SQLpro
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...
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.
Mais pour l'auteur de l'article le verrou exclusif est posé sur quoi ?
Vous avez raison, je perle de base de données relationnelles, pas décisionnelles.
Sur le nombre de colonnes par table, même SAP reste dans les standards.
En sus il faut aussi relativiser... 50 colonnes dont 40 de type booléen, ce n'est pas grand chose.
Ca m'a l'air bien théorique et bien coloré "noir ou blanc"... Ou bien on est "normalisé" ou bien on met "tout dans une seule table"... Un peu exagéré comme commentaire, non?
Si on parle Oracle, je ne vois pas comment joindre des tables de millions de lignes ne coûte "presque rien".
exemple:
CLIENT( id, nom, ... )
PROFESSION( id, flag_liberale, description, ... )
PROFESSIONS( client_id, flag_prof_principale, profession_id )
ADRESSES( client_id, adresse_no, code_postal, rue, ... )
Et maintenant je cherche les gens ayant une profession libérale et habitant dans "75...."
SELECT DISTINCT c.nom AS prospect, ...
FROM client c, profession p, professions cp, adresses a
WHERE c.id = cp.client_id
AND c.id = a.client_id
AND a.code_postal LIKE '75%'
AND cp.profession_id = p.id
AND p.flag_liberale = 1
Cet exemple est simple et un peu naif mais il va quand même "ramer"...
Siebel par exemple a fait un peu de dénormalistation en entrant l'adresse principale dans la table "client". Ca devient
SELECT c.nom AS prospect, ...
FROM client c, profession p, professions cp
WHERE c.id = cp.client_id
AND c.code_postal LIKE '75%'
AND p.flag_liberale = 1
AND p.id = cp.profession_id
AND cp.flag_prof_principale = 1
(oui oui, pas tout à fait le même "result set", il faut sacrifier un peu sur l'autel de la performance)
Et on peut mettre aussi un flag "prof_liberale" dans CLIENT pour arriver à
SELECT c.nom AS prospect, ...
FROM client c
WHERE c.code_postal LIKE '75%'
AND c.flag_liberale = 1
Quand on doit faire ce genre de recherche en temps réel pour des millions et des millions de clients, la théorie retourne dans les livres.
J'ai des tables qui ont plus d'un milliard d'enregistrements
J'ai des tables qui ont plus de 800 colonnes ((heureusement, pas les mêmes ;-))
J'ai des utilisateurs qui ne se plaignent pas (enfin, pas toujours) de la performance.
Envoyé par Luc Orient
Contrats_Index_Cluster_A.png)
Contrats_Index_Cluster_B.png)
