Optimisation de requete

**hamoud** · 20/11/2009, 11h57

Bonjour,

Je sollicite votre aide aujourd'hui pour m'aider à optimiser ma requete et avoir votre avis sur ma conception.
voila 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
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
 
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
       SUM(NVL(POP_PREVMAG.PREVPROP, 0)) AS PREVPROP,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, NVL(POP_PREVMAG.PREVSAISIE,  CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR,
         CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, 0))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR,
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT
FROM  ACCPUB
         LEFT JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
         LEFT JOIN GAM_ACT
         ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
               AND GAM_ACT.DAT_DEB = (SELECT MAX(dat_deb) FROM GAM_ACT g1 WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
                                                                                AND g1.role_pdt = GAM_ACT.role_pdt
                                                                                AND g1.dat_deb <= PUB.DAT_DEB
                                                                                AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN)),
      PRODUIT,
      FAMILLE,
      SEGMENT,
      FOURN,
      POP_CAMP_PUB,
      FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB,
      POP_PDTPOP
            LEFT JOIN SAS_PDT
 
                LEFT JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
                LEFT JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
            ON POP_PDTPOP.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
            LEFT JOIN POP_PREVMAG
                LEFT JOIN SAS_PREVAPPRO
                ON (POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG)
            ON POP_PDTPOP.NO_ACPUB = POP_PREVMAG.NO_ACPUB
WHERE 1 = 1
      -- Jointure ACCPUB -> PRODUIT
      AND ACCPUB.COD_PDT = PRODUIT.COD_PDT
      -- Jointure PRODUIT -> FAMILLE
      AND PRODUIT.COD_FAM = FAMILLE.COD_FAM
      -- Jointure PRODUIT -> SEGMENT
      AND PRODUIT.COD_MAR = SEGMENT.COD_SEG
      -- Jointure PRODUIT -> FOURN
      AND PRODUIT.COD_FRS = FOURN.COD_FRS
      -- Jointure PUB -> POP_CAMP_PUB
      AND (PUB.NO_PUB = POP_CAMP_PUB.NO_PUB AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB)
      -- Jointure POP_CAMP_PUB -> FMK_ETAT_HIST_PUB
      AND POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID
 
      AND ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
      AND FMK_ETAT_HIST_PUB.ID_ETAT = 611
      AND ACCPUB.COD_DEST='MA'

A votre avis est ce qu'on peut optimiser cette requete ? sachat que je voudrai si c'est possible éviter de la décomposer car mon application qui utilise cette vue est compliqué.

Merci beaucoup d'avance de votre aide

**hamoud** · 20/11/2009, 15h15

oops!
j'ai oublié les agrégations la vraie requete a optimiser est:

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
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
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
       SUM(NVL(POP_PREVMAG.PREVPROP, 0)) AS PREVPROP,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, NVL(POP_PREVMAG.PREVSAISIE,  CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR,
         CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, 0))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR,
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT
FROM  ACCPUB
         LEFT JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
         LEFT JOIN GAM_ACT
         ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
               AND GAM_ACT.DAT_DEB = (SELECT MAX(dat_deb) FROM GAM_ACT g1 WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
                                                                                AND g1.role_pdt = GAM_ACT.role_pdt
                                                                                AND g1.dat_deb <= PUB.DAT_DEB
                                                                                AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN)),
      PRODUIT,
      FAMILLE,
      SEGMENT,
      FOURN,
      POP_CAMP_PUB,
      FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB,
      POP_PDTPOP
            LEFT JOIN SAS_PDT
 
                LEFT JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
                LEFT JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
            ON POP_PDTPOP.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
            LEFT JOIN POP_PREVMAG
                LEFT JOIN SAS_PREVAPPRO
                ON (POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG)
            ON POP_PDTPOP.NO_ACPUB = POP_PREVMAG.NO_ACPUB
WHERE 1 = 1
      -- Jointure ACCPUB -> PRODUIT
      AND ACCPUB.COD_PDT = PRODUIT.COD_PDT
      -- Jointure PRODUIT -> FAMILLE
      AND PRODUIT.COD_FAM = FAMILLE.COD_FAM
      -- Jointure PRODUIT -> SEGMENT
      AND PRODUIT.COD_MAR = SEGMENT.COD_SEG
      -- Jointure PRODUIT -> FOURN
      AND PRODUIT.COD_FRS = FOURN.COD_FRS
      -- Jointure PUB -> POP_CAMP_PUB
      AND (PUB.NO_PUB = POP_CAMP_PUB.NO_PUB AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB)
      -- Jointure POP_CAMP_PUB -> FMK_ETAT_HIST_PUB
      AND POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID
 
      AND ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
      AND FMK_ETAT_HIST_PUB.ID_ETAT = 611
      AND ACCPUB.COD_DEST='MA'
GROUP BY
       ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ,
       POP_PDTPOP.BASE100,
       FMK_PARAM_PDT_SAS.CODE,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       ACCPUB.UNIT_CDE,PRODUIT.PCB,
       GAM_ACT.ROLE_PDT

mon objectif est dans un premier temps répérer le morceau de requete qui boufe le plus de temps.
merci pour toutes vos suggestions

**hamoud** · 23/11/2009, 10h20

Je vous informe que j'ai des index sur chacune des tables qui participent aux jointures, trop d'index peut être?
Cette requete fait actuellement 14 secondes voir plus, du coup des que le nombre d'utilisateurs connecté à mon appli dépasse le 20 ils commencent à raller ;mon appli se relantisse voir se plante

Aidez moi svp, j'ai beaucoup chercher sur google sans beaucoup de reussites

J'attend avec impatience toutes vos propositions

**gangsoleil** · 23/11/2009, 12h56

Bonjour,

A priori, tu fais trop de jointures, ce qui ralentit ton systeme. N'as tu pas la possibilite de changer le schema de la base, pour le rendre plus performant ?

**pacmann** · 23/11/2009, 13h19

Salut !

A priori, les jointures vont vite si tu as peux de lignes à récupérer, avec les bons index...

Il faut le plan d'exécution pour savoir où se situe le problème !

Au passage, ça sera plus compréhensible tu écris toutes les jointures de la même manière, et non pas seulement les jointures externe

**CinePhil** · 23/11/2009, 14h02

Ca fonctionne ça ?

Code :

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

1
2
3
SELECT
/*...*/
ACCPUB.COD_MAJ <> "A" AS VALIDE_POP

Voici la requête réécrite avec la syntaxe normalisée depuis 1992 pour les jointures :

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
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
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
       SUM(NVL(POP_PREVMAG.PREVPROP, 0)) AS PREVPROP,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, NVL(POP_PREVMAG.PREVSAISIE,  CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR,
         CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, 0))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR,
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT
FROM  ACCPUB
  LEFT JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
    INNER JOIN POP_CAMP_PUB 
      ON PUB.NO_PUB = POP_CAMP_PUB.NO_PUB 
    AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB
      INNER JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB ON POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID
  LEFT JOIN GAM_ACT
    ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
      AND GAM_ACT.DAT_DEB = (
    SELECT MAX(dat_deb) 
    FROM GAM_ACT g1 
    WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
      AND g1.role_pdt = GAM_ACT.role_pdt
      AND g1.dat_deb <= PUB.DAT_DEB
      AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN)
      )
INNER JOIN PRODUIT ON ACCPUB.COD_PDT = PRODUIT.COD_PDT
  INNER JOIN FAMILLE ON PRODUIT.COD_FAM = FAMILLE.COD_FAM
  INNER JOIN SEGMENT ON PRODUIT.COD_MAR = SEGMENT.COD_SEG
  INNER JOIN FOURN ON PRODUIT.COD_FRS = FOURN.COD_FRS
INNER JOIN POP_PDTPOP ON ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
  LEFT JOIN SAS_PDT ON POP_PDTPOP.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
    LEFT JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
      LEFT JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
  LEFT JOIN POP_PREVMAG ON POP_PDTPOP.NO_ACPUB = POP_PREVMAG.NO_ACPUB
    LEFT JOIN SAS_PREVAPPRO
      ON POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB 
    AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG
WHERE FMK_ETAT_HIST_PUB.ID_ETAT = 611
  AND ACCPUB.COD_DEST = 'MA'
GROUP BY
  ACCPUB.NO_ACPUB, ACCPUB.NO_PUB, ACCPUB.EX_PUB,
  PRODUIT.COD_PDT, PRODUIT.LIB_COUR, PRODUIT.COD_FAM, 
  FAMILLE.LIB_FAM,
  SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
  FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
  ACCPUB.COD_MAJ,
  POP_PDTPOP.NIVEAU_PUB,
  ACCPUB.QTE_BLQ,
  POP_PDTPOP.BASE100,
  FMK_PARAM_PDT_SAS.CODE,
  SAS_PDT.EXPOSITION,
  SAS_PDT.SPECIFIQUE,
  SAS_PDT.ID_PARAM_TYPE_PLAN,
  ACCPUB.UNIT_CDE,PRODUIT.PCB,
  GAM_ACT.ROLE_PDT

Quant à la simplifier...
Toutes les jointures sont-elles nécessaires ?
Difficile d'apporter des idées sans comprendre ce que cette requête est censée faire, sans connaître le schéma de la BDD ni le plan d'exécution de la requête.

**hamoud** · 23/11/2009, 14h16

Bonjour,

Merci gangsoleil et pacmann pour vos réponses
et pour te répondre gangsoleil, je peux pas justement changer le schéma par ce que mon appli vient se grefer sur une vieille base à laquelle se greffe plein d'autres appli donc cette option est diffilement applicable .
pacman, effectivement le nombre de lignes que retourne cette requette n'est pas enorme(150 lignes me suffit) j'ai essayé d'ajouter un limit apres mon select mais informix l'aime pas dans le context de creation de ma vue....

Au passage, ça sera plus compréhensible tu écris toutes les jointures de la même manière, et non pas seulement les jointures externe

c'est par ce que j'ai remarqué que je gagne qq secondes en faisant comme ça c'est bizarre mais c'est comme ça.
par exemple celle est plus consommatrice

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
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
 
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
       SUM(NVL(POP_PREVMAG.PREVPROP, 0)) AS PREVPROP,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, NVL(POP_PREVMAG.PREVSAISIE,  CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, 0))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR,
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT
FROM  ACCPUB
INNER  JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
         LEFT JOIN GAM_ACT
         ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
         AND GAM_ACT.DAT_DEB = (SELECT MAX(dat_deb) FROM GAM_ACT g1 WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
                                                                                AND g1.role_pdt = GAM_ACT.role_pdt
                                                                                AND g1.dat_deb <= PUB.DAT_DEB
                                                                                AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN))
-- Jointure ACCPUB -> PRODUIT  
INNER JOIN PRODUIT
    ON ACCPUB.COD_PDT = PRODUIT.COD_PDT
    AND ACCPUB.COD_DEST='MA'  
-- Jointure PRODUIT -> FAMILLE
INNER JOIN    FAMILLE
     ON      PRODUIT.COD_FAM = FAMILLE.COD_FAM
 -- Jointure PRODUIT -> SEGMENT
 INNER JOIN     SEGMENT
    ON     PRODUIT.COD_MAR = SEGMENT.COD_SEG
-- Jointure PRODUIT -> FOURN
INNER JOIN    FOURN
      ON PRODUIT.COD_FRS = FOURN.COD_FRS
-- Jointure PUB -> POP_CAMP_PUB
INNER JOIN POP_CAMP_PUB
    ON (PUB.NO_PUB = POP_CAMP_PUB.NO_PUB AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB)
-- Jointure POP_CAMP_PUB -> FMK_ETAT_HIST_PUB
INNER JOIN   FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB
    ON POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID 
    AND FMK_ETAT_HIST_PUB.ID_ETAT = 611       
 -- Jointure ACCPUB -> POP_PDTPOP
INNER JOIN  POP_PDTPOP 
    ON ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
 
   LEFT  JOIN SAS_PDT
            ON ACCPUB.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
      LEFT  JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS 
            ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
      LEFT  JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS
             ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
 
     LEFT JOIN POP_PREVMAG
                ON ACCPUB.NO_ACPUB = POP_PREVMAG.NO_ACPUB
          LEFT JOIN SAS_PREVAPPRO
                ON (POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG)
 
 
 
GROUP BY
       ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ,
       POP_PDTPOP.BASE100,
       FMK_PARAM_PDT_SAS.CODE,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       ACCPUB.UNIT_CDE,PRODUIT.PCB,
       GAM_ACT.ROLE_PDT;

comment je peux consulter le plan d'execution sachant je suis sous db visualize personnal 5.0
version de la base : informix 9.50.UC8
xp
quelqu'un a t'il d'autre propositions?

**pacmann** · 23/11/2009, 14h24

Hmm, normalement, l'utilisation des jointures normées ne devrait pas changer le plan (et donc mettre autant de temps mais pas plus).

Pour le plan d'exécution, je ne connais pas informix... mais j'ai tenté google

http://publib.boulder.ibm.com/infoce...oc/perf281.htm

**hamoud** · 23/11/2009, 14h55

salut CinePhil,
avec ta requete, j'applique un filtre typique comme

Code :

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

accpub.no_pub='0022' and accpub.ex_pub='2008' and famille.cod_fam

j'ai 113 lignes en 9.90 secondes
avec ma requete d'origines, j'obtiens les résultats en 15.20 secondes
donc un pas déjà vers l'avant ...

Ca fonctionne ça ?

Code :
SELECT
/*...*/
ACCPUB.COD_MAJ <> "A" AS VALIDE_POP

ça marche parfaitement.

Toutes les jointures sont-elles nécessaires ?

Oui
Pour le plan d'execution je vais aller le chercher sur le serveur mais c'est pas évident car j'ai plein de sqexplain.out (sous unix) mais je n'arrive a trouver celui qui correspond a ma requette?

**SQLpro** · 23/11/2009, 15h22

1) faites une vue avec toutes les jointures sans les agrégats avec des jointures sous forme de JOIN
2) sur cette vue calculez les agrégats
3) placez des index couvrants sur les principales tables et notamment pour les colonnes de jointure + FMK_ETAT_HIST_PUB.ID_ETAT et ACCPUB.COD_DEST

Pour savoir ce qu'est un index couvrant, lisez l'article que j'ai écrit au sujet de l'indexation : http://sqlpro.developpez.com/cours/quoi-indexer/

A +

**hamoud** · 23/11/2009, 18h07

Au final
voila ma requete défintive
1-je crée la vue sans les agrégats

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
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
 
 --vu sans les agregats
 
 create view v_pop_test_sas(
 NO_ACPUB,
 NO_PUB,
 EX_PUB,
 COD_PDT,  LIB_COUR,
 COD_FAM,  LIB_FAM,
 COD_SEG,  LIB_SEG,
 COD_FRS,  LIB_FRS,  REF_FOUR,
       VALIDE_POP,
 NIVEAU_PUB,
 QTE_BLQ, 
 BASE100,
 PREVPROP ,
 PREVSAISIE ,
 
 cod_mag,  STATUTSTOCK,
      PREVISIONAPPRO,
       ETAT_SAS,
       EXPOSITION,
   SPECIFIQUE,
     ID_PARAM_TYPE_PLAN,
       PCB,
 ROLE_PDT,ID_ETAT,COD_DEST )
as
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
        POP_PREVMAG.PREVPROP ,
        POP_PREVMAG.PREVSAISIE ,
         pop_prevmag.cod_mag, POP_PREVMAG.STATUTSTOCK,
        SAS_PREVAPPRO.PREVISIONAPPRO , 
 
 
 
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT,FMK_ETAT_HIST_PUB.ID_ETAT  
,ACCPUB.COD_DEST  
FROM  ACCPUB
  LEFT JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
    INNER JOIN POP_CAMP_PUB 
      ON PUB.NO_PUB = POP_CAMP_PUB.NO_PUB 
    AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB
      INNER JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB ON POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID
  LEFT JOIN GAM_ACT
    ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
      AND GAM_ACT.DAT_DEB = (
    SELECT MAX(dat_deb) 
    FROM GAM_ACT g1 
    WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
      AND g1.role_pdt = GAM_ACT.role_pdt
      AND g1.dat_deb <= PUB.DAT_DEB
      AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN)
      )
INNER JOIN PRODUIT ON ACCPUB.COD_PDT = PRODUIT.COD_PDT
  INNER JOIN FAMILLE ON PRODUIT.COD_FAM = FAMILLE.COD_FAM
  INNER JOIN SEGMENT ON PRODUIT.COD_MAR = SEGMENT.COD_SEG
  INNER JOIN FOURN ON PRODUIT.COD_FRS = FOURN.COD_FRS
INNER JOIN POP_PDTPOP ON ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
  LEFT JOIN SAS_PDT ON POP_PDTPOP.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
    LEFT JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
      LEFT JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
  LEFT JOIN POP_PREVMAG ON POP_PDTPOP.NO_ACPUB = POP_PREVMAG.NO_ACPUB
    LEFT JOIN SAS_PREVAPPRO
      ON POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB 
    AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG
WHERE FMK_ETAT_HIST_PUB.ID_ETAT = 611
  AND ACCPUB.COD_DEST = 'MA'  ;

2-je fais la requete finale

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
select 
 NO_ACPUB,
 NO_PUB,
 EX_PUB,
 COD_PDT,  LIB_COUR,
 COD_FAM,  LIB_FAM,
 COD_SEG,  LIB_SEG,
 COD_FRS,  LIB_FRS,  REF_FOUR,
       VALIDE_POP,
 NIVEAU_PUB,
QTE_BLQ,
 BASE100,
 
       ETAT_SAS,
       EXPOSITION,
   SPECIFIQUE,
     ID_PARAM_TYPE_PLAN,
       PCB,
 ROLE_PDT ,    
 SUM(NVL( PREVPROP, 0)) AS PREVPROPOSEE,
SUM(NVL( PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR ,
SUM(NVL( PREVSAISIE, CASE WHEN  STATUTSTOCK=1 THEN NVL( PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR ,
 SUM(NVL( PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR ,
       SUM(NVL( PREVISIONAPPRO, NVL( PREVSAISIE,  CASE WHEN  STATUTSTOCK=1 THEN NVL( PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR  ,
 
 
 
-- ROUND(((SUM(NVL( PREVSAISIE, 0)))/  QTE_BLQ) * 100, 2)   AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
CASE WHEN  QTE_BLQ != 0 THEN ROUND((SUM(NVL( PREVSAISIE,0))/  QTE_BLQ) * 100, 2) ELSE NULL END 
    AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
 
CASE WHEN  QTE_BLQ != 0 THEN ROUND((SUM(NVL( PREVSAISIE, CASE WHEN  STATUTSTOCK=1 THEN NVL( PREVPROP, 0) ELSE 0 END))/  QTE_BLQ) * 100, 2) ELSE NULL END 
    AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR
 
 
 
 
 from v_pop_test_sas
 
where  ID_ETAT = 611
  AND  COD_DEST = 'MA'  
group by
 
 NO_ACPUB,
 NO_PUB,
 EX_PUB,
 COD_PDT,  LIB_COUR,
 COD_FAM,  LIB_FAM,
 COD_SEG,  LIB_SEG,
 COD_FRS,  LIB_FRS,  REF_FOUR,
       VALIDE_POP,
 NIVEAU_PUB,
 
 BASE100,
 
       ETAT_SAS,
       EXPOSITION,
   SPECIFIQUE,
     ID_PARAM_TYPE_PLAN,
       PCB,role_pdt ,QTE_BLQ

3-en faisant 5 jeu de test(juste pour savoir l'ordre de grandeur du temps d'execution), je compare le temps d'executions entre la nouvelle requete et la requette de depart
en moyenne le temps d'execution de l'ancienne requette est de 9.911 secondes tant dis que celui de la nouvelle requette est de 2.237 secondes

merci beacoup à vous tous pour avoir penché sur mon problème

**CinePhil** · 23/11/2009, 23h15

Envoyé par hamoud

salut CinePhil,
avec ta requete, j'applique un filtre typique comme

Code :

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

accpub.no_pub='0022' and accpub.ex_pub='2008' and famille.cod_fam

j'ai 113 lignes en 9.90 secondes
avec ma requete d'origines, j'obtiens les résultats en 15.20 secondes
donc un pas déjà vers l'avant ...

C'est intéressant ce que tu dis là !
Comme je n'ai fait que réécrire la requête, cela tendrait à prouver que les jointures normalisées sont plus rapides que les jointures à syntaxe obsolètes et que ça devient sensible sur des requêtes complexes avec beaucoup de jointures comme la tienne.

Moralité : Mettez vous à la page les accros du FROM WHERE !

Optimisation de requete

Langage SQL

Discussions similaires

Partager

Partager