Discussion :

[npopravka]

Bonjour, je patauge desespéremment....

3 tables :
utilisateurs (id,prenom,nom...)
itemforum (id,idutilisateur,date,titre....)
itembibliotheque (id,idutilisateur,date,titre....)

Les deux tables itemforum et itembibliotheque ont une structure strictement identique.

Je souhaite connaître, pour chaque utilisateur, le nombre d'éléments lui correspondant dans le forum, ainsi que dans la bibliotheque.

Pour le forum tout seul, pas de pb :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT u.id,u.nom,COUNT( f.id)AS nbforum
FROM utilisateur AS u
INNER JOIN itemforum AS f ON u.id=f.idutilisateur
GROUP BY u.id,u.nom
ORDER BY forum;

En revanche, si je tente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT u.id,u.nom,COUNT( f.id)AS nbforum,COUNT( b.id)AS nbbibli
FROM utilisateur AS u
INNER JOIN itemforum AS f ON u.id=f.idutilisateur
INNER JOIN itembibliotheque AS b ON u.id=b.idutilisateur
GROUP BY u.id,u.nom
ORDER BY forum;

...j'obtiens des chiffres délirants genre multiplication des pains, ce qui en plus me semble pas illogique avec le peu que je connais de SQL.

J'ai tenté de ruser en déclarant deux fois utilisateur (u1,u2), avec une autojointure, et un join sur u1 pour le forum et u2 pour la bibli, pas mieux.

Je suis en train de me dire que les deux tables étant identiques, j'aurais peut-être une chance avec une vue les réunissant ? Ou c'est idiot ?

Bref au secours merci beaucoup d'avance :-)

PS : c'est Postgresql 8.4

[al1_24]

Tu as raison ; telle que tu montais ta requête, il y avait un produit entre les tables itemforum et itembibliotheque.
Il faut effectuer au moins un des deux comptages dans une table dérivée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT  u.id
    ,   u.nom
    ,   COUNT(f.id) AS nbforum
    ,   COUNT(b.id) AS nbbibli
FROM    utilisateur AS u1
    INNER JOIN 
        itemforum AS f 
        ON  u.id = f.idutilisateur
    INNER JOIN
        (   SELECT  idutilisateur
                ,   COUNT(id) AS nbbibli
            FROM    itembibliotheque
            GROUP BY idutilisateur 
        ) AS b 
        ON u.id = b.idutilisateur
GROUP BY u.id
    ,   u.nom
ORDER BY forum
;

[npopravka]

Il faut effectuer au moins un des deux comptages dans une table dérivée.

Merci beaucoup, je teste ça cet AM.
Je savais bien que j'aurais du regarder de plus près le chapitre sur les sous-requêtes :-)

[StringBuilder]

Tu peux aussi faire comme ça, ce que je trouve plus lisible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select u.id, u.prenom, u.nom, (select count(*) from itemforum f where f.idutilisateur = u.id) nbForum, (select count(*) from itembibliotheque b where b.idutilisateur = u.id) nbBibliotheque
from utilisateurs u

[Waldar]

Ou comment tuer les performances d'une requête !

[StringBuilder]

La requête de a1_24 corrigée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT  u1.id
    ,   u1.nom
    ,   COUNT(f.id) AS nbforum
    ,   sum(b.nbbibli) AS nbbibli
FROM    utilisateur AS u1
    INNER JOIN 
        itemforum AS f 
        ON  u1.id = f.idutilisateur
    INNER JOIN
        (   SELECT  idutilisateur
                ,   COUNT(id) AS nbbibli
            FROM    itembibliotheque
            GROUP BY idutilisateur 
        ) AS b 
        ON u1.id = b.idutilisateur
GROUP BY u1.id
    ,   u1.nom
ORDER BY nbforum

(parce que non seulement est était truffée de fautes, mais en plus elle faisait pas du tout ce qu'il fallait)

Ensuite, niveau perfs, la requête de a1_24 donne la plan d'exécution "Plan 1" ci-joint.

Et la mienne, "Plan 2" ci-joint.

Plans d'exécutions tirés de SQL Server 2008 Express

A moins que je ne sache lire un plan d'exécution, ma requête est plus de 3 fois plus rapide... (la lecture en clustered index scan des trois tables prend chez moi 100% du temps, alors que chez a1_24, ça ne prends que 30% du temps... laissant présager de longs traîtements par la suite (genre des tris à n'en plus finir)

Jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
drop table itembibliotheque
go
 
drop table itemforum
go
 
drop table utilisateur
go
 
create table utilisateur (
  id int identity(1,1) primary key, 
  prenom varchar(30), 
  nom varchar(30)
)
go
 
create table itemforum (
  id int identity(1,1) primary key,
  idutilisateur int references utilisateur (id),
  titre varchar(30)
)
go
 
create table itembibliotheque (
  id int identity(1,1) primary key,
  idutilisateur int references utilisateur (id),
  titre varchar(30)
)
go
 
insert into utilisateur (nom, prenom) values ('Toto', 'Lapraline');
insert into utilisateur (nom, prenom) values ('Alfonse', 'Dupont');
 
insert into itemforum (idutilisateur, titre) values (1, 'topic 1');
insert into itemforum (idutilisateur, titre) values (2, 'topic 2');
 
insert into itembibliotheque (idutilisateur, titre) values (1, 'livre 1');
insert into itembibliotheque (idutilisateur, titre) values (2, 'livre 2');
insert into itembibliotheque (idutilisateur, titre) values (2, 'livre 3');

[Waldar]

La requête scalaire que vous avez écrite est plus rapide si vous devez afficher les lignes au fur et à mesure, par exemple en 100 par 100 dans une application, car au final la requête ne va pas au bout.

La requête avec les vues imbriquées (ou tables dérivées) sera beaucoup plus rapide pour traiter toutes les données.

J'ai fait un test entre une table produit et une table de vente agrégée par produit / jour.

Au niveau volumétrie : 2.500.000 produits, 4.000.000 de vente.
La requête avec la jointure a duré 27 secondes, j'ai annulé l'exécution de la requête scalaire au bout de 12 minutes.

Par contre, cette dernière s'affiche instantanément pour les 30.000 premiers produits.

Je vous accorde avoir été un peu trop catégorique, en fonction des besoins et volumétrie ce n'est pas toujours vrai. Mais d'une façon générale, il vaut mieux apprendre à coder en ensembliste.