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 :

Optimisation et utilisation des clés étrangères [Débat]


Sujet :

Optimisations SGBD

  1. #1
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 792
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 792
    Points : 34 013
    Points
    34 013
    Billets dans le blog
    14
    Par défaut Optimisation et utilisation des clés étrangères
    Bonjour,
    J'étudie un système de bases de données MySQL qui a été conçu sans conception schématique préalable et agrémentée au fil du temps de fantaisies diverses et variées. Dans l'analyse que je dois rendre, j'aimerais argumenter en faveur d'une reconception plus normale du machin et je cherche donc des arguments autres que purement théoriques et conceptuels qui pourraient être considérés comme dogmatiques et ne justifiant pas l'investissement en temps et le risque vis à vis de l'application pour modifier tout ça de manière cohérente.

    La grosse crainte de l'équipe qui utilise le système est la performance, autrement dit l'attente angoissée de l'utilisateur devant un écran qui ne réagit pas parce que les données mettent du temps à être traitées.

    Ce que j'ai remarqué notamment dans le système, c'est qu'un champ Ind de type Int auto incrémenté figure bien dans la plupart des tables mais qu'il est très peu utilisé comme identifiant dans les tables liées (en tant que clé étrangère en d'autres mots).

    La raison invoquée par le créateur du système est que les jointures sont coûteuses en temps de traitement. Est-ce une idée fausse ? Est-ce parfois vrai, parfois faux ? Sur le plan des performances, vaut-il mieux utiliser des clés étrangères Index "anonymes" qui vont demander une jointure en interrogation ou une colonne à enregistrements uniques mais plus faciles à interroger, comme un nom d'utilisateur par exemple ?

    J'ai l'intention de faire quelques tests sur des grosses tables mais j'aimerais déjà avoir votre avis sur la question.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  2. #2
    Membre expérimenté

    Profil pro
    Inscrit en
    Août 2002
    Messages
    1 249
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2002
    Messages : 1 249
    Points : 1 745
    Points
    1 745
    Par défaut
    La raison invoquée par le créateur du système est que les jointures sont coûteuses en temps de traitement. Est-ce une idée fausse ?
    Le fondement d'une base de données relationnelle, c'est la jointure, alors evidemment, ne plus utiliser de jointure ne me parrait pas une bonne idée ;o))

    par contre, c'est vrai que la denormalisation existe dans des cas exceptionnel d'amélioration de la performance et que son but est de supprimer les jointures en introduisant une redondance pour améliorer la performance...

  3. #3
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 792
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 792
    Points : 34 013
    Points
    34 013
    Billets dans le blog
    14
    Par défaut
    Merci pour la réponse.
    Je pense la même chose mais j'ai besoin d'arguments tangibles. Je vais plonger un peu plus dans des cas concrets du logiciel et de la manipulation des grosses tables, faire des tests comparatifs en créant un schéma meilleur et en utilisant les mêmes données.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  4. #4
    Membre expérimenté

    Profil pro
    Inscrit en
    Août 2002
    Messages
    1 249
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2002
    Messages : 1 249
    Points : 1 745
    Points
    1 745
    Par défaut
    appuis toi sur la norme... une base bien normalisée en 3 ème forme normale...

    Ensuite, ce qui peut faire la différence, c'est l'écriture des requetes aussi...

  5. #5
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    Citation Envoyé par CinePhil Voir le message
    Bonjour,
    Dans l'analyse que je dois rendre, j'aimerais argumenter en faveur d'une reconception plus normale du machin et je cherche donc des arguments autres que purement théoriques et conceptuels qui pourraient être considérés comme dogmatiques et ne justifiant pas l'investissement en temps et le risque vis à vis de l'application pour modifier tout ça de manière cohérente.
    Très facile : tu imagines tous les cas aberrants qu'une modélisation normalisée empêcherait mais que ton machin laisse passer. Ensuite, tu fais des requêtes pour voir si ces cas existent vraiment. La dernière fois que j'ai fait ça, j'ai trouvé au moins un cas de toutes les aberrations que j'avais imaginées
    C'était l'ERP d'une fac, je me rappelle notamment des cas rigolos suivants :

    • étudiants vraiment très vieux (nés dans les années 980)
    • étudiants pas encore nés, mais déjà inscrits (naîtront vers 2080)
    • thésards inscrits en 6ème année de première cycle
    • diplômés qui recommencent le même cursus
    • diplômés dont le niveau recule de plusieurs années

    on avait aussi des cas que je n'avais même pas imaginés et qui se sont présentés :

    • étudiant mâle avec une civilité "Mademoiselle" (l'était pas content de voir ça sur son attestation de diplôme )
    • étudiant inscrit deux fois, avec la moitié de ses cours sur chaque inscription (ça aide pas à avoir le bon nombre d'UV )
    • étudiant touchant une bourse sans être inscrit ni suivre de cours

    Citation Envoyé par CinePhil Voir le message
    La raison invoquée par le créateur du système est que les jointures sont coûteuses en temps de traitement. Est-ce une idée fausse ? Est-ce parfois vrai, parfois faux ?
    C'est une idée vraie en théorie mais généralement négligeable en pratique. D'un côté, c'est vrai que les jointures sont des opérations complexes et coûteuses ; d'un autre côté, les SGBD sont faits pour ça, et la jointure est certainement l'opération la mieux optimisée (à condition d'avoir des index des deux côtés, évidemment).
    Citation Envoyé par CinePhil Voir le message
    Sur le plan des performances, vaut-il mieux utiliser des clés étrangères Index "anonymes" qui vont demander une jointure en interrogation ou une colonne à enregistrements uniques mais plus faciles à interroger, comme un nom d'utilisateur par exemple ?
    Je poserai la question un peu différemment : est-ce mieux d'avoir des données fausses en 1/2 seconde ou des données justes en 1 seconde ?

    Autrement dit, par défaut, il faut normaliser à fond. Si des problèmes de perf apparaissent, il faut les analyser assez précisément, et éventuellement les résoudre par une dénormalisation (mais seulement s'il n'y a aucune autre solution). Ton gars a fait exactement le contraire : supposer des problèmes de perf là où n'y en a sans doute pas et introduire des tonnes d'incohérences.
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 792
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 792
    Points : 34 013
    Points
    34 013
    Billets dans le blog
    14
    Par défaut
    Merci Antoun, j'ai bien rigolé en lisant ta réponse !
    Bon, j'ai plus qu'à remettre les mains dans le cambouis !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Membre expert
    Avatar de TheLeadingEdge
    Inscrit en
    Mai 2005
    Messages
    1 199
    Détails du profil
    Informations forums :
    Inscription : Mai 2005
    Messages : 1 199
    Points : 3 100
    Points
    3 100
    Par défaut
    La raison invoquée par le créateur du système est que les jointures sont coûteuses en temps de traitement. Est-ce une idée fausse ? Est-ce parfois vrai, parfois faux ?
    Ca c'est une légende...Sur une base bien conçue et utilisée ds des conditions normales les jointures ne devraient pas pénaliser.
    Dans des conditions normales : ie Pas de requête de la mort façon BI.
    Bien conçue : Les besoins ont été correctements exprimés et pris en compte, normalisée (of course) et ds laquelle on évite entr'autre les PK composée du nom par ex.
    Un simple explain sur sur une requête qui extrait les produits d'un certain type parmi les 100 000 que contient une table devrait te convaincre qu'un full scan table est plus couteux que si tu fais 1 jointure avec une table ''type de produit'' qui va te réduire de 90% le volume des tuples à lire.
    Enfin avoir un schéma non normalisé peut même augmenter le nombre de jointures et plomber le temps de dev. et les perfs.
    Imagines une société de VPC qui as 1 table ''couts de transports'' de ce genre.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    transporteur, prix, destination
    UPS, 25, Lyon
    Colissimo, 35, Paris
    Calberson, 35, Paris
    x, 25, Lyon
    y, 25, Marseille
    au lieu de 2 tables transporteur et destination.
    Si un utilisateur veut connaitre le prix moyen de ttes les destinations tu es mal.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT AVG(tarif) FROM ...
    = 29 au lieu de 28,35 (arrondi).
    Pour t'en sortir il faut faire une rq du genre
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT AVG(tarif) 
    FROM 
    (
    SELECT tarif 
    FROM Cout
    GROUP BY destination, tarif
    ) AS Tmp;
    no comment

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    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 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    La raison invoquée par le créateur du système est que les jointures sont coûteuses en temps de traitement. Est-ce une idée fausse ? Est-ce parfois vrai, parfois faux ? Sur le plan des performances, vaut-il mieux utiliser des clés étrangères Index "anonymes" qui vont demander une jointure en interrogation ou une colonne à enregistrements uniques mais plus faciles à interroger, comme un nom d'utilisateur par exemple ?
    C'est malheureusement avec des croyances aussi profondément débiles dues à l'inculture crasse de certains informaticiens que l'on tue les bases de données.

    Petite anecdote pour commencer et ensuite comparatif...
    (PS : je fais des audits de BD depuis maintenant plus d'une dizaines d'années, et je travaille les SGBDR depuis 20 ans...)

    1) anecdote
    J'ai récemment été "convoqué" par un organisme d'état au sujet d'une application critique utilisant une base de données, application écrite par un éditeur dont les informaticiens, comme souvent ont une faible culture informatique au sujet des SGBDR. Bilan de la chose : au fur et à mesure de la collecte des données, l'application est devenue de plus en plus lente... C'est la faute à PostGreSQL... Revenons aux fichiers clame l'éditeur qui n'a pas peur du ridicule...
    Il est évident que cet éditeur ne sait pas se servir d'une base de données relationnelle.

    2) comparatif
    Pour comprendre pourquoi et comment un SGBDR peut être supra rapide, il faut savoir comment il fonctionne. En principe un SGBDR ne lit jamais le disque. En effet, toutes les données doivent être en RAM pour qu'une requête puisse s'effectuer. Dès lors les manipulations de données sont ultra rapide : le temps d'une lecture disque correspond au temps de plusieurs milliers de lecture RAM pour la même quantité d'information.
    Maintenant, la vitesse intrinsèque va dépendre de deux facteurs :
    a) la quantité de RAM, qui est toujours limitée
    b) l'indexation des tables
    Prenons un modèle de tables sans aucune clef ni indexation. Imaginons que nous avons 2 tables à mettre en relation et que pour chacune des tables les lignes fassent 200 octets.
    Mettons 100 000 000 (cent millions de lignes) dans chaque table. cela fera deux tables de 19 Go, soit une base de 38 Go.
    Imaginons maintenant que la RAM fasse 2 Go...
    Sachant qu'une base de données organise ses fichiers et sa RAM en page (pour pouvoir paginer) et prenons une page de taille de 8 Ko (courant sous Windows).
    Cela fait qu'une page contient 40 lignes de table et une table à besoin de 2 500 000 pages pour être stockée dans la base.
    Imaginons maintenant que ces deux tables soient pourvues de clef constituée d'un seul entier (de 32 bits). Comme une clef génère un index, nous avons une structure de données complémentaire qui fait en gros 100 000 000 de fois 32 bits (4 octets) + autant pour référencer la ligne, soit 762 Mo de données...

    Constatons donc une première chose : sans index, la recherche d'une information de clef demande la lecture de 19 Go, ce qui ne tient pas en mémoire, contre 762 Mo qui tient en mémoire ... Le rapport de vitesse entre mémoire et disque étant au moins de 1000, nous avons dans un cas :
    une seule lecture de 762 Mo en RAM contre 9.5 (19/2) chargement / déchargement mémoire pour lire toutes les lignes, c'est à dire 9500 fois plus lent !!!

    Demandons nous maintenant combien coûte une jointure si des deux côtés de la jointure il y a un index.

    Les lignes d'un index sont organisées en 2 parties : la clef d'index (une valeur que l'on cherche) et la référence à la ligne dans la table (la plupart du temps un entier 32 bits). Les index sont organisé sous forme d'arbre, chaque page d'index adressant autant de nouvelles pages que possible.
    par exemple dans notre cas, une ligne d'index faisant 8 octets, il y a 1000 lignes d'index dans une page. Ce qui veut dire que si notre arbre possède un seul niveau, il permet d'organiser la recherche de 1000 occurences.
    Au second niveau, chaque ligne référençant une nouvelle page, il y a 1000 * 1000 entrées différentes. Au 3e niveau il y a 1000^3 entrées et au niveau 4 1000^4 et ainsi de suite.
    Or pour chercher une seule ligne particulière il suffit de descendre d'une page par niveau.
    Ainsi dans notre table à 100 000 000 lignes, un arbre d'index à 3 niveaux suffit. Tant est si bien que faire une jointure entre deux tables dans ce cas revient à lire 6 pages de 8 ko de données (trois pages de l'index de la première table puis 3 pages de l'index de la seconde table)....

    A votre avis qu'est ce qui va le plus vite :
    lire séquentiellement une seule table de 38 Go contenant toutes les données afin de trouver la clef demandée
    ou
    faire une jointure entre deux tables de 19 Go sur des index qui ne nécessite que la lecture de 6 pages de 8 ko soit 48 ko de données ?

    CONCLUSION

    Une fois cela compris, vous comprendrez alors l'importance de faire de petites tables (elles occuperont peu d'espace en mémoire), peut importe le nombre des jointures pourvu que les colonnes mise en relations soient pourvues d'index.
    Cela s'appelle normaliser un schéma de BD, et cette technique avec l'ajout des technologies des bases de données donne des résultats exceptionnel en terme de compromis volume/vitesse de traitement.

    Pour compléter votre culture je vous invite à lire plusieurs de mes articles :
    1) l'optimisation des SGBDR :
    http://sqlpro.developpez.com/optimisation/
    notamment article 3/5
    2) une étude d'indexation
    http://www.sqlspot.com/INDEXATION-un...l-exemple.html

    2eme anecdote... il y a quelques années, modélisant une base de données, le client était stupéfait de voir que certaines requêtes ayant plus d'une dizaines de jointures prenait si peu de temps que la mesure de durée d'exécution de la requête n'étais pas apréciable (zero ms). Il s'en étonnait, jusqu'à ce que je luis dise que ces requêtes étaient basées en parties sur des vues qui elles mêmes introduisait d'autres jointures.... Ce jour là le client à commencé à comprendre que ce ne sont pas les jointures qui coûtent !

    DE PLUS...
    Dernière petite chose, on me demande souvent si les contraintes (par exemple intégrité référentielle) c'est pas contre performant... Oui cela demande du traitement supplémentaire lors des INSERT / UPDATE. MAIS... les meilleurs moteurs des SGBD relationnels savent en tirer un parti très puissant qui permet d'accélérer notablement les traitements. Et je vais vous donner un exemple spectaculaire...
    Soit la requête suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT ...
    FROM   T_CLIENT C
           FULL OUTER JOIN T_FACTURE F
                 ON C.CLI_ID = F.CLI_ID
    WHERE  F.CLI_ID = 53
    Avec 1 000 clients, 50 000 factures
    Imaginons que le client 53 n'existe pas (ni client ni facture). S'il n'y a pas d'intégrité référentielle il faudra faire une recherche du client dans les factures puis une recherche des clients dans les clients et joindre tout cela => 51 000 lignes lues si pas d'index.
    Maintenant que va faire le moteur s'il y a une intégrité référentielle sur CLI_ID entre les deux tables et que le moteur SQL est bien pensé ?
    Il ira juste voir D'ABORD dans les clients et s'il ne trouve pas il s'arrête immédiatement puisqu'il sait que grâce à la contrainte d'intégrité référentielle il n'est pas possible que cette référence de client soit présente dans la table des factures => 1 000 lignes lues si pas d'index

    A votre avis faut-il ou pas mettre des intégrité référentielles si vous voulez obtenir des performances ???
    Il en est de même généralement avec toutes les autres contraintes. Par exemple, mettre une contrainte CHECK value >= 0 sur des colonnes prix, quantité, dimensions... fera que si jamais vous faites une requête du style :
    Alors la réponse sera instantanée car il n'y aura même pas besoin de lire les données pour retourner un résultat vide !

    A +



    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/ * * * * *

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    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 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    Antoun a dit :
    C'est une idée vraie en théorie mais généralement négligeable en pratique. D'un côté, c'est vrai que les jointures sont des opérations complexes et coûteuses ;
    Non, non, non et non... Je ne peut pas laisser passer cela ! Non, les opérations de jointure ne sont pas couteuses si les index ont été placé sur clef primaire et clef étrangère. Elle sont même négligeables en regard d'opérations comme le tri, la recherche d'une valeur d'une colonne non indexée, un LIKE '%abc', ou le group by, notamment si le tri ou le group by porte sur des colonnes dépourvues d'index.

    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/ * * * * *

  10. #10
    Membre confirmé Avatar de TryExceptEnd
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Octobre 2006
    Messages
    501
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2006
    Messages : 501
    Points : 574
    Points
    574
    Par défaut
    Je suis encore étonné que l'on se demande encore de nos jours de l'utilité de ce qui fait vraiment qu'un SGBD est un SGBD, pas autre chose.
    La documentation est pourtant disponible pour tous grâce au web et je trouve malheureux qu'un expert comme SQLPro passe beaucoup de son temps a rectifier les allégations des uns et des autres au lieu de le laisser aider ceux qui en on vraiment besoin.
    Si vous êtes libre, choisissez le Logiciel Libre.

  11. #11
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 792
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 792
    Points : 34 013
    Points
    34 013
    Billets dans le blog
    14
    Par défaut
    Merci pour ta longue réponse SQLPro. J'avais déjà lu ton article sur l'exemple d'indexation qui permettait de passer (de mémoire) de 96000 à... 2 !
    Je l'ai montré à mon maître de stage et il a eu l'air intéressé. Je vais maintenant lui montrer cette réponse à ma question, j'espère que ça finira de le convaincre.

    Après il y a aura du boulot pour remettre de l'ordre dans la base de données mais bon. Pas inutile vu la croissance que risque de prendre cette BDD.

    Au fait au passage, moi aussi c'est un organisme public !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  12. #12
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Non, non, non et non... Je ne peut pas laisser passer cela !
    Bon, je me tais alors
    Merci pour ta magistrale démonstration !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    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 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    Oui, mais c'est l'expérience d'un vieux schnock !!! ;-)

    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/ * * * * *

  14. #14
    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
    ... Pour comprendre pourquoi et comment un SGBDR peut être supra rapide, il faut savoir comment il fonctionne. En principe un SGBDR ne lit jamais le disque. En effet, toutes les données doivent être en RAM pour qu'une requête puisse s'effectuer.
    Euh ... j'ai un peu de mal à comprendre là ... à un moment ou à un autre, pour monter la page en mémoire (mon SGBD à moi lit des pages), il faudra bien lire le disque non ?
    ... dites moi si je me trompe ...

    Une fois cela compris, vous comprendrez alors l'importance de faire de petites tables (elles occuperont peu d'espace en mémoire), peut importe le nombre des jointures pourvu que les colonnes mise en relations soient pourvues d'index.
    Certes, et je suis bien d'accord, mais, a contrario, la performance d'une jointure avec un prédicat de jointure non indexé est très souvent catastrophique ...

  15. #15
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    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 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    à un moment ou à un autre, pour monter la page en mémoire (mon SGBD à moi lit des pages), il faudra bien lire le disque non ?
    Comment vos données sont elles alimentées ? Par la magie de l'informatique ??? Ou par un processus qui fait que l'utilisateur poste une requête (flux réseau) qui est analysé et peut être directement monté en mémoire ?
    L'écriture des données sur le disque n'est que la dernière phase du processus et ce uniquement en cas de mise à jour... Autrement les lignes parviennent en mémoire cache, avant même d'être écrites sur le disque. Ce n'est donc pas une montée des lignes depuis le disque, mais un descente de pages dites sales (en fait désynchronisées de la table) vers le disque.
    Bien entendu ceci cuppose deux chose :
    1) une RAM infinie (ou tout au moins supérieure à la taille de la BD)
    2) un serveur qui ne s'arrête jamais (ce que devrait faire tout SGBDR C/S)

    Certes, et je suis bien d'accord, mais, a contrario, la performance d'une jointure avec un prédicat de jointure non indexé est très souvent catastrophique ...
    On peut aussi rouler avec une roue crevée et trouver que c'est pas terrible...

    Si vous utilisez un outil de modélisation, vous verrez que par défaut il n'oublie pas lui, de créer les index sur toutes les clefs étrangères.
    Ne pas respecter cette règles est de la bêtise pure et comme il s'agit d'un manquement grave aux règles de l'art, donc une faute professionnelle grave, je n'hésite pas à dire que dans ce cas, il convient de licencier le coupable !!! ;-)

    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/ * * * * *

  16. #16
    Membre expérimenté

    Profil pro
    Inscrit en
    Août 2002
    Messages
    1 249
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2002
    Messages : 1 249
    Points : 1 745
    Points
    1 745
    Par défaut contre exemple.
    Certes, et je suis bien d'accord, mais, a contrario, la performance d'une jointure avec un prédicat de jointure non indexé est très souvent catastrophique ...
    Bon, alors pour alimenter le débat, je vous propose de parler d'aloès, une base de moins de 1 giga avec la quasi totalité des tables à moins de 5000 lignes. Une petite base quoi !

    Donc je suis en charge d'optimiser cette base grâce à l'indexation.

    J'ai obtenu quelques résultats avec le tuning advisor de 2000.

    J'ai proposé l'indexation systématique des clefs étrangères me disant qu'un index de clef étrangère ne peut qu'être utile.

    Après indexation systèmatique des clefs étrangères, j'ai proposé au tuning advisor d'analyser avec possibilité de suppression d'index.

    En effet, il a supprimé quelques index de clef étrangères dans son analyse et proposa un gain de 15 % sur la performance.

    J'ai pu constater par moi même que sur une petite base, une indexation de clé étrangère peut entrainer une trés lègère perte de performance ( environ 1 s )...

    Par conséquent, personnellement, je pense que sur une petite base de données, pour des tables de moins de 10.000 lignes, l'indexation des clefs étrangères est facultative.

    Qu'en pensez-vous ?

  17. #17
    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
    Comment vos données sont elles alimentées ? Par la magie de l'informatique ??? Ou par un processus qui fait que l'utilisateur poste une requête (flux réseau) qui est analysé et peut être directement monté en mémoire ?
    L'écriture des données sur le disque n'est que la dernière phase du processus et ce uniquement en cas de mise à jour... Autrement les lignes parviennent en mémoire cache, avant même d'être écrites sur le disque. Ce n'est donc pas une montée des lignes depuis le disque, mais un descente de pages dites sales (en fait désynchronisées de la table) vers le disque.
    certes ... certes ...

    Mais nous parlons ici de jointure et d'indexation et donc de lecture ...

    Il faudra quand même aller chercher les données sur le disque, il me semble ...

  18. #18
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    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 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Ajouter des index à foison ne pose évidemment aucun problème particulier au niveau de la consultation. L’optimiseur du SGBD a juste à choisir ceux qui lui paraissent pertinents. En revanche, cela peut devenir catastrophique lors des opérations de mise à jour, si par exemple vous avez besoin de supprimer un pourcentage relativement élevé de lignes d’une table : j’ai par exemple constaté la durée d’un traitement batch passer de 9 heures à 3 minutes après suppression des 5 index d’une table de quelques millions de lignes (DB2 for z/OS, années 90).

    => Dans ce genre d’exercice (traitement de masse + mise à jour), supprimer d'abord les index et les recréer ensuite.
    (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.

  19. #19
    Membre régulier
    Inscrit en
    Avril 2002
    Messages
    182
    Détails du profil
    Informations forums :
    Inscription : Avril 2002
    Messages : 182
    Points : 113
    Points
    113
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Ajouter des index à foison ne pose évidemment aucun problème particulier au niveau de la consultation. L’optimiseur du SGBD a juste à choisir ceux qui lui paraissent pertinents. En revanche, cela peut devenir catastrophique lors des opérations de mise à jour, si par exemple vous avez besoin de supprimer un pourcentage relativement élevé de lignes d’une table : j’ai par exemple constaté la durée d’un traitement batch passer de 9 heures à 3 minutes après suppression des 5 index d’une table de quelques millions de lignes (DB2 for z/OS, années 90).

    => Dans ce genre d’exercice (traitement de masse + mise à jour), supprimer d'abord les index et les recréer ensuite.
    Le probleme de supprimer les index et de les receer c'est que ca met enormement de temps lorsque la table est volumineuse.
    Cas personel : 4 heures pour supprimer/recreer les index sur une table de 20 millions de lignes soit autant que le batch de suppression !!

  20. #20
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    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 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par voyageur
    Le probleme de supprimer les index et de les receer c'est que ca met enormement de temps lorsque la table est volumineuse.
    Certes, pour une table de 20 millions de lignes, le temps de création d’un index est élevé : disons de l’ordre de 20 à 30 minutes en moyenne avec DB2 for z/OS, pour une table moyennement désorganisée. Ce que j’ai écrit peut avoir l’air d’être péremptoire, mais s’il s’agit d’un principe qui fonctionne souvent pas mal, il ne faut pas pour autant l’ériger en règle absolue. Une solution alternative peut par exemple consister à recharger complètement la table concernée, en évitant de reconduire les tuples réputés supprimés. Ce rechargement a aussi pour effet de réorganise la table. Là encore, il ne s’agit pas d’une panacée, et chaque table est un cas particulier du fait de la nature des traitements de mise à jour qu’elle subit, de leur fréquence, de leur volume, du nombre d’index et de la nature de ceux-ci, de la taille des clés, du nombre de plans et packages d’application touchés, j’en passe et des meilleures. Il n’y a qu’en effectuant des mesures précises appliquées à différents scénarios que l’on peut choisir le meilleur de ceux-ci (ou le moins mauvais...) En tout état de cause, il est évident que l’on y regarde de beaucoup plus près quand les tables contiennent 20 millions de lignes que lorsqu'elles en contiennent 10 fois moins.
    (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.

Discussions similaires

  1. Import/export sql 2000 impossible à causes des clés étrangères
    Par chouchou2clichy dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 24/03/2007, 09h27
  2. [hibernate 3] [mapping] Description des clés étrangères
    Par CharlSka dans le forum Hibernate
    Réponses: 2
    Dernier message: 01/02/2007, 10h01
  3. Réponses: 5
    Dernier message: 05/10/2006, 20h07
  4. Gestion des clés étrangères
    Par Gonelle dans le forum HyperFileSQL
    Réponses: 1
    Dernier message: 06/07/2006, 11h48
  5. Optimiser l'utilisation des pointeurs
    Par progfou dans le forum C
    Réponses: 65
    Dernier message: 10/03/2006, 12h49

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