Discussion :

[jean62]

Bonjour,

je dois définir une vue sur des tables A et B
La table B doit être utilisée plusieurs fois avec A avec des conditions différentes.
J'utilise donc des sous-requêtes.
Voici un extrait de mon code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
...
(SELECT SUM(B.heures) FROM B
    WHERE B.col1 IN ('2','3') AND B.col2='NE' AND A.ID=B.ID) 
    AS req_B_1,
(SELECT SUM(B.montant) FROM B
    WHERE B.col1 IN ('2','3') AND B.col2='NE' AND AND A.ID=B.ID) 
    AS req_B_2,
(SELECT SUM(B.heures) FROM B
    WHERE B.col1='5' AND STR_TYPE='NE' AND AND A.ID=B.ID) 
    AS req_B_3,
(SELECT SUM(B.montant) FROM B
    WHERE SB.col1='5' AND STR_TYPE='NE' AND AND A.ID=B.ID) 
    AS req_B_4,
...

Le problème est que j'ai environ 20 sous-requêtes comme celles ci-dessus, et que cela plombe le temps d'exécution. D'autant plus qu'on ne peut retourner qu'une seule valeur par requête: dans mon exemple, j'utilise 2 sous-requêtes avec les mêmes conditions pour récupérer le nombre d'heures et le montant . De plus B comporte plusieurs millions de lignes.
Y-a-t-il une solution plus performante ? (utiliser une table CTE qui ne comporterait que les lignes A.ID=B.ID ?)

Merci à l'avance de votre aide
Jean

[SQLDev]

Bonjour,

Pourrais tu nous fournir le DDL des 2 tables ?
Et la totalité des colonnes que tu dois retourner ?

Ces informations sont essentielles pour déterminer une solution optimisée, surtout pour l'utilisation des index.

[Sergejack]

Quelque chose du genre :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT
 SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.heures END) AS req_B_1
 , SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.montant END) AS req_B_2
 , ...
FROM A
INNER JOIN B ON (
 A.ID=B.ID
)

[jean62]

Merci SergeJack,

Mais l'utilisation de CASE...WHEN augmente le temps de réponse énormément.
Concernant la table qui fait 5 millions d'enregistrements, elle ne comporte ni d clé primaire ni index !
Je vais voir si j'ai l'autorisation d'en rajouter.

[Sergejack]

Merci SergeJack,

Mais l'utilisation de CASE...WHEN augmente le temps de réponse énormément.

Ha.
Ce n'est pas logique, surtout en l'absence de clés.

Est-ce que vous pourriez mettre au complet les deux query, celui que vous aviez à l'origine et celui que je suggérai.

À mon avis vous avez oubliez des filtres dans le seconds et pourtant présents dans le premier (genre : WHERE A.ID = 5).

[jean62]

Ha.
Ce n'est pas logique, surtout en l'absence de clés.

Est-ce que vous pourriez mettre au complet les deux query, celui que vous aviez à l'origine et celui que je suggérai.

À mon avis vous avez oubliez des filtres dans le seconds et pourtant présents dans le premier (genre : WHERE A.ID = 5).

Voici mon code:

Code :

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

1
2
3
 
SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.heures END) AS req_B_1,
SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.montant END) AS  req_B_2,

avec la jointure suivante:

Code :

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

1
2
LEFT OUTER JOIN B
ON  A_ID = B_ID

Je ne pense pas m'etre trompé. Mais je vous rappelle que j'ai 17 SUM(CASE...)
dans le code, et que je ramène par ailleurs plus de 50 colonnes provenant de 5 tables différentes. D'autre part, votre méthode oblige à l'agrégat GROUP BY sur tous les autres champs.
Merci de votre patience.
Jean

[Rei Ichido]

Il vaut mieux faire les sommes avant la jointure (et ainsi s'éviter le group by l'ensemble des colonnes ramenées) :

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
 
WITH sumB AS
(
SELECT B_ID
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.heures END) AS req_B_1
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.montant END) AS  req_B_2
           ...
  FROM B
 WHERE ..
 GROUP BY B_ID
)
SELECT req_B_1
          ,req_B_2
          , ...
  FROM A
          LEFT OUTER JOIN sumB ON (sumB.B_ID= A.A_ID)

[jean62]

Il vaut mieux faire les sommes avant la jointure (et ainsi s'éviter le group by l'ensemble des colonnes ramenées) :

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
 
WITH sumB AS
(
SELECT B_ID
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.heures END) AS req_B_1
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.montant END) AS  req_B_2
           ...
  FROM B
 WHERE ..
 GROUP BY B_ID
)
SELECT req_B_1
          ,req_B_2
          , ...
  FROM A
          LEFT OUTER JOIN sumB ON (sumB.B_ID= A.A_ID)

J'ai pensé à cela, mais à ma connaissance, on ne peut insérer une table CTE dans une vue avec plusieurs requêtes. Ou alors il faut procéder comme cela:

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
WITH sumB AS
(
SELECT B_ID
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.heures END) AS req_B_1
          ,SUM(CASE WHEN B.col1 IN ('2','3') AND B.col2='NE' THEN B.montant END) AS  req_B_2
           ...
  FROM B
 WHERE ..
 GROUP BY B_ID
),
C as
(
SELECT C_ID, C.Col1, C.Col2...
  FROM C
 WHERE ..
 
),
D as
(
SELECT D_ID, D.Col1, D.Col2...
  FROM D
 WHERE ..
 
),
E as ...
SELECT B.req_B_1
          ,B.req_B_2
          , C.Col1
          , C.Col2
          , ...
          , D.Col1
          , D.Col2
          , E....
  FROM A
          LEFT OUTER JOIN sumB ON (sumB.B_ID= A.A_ID)
          LEFT OUTER JOIN C ON (C.C_ID= A.A_ID)
          LEFT OUTER JOIN D ON (D.D_ID= A.A_ID)
          ...

travail fastidieux et je ne sais pas si cela serait plus performant.
Merci Rei Ichido pour votre aide
Jean