Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 16 sur 16
  1. #1
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut Calcul avec plusieurs aggrégats

    Bonjour tout le monde

    Je cherche a faire un peu d'analyse de donnees sur mes tables, et je commence a tomber sur des cas assez recurrents qui me posent probleme.

    Admettons que j'ai 3 tables.
    Une table "community" avec 1 colonne "id" et d'autres attributs
    Une table "user", de meme.
    Une table "community_user" qui me modelise la relation ManyToMany entre les deux.

    Si je veux par exemple le nombre de moins de 30 ans par communaute, pas de soucis

    Code :
    SELECT community_id, count(user_id) FROM community_user, user WHERE id=user_id AND age<30 GROUP BY community_id
    Par contre, comment les choses se passent si je veux calculer plusieurs aggregats avec des filtres differents. Disons renvoyer le nombre de moins de 18 ans et de plus de 60 ans dans la meme requete.

    J'avais commence quelque chose avec des clauses select imbriquees dans mon from, mais je me suis vite rendu compte que mon extraction commencait apres que ces select aient ete faits en integralite. Si je rajoute un limit 10 a la fin par exemple, je dois quand meme patienter jusqu'a la fin des select.

    Ainsi,
    Code :
    1
    2
    3
    4
    SELECT community_id, c1, c2 FROM
    (SELECT community_id, count(user_id) AS "c1" FROM community_user GROUP BY community_id) AS "q1",
    (SELECT community_id, count(user_id) AS "c2" FROM community_user, user WHERE id=user_id AND age<30 GROUP BY community_id) AS "q2"
    WHERE q1.community_id=q2.community_id
    semble vraiment inefficace !

    Y a-t'il une meilleure methode pour faire cela ?
    Le SELECT CASE n'est pas satisfaisant, parce que les filtres que je veux appliquer ne sont pas forcement disjoints.
    Les Window Functions (je suis sous Postgresql) m'ont ete recommandees, mais je ne vois pas du tout comment elles peuvent m'aider...
    Les UNION ne me semblent pas tres "propres" dans ce cas, compare aux jointures (mais vu ce que j'y connais...)

  2. #2
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Je viens de penser a ca
    Code :
    1
    2
    3
    4
    5
     
    SELECT id,
    (SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND community_id=community.id),
    (SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND age<30 AND community_id=community.id)
    FROM community
    Mais je soupconne que c'est aussi lent que mon autre solution... Qu'en pensez-vous ?

  3. #3
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    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 : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Il faut utiliser CASE et additionner ceux qui répondent à la condition au lieu de les compter.

    Au passage, les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !

    Et indente et aère ton code, il sera plus facile à lire et à débugguer.

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    SELECT cu.community_id, 
    	SUM (
    		CASE 
    			WHEN u.age < 30 THEN 1
    			ELSE 0
    		END
    	) AS moins_de_30_ans,
    	SUM (
    		CASE 
    			WHEN u.age > 60 THEN 1
    			ELSE 0
    		END
    	) AS plus_de_60_ans
    FROM community_user cu
    INNER JOIN user u ON u.id = cu.user_id 
    GROUP BY cu.community_id
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « 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
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Merci pour la piste. Je vais lancer un test en taille reelle sur mes tables pour voir comment les differentes approches se comportent. A ce rythme la, ca prendra tout le week-end ^^

    En ce qui concerne le WHERE, c'est une mauvaise habitude que j'ai prise de regarder les requetes genererees par mon ORM (SQLAlchemy) qui au lieu de INNER JOIN utilise des WHERE partout.
    Mais effectivement, c'est plus lisible de separer les clauses de filtrage de la jointure, j'en conviens

  5. #5
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    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 : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Ah ces ORM ! Quelle plaie !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « 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 !

  6. #6
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Etant plutot debutant en SQL, j'etais parti du principe que l'ORM savait un peu mieux que moi ce qu'il faisait.
    Ensuite je me suis rendu compte que le WHERE et INNER JOIN etaient effectivement interpretes de la meme facon par le moteur, donc je croyais que les formulations etaient juste equivalentes. Je ne savais pas qu'une ecriture etait plus recommandee que l'autre.

  7. #7
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    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 : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Les deux écritures sont équivalentes pour la jointure interne. Avec la jointure externe, il peut y avoir un piège que décrit SQLPro dans son cours sur les jointures.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « 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 !

  8. #8
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Bon, ca a pris du temps, mais je voulais etre sur que rien d'autre n'interferait avec la base, donc en gros j'ai lance les requetes le week-end, sur la meme machine.

    Code :
    1
    2
    3
    4
    5
    6
    SELECT id,
        (SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
            WHERE community_id=community.id),
        (SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
            WHERE age<30 AND sex='male' AND community_id=community.id)
    FROM community
    Cette requete se termine apres 19h 5mins


    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT community.id, 
        SUM (
            CASE 
                WHEN age < 30 AND sex='male' THEN 1
                ELSE 0
            END
        ),
        COUNT (user.id)
    FROM community
    INNER JOIN community_users ON community.id = community_id
    INNER JOIN user ON user.id = user_id
    GROUP BY community.id
    Celle-la met 1j 2h 10mins, c'est a dire pratiquement 40% de temps en plus

    Pour information, la table community contient 3 millions d'entrees, la table user en contient 50 millions. La table de mapping est a environ 500 millions.
    Je pense avoir mis les index ou il faut, sur les cles etrangeres egalement, et dans ce cas precis les colonnes "age" et "sex" (je precise que j'ai bien mis une Enum sur cette derniere, et non pas du texte... )

  9. #9
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 809
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 809
    Points : 13 464
    Points
    13 464

    Par défaut

    Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
        SELECT community.id
             , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
             , COUNT(user.id)
          FROM community
     LEFT JOIN community_users
    INNER JOIN user
            ON user.id = user_id
            ON community.id = community_id
      GROUP BY community.id
    Si ce n'est pas le cas, pas de soucis.

    Maintenant, vos temps d'exécution sont beaucoup trop élevés.
    Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
    Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?

  10. #10
    Membre expérimenté
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 550
    Points
    550

    Par défaut

    Dans ta première requête, cette partie :
    Code :
    1
    2
    (SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
            WHERE community_id=community.id)
    n'est elle pas équivalente à ceci ?:
    Code :
    (SELECT COUNT(user_id) FROM community_users WHERE community_id=community.id)
    Edit:
    C'est surement équivalent, mais en relisant tes messages, je pense que ce n'est pas le but puisque tu essayais de comparer le temps d’exécution des deux méthodes...

  11. #11
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    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 : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    je precise que j'ai bien mis une Enum sur cette derniere, et non pas du texte...
    Mauvaise idée !
    Il est bien plus long pour le SGBD d'évaluer 'male' et 'female' qu'un simple chiffre !

    Comme tu parles de ENUM, je suppose que tu utilises MySQL qui n'est pas non plus la meilleure idée avec des tables aussi grosses !

    Donne la description exacte et complète de tes tables (Résultat de SHOW CREATE TABLE la_table si tu utilises MySQL) ainsi que le plan d'exécution comme l'a demandé Waldar (Résultat de EXPLAIN texte_de_la_requête si tu utilises MySQL).

    As-tu essayé la mienne ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « 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
    Membre expérimenté
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 550
    Points
    550

    Par défaut

    Citation Envoyé par Waldar Voir le message
    Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
        SELECT community.id
             , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
             , COUNT(user.id)
          FROM community
     LEFT JOIN community_users
    INNER JOIN user
            ON user.id = user_id
            ON community.id = community_id
      GROUP BY community.id
    Si ce n'est pas le cas, pas de soucis.
    Si ce n'est pas le cas, vous pouvez surement enlever une jointure :

    Code :
    1
    2
    3
    4
    5
    6
    7
        SELECT community_users.community_id
             , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
             , COUNT(user.id)
          FROM community_users
    INNER JOIN user
            ON user.id = user_id
      GROUP BY community_users.community_id

  13. #13
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Beaucoup de reponses d'un seul coup, merci ! Aussi je prends tout dans l'ordre !

    Citation Envoyé par Waldar Voir le message
    Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
        SELECT community.id
             , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
             , COUNT(user.id)
          FROM community
     LEFT JOIN community_users
    INNER JOIN user
            ON user.id = user_id
            ON community.id = community_id
      GROUP BY community.id
    Si ce n'est pas le cas, pas de soucis.

    Maintenant, vos temps d'exécution sont beaucoup trop élevés.
    Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
    Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?
    Je n'ai pas de communautes sans utilisateurs, donc le probleme ne se pose pas je dirais.
    Pour ce qui est de ma configuration materielle, c'est une base Postgresql qui tourne sur ma machine de bureau (QuadCore i5, 8Go de RAM, local HD 7200tr/min) sans rien de particulier. C'est un petit projet personnel, donc pas les moyens d'avoir un serveur dedie avec SSD et toutes les petites options sympathiques !

    Citation Envoyé par CinePhil Voir le message
    Mauvaise idée !
    Il est bien plus long pour le SGBD d'évaluer 'male' et 'female' qu'un simple chiffre !

    Comme tu parles de ENUM, je suppose que tu utilises MySQL qui n'est pas non plus la meilleure idée avec des tables aussi grosses !

    Donne la description exacte et complète de tes tables (Résultat de SHOW CREATE TABLE la_table si tu utilises MySQL) ainsi que le plan d'exécution comme l'a demandé Waldar (Résultat de EXPLAIN texte_de_la_requête si tu utilises MySQL).

    As-tu essayé la mienne ?
    Je croyais que les Enums dans Postgresql etaient justement la dans un soucis de performances, et etaient plus elegantes qu'un mapping en dur entre entiers et strings.

    Pour ce qui est de mon schema, voici celui qui est reduit a mes 3 tables.

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    CREATE TABLE community
    (
      id serial NOT NULL,
      name text NOT NULL,
      category_id integer NOT NULL,
      last_visit timestamp WITH time zone NOT NULL,
      CONSTRAINT community_pkey PRIMARY KEY (id ),
      CONSTRAINT community_category_id_fk FOREIGN KEY (category_id)
          REFERENCES category (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    )
     
    CREATE INDEX ix_community_category_id
      ON community
      USING btree
      (category_id );
     
    CREATE INDEX ix_community_last_visit
      ON community
      USING btree
      (last_visit );
     
    --------------------------------------------------
     
    CREATE TABLE user
    (
      id serial NOT NULL,
      profile_name text,
      name text,
      sex sex,
      age integer,
      birthday date,
      last_visit timestamp WITH time zone,
      location_id integer,
      hometown_id integer,
      work_id integer,
      valid BOOLEAN NOT NULL,
      CONSTRAINT user_pkey PRIMARY KEY (id ),
      CONSTRAINT user_hometown_id_fk FOREIGN KEY (hometown_id)
          REFERENCES place (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT user_location_id_fk FOREIGN KEY (location_id)
          REFERENCES place (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT user_work_id_fk FOREIGN KEY (work_id)
          REFERENCES work (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    )
     
    CREATE INDEX ix_user_age
      ON user
      USING btree
      (age );
     
    CREATE INDEX ix_user_hometown_id
      ON user
      USING btree
      (hometown_id );
     
    CREATE INDEX ix_user_last_visit
      ON user
      USING btree
      (last_visit );
     
    CREATE INDEX ix_user_location_id
      ON user
      USING btree
      (location_id );
     
    CREATE INDEX ix_user_sex
      ON user
      USING btree
      (sex );
     
    CREATE INDEX ix_user_valid
      ON user
      USING btree
      (valid );
     
    CREATE INDEX ix_user_work_id
      ON user
      USING btree
      (work_id );
     
    --------------------------------------------------
     
    CREATE TABLE user_communities__community_users
    (
      community_id integer NOT NULL,
      user_id integer NOT NULL,
      CONSTRAINT user_communities__community_users_pkey PRIMARY KEY (community_id , user_id ),
      CONSTRAINT community_users_fk FOREIGN KEY (community_id)
          REFERENCES community (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE CASCADE,
      CONSTRAINT user_communities_fk FOREIGN KEY (user_id)
          REFERENCES user (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE CASCADE
    )
     
    CREATE INDEX ix_user_communities__community_users_community_id
      ON user_communities__community_users
      USING btree
      (community_id );
     
    CREATE INDEX ix_user_communities__community_users_user_id
      ON user_communities__community_users
      USING btree
      (user_id );
    Citation Envoyé par Fred_34 Voir le message
    Si ce n'est pas le cas, vous pouvez surement enlever une jointure :

    Code :
    1
    2
    3
    4
    5
    6
    7
        SELECT community_users.community_id
             , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
             , COUNT(user.id)
          FROM community_users
    INNER JOIN user
            ON user.id = user_id
      GROUP BY community_users.community_id
    Le but etait de garder la jointure au cas ou je voudrais rajouter une condition sur "community", comme la categorie par exemple.

  14. #14
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    pour ce qui est des plans d'execution :

    pour la requete
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    SELECT community.id, 
        SUM (
            CASE 
                WHEN age < 30 AND sex='female' THEN 1
                ELSE 0
            END
        ),
        COUNT (
            user.id
        )
    FROM community
    INNER JOIN
    user_communities__community_users ON community.id = community_id
    INNER JOIN
    user ON user.id = user_id
    GROUP BY community.id
    j'obtiens

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    "GroupAggregate  (cost=185445412.85..201250011.61 rows=2768883 width=16)"
    "  ->  Merge Join  (cost=185445412.85..194636413.18 rows=526872768 width=16)"
    "        Merge Cond: (community.id = user_communities__community_users.community_id)"
    "        ->  Sort  (cost=467819.39..474741.60 rows=2768883 width=4)"
    "              Sort Key: community.id"
    "              ->  Seq Scan on community  (cost=0.00..57968.83 rows=2768883 width=4)"
    "        ->  Materialize  (cost=184937781.61..187572145.45 rows=526872768 width=16)"
    "              ->  Sort  (cost=184937781.61..186254963.53 rows=526872768 width=16)"
    "                    Sort Key: user_communities__community_users.community_id"
    "                    ->  Hash Join  (cost=1781904.65..54587634.85 rows=526872768 width=16)"
    "                          Hash Cond: (user_communities__community_users.user_id = user.id)"
    "                          ->  Seq Scan on user_communities__community_users  (cost=0.00..7601473.68 rows=526872768 width=8)"
    "                          ->  Hash  (cost=872599.62..872599.62 rows=52310562 width=12)"
    "                                ->  Seq Scan on user  (cost=0.00..872599.62 rows=52310562 width=12)"
    Pour
    Code :
    1
    2
    3
    4
    SELECT id,
    (SELECT COUNT(id) FROM user, user_communities__community_users WHERE id=user_id AND community_id=community.id),
    (SELECT COUNT(id) FROM user, user_communities__community_users WHERE id=user_id AND age<30 AND sex='female' AND community_id=community.id)
    FROM community
    j'ai
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    "Seq Scan on community  (cost=0.00..2239730798282.68 rows=2768883 width=4)"
    "  SubPlan 1"
    "    ->  Aggregate  (cost=404415.58..404415.59 rows=1 width=4)"
    "          ->  Nested Loop  (cost=0.00..404353.84 rows=24696 width=4)"
    "                ->  Index Scan using ix_user_communities__community_users_community_id on user_communities__community_users  (cost=0.00..59978.63 rows=24696 width=4)"
    "                      Index Cond: (community_id = community.id)"
    "                ->  Index Scan using user_pkey on user  (cost=0.00..13.93 rows=1 width=4)"
    "                      Index Cond: (id = public.user_communities__community_users.user_id)"
    "  SubPlan 2"
    "    ->  Aggregate  (cost=404477.64..404477.65 rows=1 width=4)"
    "          ->  Nested Loop  (cost=0.00..404477.32 rows=126 width=4)"
    "                ->  Index Scan using ix_user_communities__community_users_community_id on user_communities__community_users  (cost=0.00..59978.63 rows=24696 width=4)"
    "                      Index Cond: (community_id = community.id)"
    "                ->  Index Scan using user_pkey on user  (cost=0.00..13.94 rows=1 width=4)"
    "                      Index Cond: (id = public.user_communities__community_users.user_id)"
    "                      Filter: ((age < 30) AND (sex = 'female'::sex))"

  15. #15
    Futur Membre du Club
    Inscrit en
    mars 2005
    Messages
    99
    Détails du profil
    Informations personnelles :
    Âge : 31

    Informations forums :
    Inscription : mars 2005
    Messages : 99
    Points : 15
    Points
    15

    Par défaut

    Citation Envoyé par Waldar Voir le message
    Maintenant, vos temps d'exécution sont beaucoup trop élevés.
    Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
    Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?
    Je m'excuse de revenir sur cette phrase en particulier, mais n'ayant pas beaucoup d'experience dans le domaine des bases de donnees, je me demande.
    Quel est l'ordre de grandeur auquel je devrais m'attendre pour ce genre de requete ? Parce qu'a par exemple 10000 lectures par seconde (ce qui est assez rapide je trouve), il me faudrait plus de 12 heures rien que pour lister le contenu de user_communities__community_users. Bien sur je me doute que les index et autres accelerent grandement les performances, mais dans la mesure ou j'ai du mal a savoir a quoi m'attendre, c'est difficile de pouvoir dire si mes requetes sont lentes ou pas

    Pour ce qui est des 2 explain, je soupconne que la premiere requete fait un join sur user_communities__community_users dans son integralite, alors que la seconde ne traite que des sous-parties, ce qui devrait etre bien plus leger sur des tables de cette taille. J'ai bon ?

  16. #16
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 281
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 281
    Points : 27 290
    Points
    27 290

    Par défaut

    Les temps de réponse d'une SGBDR ne sont absolument pas linéaires à la volumétrie. En effet :
    1) du fait des index les traitement sont plutôt de cout logarithmique
    2) le changement de volumétrie induit souvent un changement de plan de requête
    Pour le 2, par exemple une jointure à faible volumétrie se traduira par un algorithme de boucle imbriquées, alors qu'avec une forte volumétrie cela va conduire à une fusion ou bien un hachage.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •