optimisation d'une vue avec plusieurs sous-requêtes

Version imprimable

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:

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

03/08/2012, 10h50
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.

Quelque chose du genre :

Code:

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
)

07/08/2012, 10h10
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.
07/08/2012, 10h55
Sergejack

Citation:

Envoyé par jean62

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).
08/08/2012, 10h49
jean62
Citation:

Envoyé par Sergejack

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:

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:

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

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

Code:

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)

Citation:

Envoyé par 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:

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:

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