Optimisation requête SQL avec WITH

Version imprimable

23/11/2016, 11h32
Nhaps

Optimisation requête SQL avec WITH
Bonjour à tous,

J'ai une requete sous la forme :
Code:

1 2 3 4 5 WITH SELECT FROM WHERE
La requête dans le WITH est rapide, mais j'ai de fort ralentissement quand je joint ma requête du select avec les résultat du WITH (3-4s) alors que si je commente la jointure la requete se fait quasiment instantanément.
J'aimerai avoir vos conseils pour optimiser les temps de traitement dans le cas de ces requetes, quid des index ?

Merci de vos conseils.
23/11/2016, 13h46
escartefigue

Bonjour,

Communiquez la requête complète, ainsi que la description des vos index et quelques éléments de volumétrie, sans quoi il sera difficile de vous aider

Citation:

Envoyé par escartefigue

Bonjour,

Communiquez la requête complète, ainsi que la description des vos index et quelques éléments de volumétrie, sans quoi il sera difficile de vous aider

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
 
WITH
PRIX_IPA(IDCONVIVE, IDIPA, DESIGNATION, PRIX)
AS
(
SELECT
PRIX_FR_CONVIVE.IDCONVIVE,
PRIX_FR_CONVIVE.ID_IPA,
PRIX_FR_CONVIVE.DESIGNATION_IPA,
AVG(PRIX_FR_CONVIVE.PRIX_CALCUL) AS PRIX_IPA
FROM
(
SELECT
IC_PRIXFR.ID_IPA,
IC_PRIXFR.DESIGNATION_IPA,
InfoDiet.ID_Convives AS IDCONVIVE,
IC_PRIXFR.PRIX_UNITE * InfoDiet.Grammages AS PRIX_CALCUL
FROM
IC_PRIXFR 
INNER JOIN InfoDiet
ON IC_PRIXFR.IDFR = InfoDiet.ID_FR
INNER JOIN AS_Region
ON IC_PRIXFR.IDREGION = AS_Region.idRegion
WHERE
AS_Region.CodeRegion = 'TESTTEST'
)  AS PRIX_FR_CONVIVE	
GROUP BY
PRIX_FR_CONVIVE.IDCONVIVE,
PRIX_FR_CONVIVE.ID_IPA,
PRIX_FR_CONVIVE.DESIGNATION_IPA
)
SELECT
PA_DETAILS.IDPA_DETAILS,
PA_DETAILS.NumSemaine,
PA_DETAILS.IDConvive,
PA_DETAILS.IDJour,
PA_DETAILS.IDElement,
PA_DETAILS.IDIPA,
PA_DETAILS.IDService,
PA_DETAILS.NumChoix,
PA_DETAILS.ESTACTIF,
PA_DETAILS.GEMRCN_1,
PA_DETAILS.GEMRCN_2,
PA_DETAILS.GEMRCN_3,
LETTRE_REPERE_1.LettreRepere LETTRE_1,
LETTRE_REPERE_2.LettreRepere LETTRE_2,
LETTRE_REPERE_3.LettreRepere LETTRE_3,
ELEMENT.Element AS DESIGNATION_ELEMENT,
SERVICE.LibService AS DESIGNATION_SERVICE,
CONVIVE.Designation AS DESIGNATION_CONVIVE,
PRIX_IPA.PRIX,
IPA_INTITULE.Intitule AS DESIGNATION_IPA
 
FROM
MAp_PlanAlim PA
INNER JOIN MAp_PlanAlimDetails PA_DETAILS
ON PA.IDPA = PA_DETAILS.IDPA
INNER JOIN IPA_Element ELEMENT
ON PA_DETAILS.IDElement = ELEMENT.ID_IPA_Element
INNER JOIN Convives CONVIVE
ON PA_DETAILS.IDConvive = CONVIVE.ID_Convives
INNER JOIN MAp_Services SERVICE
ON PA_DETAILS.IDService = SERVICE.IDService
LEFT OUTER JOIN IPA_Intitule IPA_INTITULE
ON PA_DETAILS.IDIPA = IPA_INTITULE.ID_IPA_Intitule
LEFT OUTER JOIN PRIX_IPA
ON PA_DETAILS.IDConvive = PRIX_IPA.IDCONVIVE AND PA_DETAILS.IDIPA = PRIX_IPA.IDIPA
LEFT OUTER JOIN LettresReperes LETTRE_REPERE_1
ON PA_DETAILS.GEMRCN_1 = LETTRE_REPERE_1.ID_LettresReperes
LEFT OUTER JOIN LettresReperes LETTRE_REPERE_2
ON PA_DETAILS.GEMRCN_2 = LETTRE_REPERE_2.ID_LettresReperes
LEFT OUTER JOIN LettresReperes LETTRE_REPERE_3
ON PA_DETAILS.GEMRCN_3 = LETTRE_REPERE_3.ID_LettresReperes
WHERE
PA_DETAILS.IDPA = 111

Je n'ai pas d'index, simplement ma requete dans le WITH a part se fait en 0s. Si la jointure entre la requete du WITH et ma requete du SELECT ralenti la requete à 3-4s... Mais je ne saisi pas quels index mettre car au final la requete du WITH est une sorte de table temporaire non ?

23/11/2016, 14h30
escartefigue

Si vous n'avez aucun index, vu le nombre de jointures, même si les volumes sont faibles (qu'en est il), il est normal que le temps d'exécution soit long !

En même temps une table sans index, c'est anormal. A minima, vous devriez avoir des index sur les attributs identifiants primaires, et ce sont justement eux que vous utilisez dans les jointures, comme c'est d'ailleurs le plus souvent le cas.

EDIT : j'imagine que vos tables ne contiennent que quelques centaines ou milliers de lignes, car sans index, si vous aviez des centaines de millions de lignes ce ne serait pas 3 à 4 secondes de temps de réponse, mais plusieurs heures !
23/11/2016, 14h31
Nhaps
Citation:

Envoyé par escartefigue

Si vous n'avez aucun index, vu le nombre de jointures, même si les volumes sont faibles (qu'en est il), il est normal que le temps d'exécution soit long !

En même temps une table sans index, c'est anormal. A minima, vous devriez avoir des index sur les attributs identifiants primaires, et ce sont justement eux que vous utilisez dans les jointures, comme c'est d'ailleurs le plus souvent le cas.

Oui oui il y a les index par defaut.
La requete est instantané sauf avec la jointure
Code:

1 2 3 LEFT OUTER JOIN PRIX_IPA ON PA_DETAILS.IDConvive = PRIX_IPA.IDCONVIVE AND PA_DETAILS.IDIPA = PRIX_IPA.IDIPA
mais quoi mettre en index quand on utilise WITH ?
23/11/2016, 14h38
escartefigue
Citation:
Envoyé par Nhaps

Oui oui il y a les index par defaut.
La requete est instantané sauf avec la jointure

Code:

1 2 3 LEFT OUTER JOIN PRIX_IPA ON PA_DETAILS.IDConvive = PRIX_IPA.IDCONVIVE AND PA_DETAILS.IDIPA = PRIX_IPA.IDIPA
Alors vérifiez que les colonnes concernées ont des index et qu'elles sont bien de même type et même longueur
Vérifiez aussi si les stats sont à jour
Combien de lignes y a -t- il dans PRIX_IPA et dans PA_DETAILS ?

Citation:

Envoyé par Nhaps

mais quoi mettre en index quand on utilise WITH ?

Ce n'est pas la CTE qui pose souci puisqu'elle s'exécute immédiatement, de plus qu'une requête soit exécutée au travers d'une CTE ou non, ça ne change rien par rapport aux besoins éventuels d'index
EDIT : oups pardon, pas vu que PRIX_IPA est justement le nom de la CTE :mrgreen:
23/11/2016, 14h49
Nhaps

Citation:

Envoyé par escartefigue

Alors vérifiez que les colonnes concernées ont des index et qu'elles sont bien de même type et même longueur
Vérifiez aussi si les stats sont à jour
Combien de lignes y a -t- il dans PRIX_IPA et dans PA_DETAILS ?

PA_DETAILS = 50000+ enregistrement
PRIX_IPA = une centaine de ligne environ

Pour le reste oui tout est bon.
23/11/2016, 15h00
escartefigue

hum, du coup c'est surprenant, avec si peu de lignes dans la CTE
Mais essayez tout de même en supprimant la CTE que vous remplacez par une requête imbriquée (à l'ancienne quoi), sait on jamais
23/11/2016, 15h05
Nhaps

Citation:

Envoyé par escartefigue

hum, du coup c'est surprenant, avec si peu de lignes dans la CTE
Mais essayez tout de même en supprimant la CTE que vous remplacez par une requête imbriquée (à l'ancienne quoi), sait on jamais

Comment adapterez vous ma requête sachant que j'ai une double jointure ?

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
LEFT OUTER JOIN
(SELECT PRIX_FR_CONVIVE.IDCONVIVE,
            PRIX_FR_CONVIVE.ID_IPA,
            PRIX_FR_CONVIVE.DESIGNATION_IPA,
            AVG(PRIX_FR_CONVIVE.PRIX_CALCUL) AS PRIX_IPA
            FROM
                  (SELECT IC_PRIXFR.ID_IPA,
                          IC_PRIXFR.DESIGNATION_IPA,
                          InfoDiet.ID_Convives AS IDCONVIVE,
                          IC_PRIXFR.PRIX_UNITE * InfoDiet.Grammages AS PRIX_CALCUL
                   FROM IC_PRIXFR 
                   INNER JOIN InfoDiet
                      ON IC_PRIXFR.IDFR = InfoDiet.ID_FR
                   INNER JOIN AS_Region
                      ON IC_PRIXFR.IDREGION = AS_Region.idRegion
                   WHERE AS_Region.CodeRegion = 'TESTTEST'
                   )  AS PRIX_FR_CONVIVE	
            GROUP BY
            PRIX_FR_CONVIVE.IDCONVIVE,
            PRIX_FR_CONVIVE.ID_IPA,
            PRIX_FR_CONVIVE.DESIGNATION_IPA
    ) as PRIX_IPA
  ON PA_DETAILS.IDConvive = PRIX_IPA.IDCONVIVE 
 AND PA_DETAILS.IDIPA     = PRIX_IPA.IDIPA

23/11/2016, 15h56
Nhaps

Je viens de faire l'essai, ca prend exactement le même temps qu'en passant avec la CTE
23/11/2016, 16h06
escartefigue

Je vois que la table PA_DETAILS contient beaucoup d'identifiants, peut être que les 2 ID qui correspondent au critère de jointure avec la CTE sont très dispersés et requièrent des tris de RID voire un tablescan
Sinon essayez en créant un index sur la table PA_DETAILS comprenant IDConvive+IDIPA
23/11/2016, 16h37
Nhaps

Tu es mon roi de la journée :)

Ça fonctionne merci beaucoup

Récrivez votre requête comme ceci. Ce sera beaucoup plus lisible :

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
WITH prix_fr_convive
     AS (SELECT IC_PRIXFR.ID_IPA,
                IC_PRIXFR.DESIGNATION_IPA,
                InfoDiet.ID_Convives                      AS IDCONVIVE,
                IC_PRIXFR.PRIX_UNITE * InfoDiet.Grammages AS PRIX_CALCUL
         FROM   IC_PRIXFR
                INNER JOIN InfoDiet
                        ON IC_PRIXFR.IDFR = InfoDiet.ID_FR
                INNER JOIN AS_Region
                        ON IC_PRIXFR.IDREGION = AS_Region.idRegion
         WHERE  AS_Region.CodeRegion = 'TESTTEST'),
     prix_ipa (IDCONVIVE, IDIPA, DESIGNATION, PRIX)
     AS (SELECT PRIX_FR_CONVIVE.IDCONVIVE,
                PRIX_FR_CONVIVE.ID_IPA,
                PRIX_FR_CONVIVE.DESIGNATION_IPA,
                Avg(PRIX_FR_CONVIVE.PRIX_CALCUL) AS PRIX_IPA
         FROM   prix_fr_convive
         GROUP  BY PRIX_FR_CONVIVE.IDCONVIVE,
                   PRIX_FR_CONVIVE.ID_IPA,
                   PRIX_FR_CONVIVE.DESIGNATION_IPA)
SELECT PA_DETAILS.IDPA_DETAILS,
       PA_DETAILS.NumSemaine,
       PA_DETAILS.IDConvive,
       PA_DETAILS.IDJour,
       PA_DETAILS.IDElement,
       PA_DETAILS.IDIPA,
       PA_DETAILS.IDService,
       PA_DETAILS.NumChoix,
       PA_DETAILS.ESTACTIF,
       PA_DETAILS.GEMRCN_1,
       PA_DETAILS.GEMRCN_2,
       PA_DETAILS.GEMRCN_3,
       LETTRE_REPERE_1.LettreRepere LETTRE_1,
       LETTRE_REPERE_2.LettreRepere LETTRE_2,
       LETTRE_REPERE_3.LettreRepere LETTRE_3,
       ELEMENT.Element              AS DESIGNATION_ELEMENT,
       SERVICE.LibService           AS DESIGNATION_SERVICE,
       CONVIVE.Designation          AS DESIGNATION_CONVIVE,
       PRIX_IPA.PRIX,
       IPA_INTITULE.Intitule        AS DESIGNATION_IPA
FROM   MAp_PlanAlim PA
       INNER JOIN MAp_PlanAlimDetails PA_DETAILS
               ON PA.IDPA = PA_DETAILS.IDPA
       INNER JOIN IPA_Element ELEMENT
               ON PA_DETAILS.IDElement = ELEMENT.ID_IPA_Element
       INNER JOIN Convives CONVIVE
               ON PA_DETAILS.IDConvive = CONVIVE.ID_Convives
       INNER JOIN MAp_Services SERVICE
               ON PA_DETAILS.IDService = SERVICE.IDService
       LEFT OUTER JOIN IPA_Intitule IPA_INTITULE
                    ON PA_DETAILS.IDIPA = IPA_INTITULE.ID_IPA_Intitule
       LEFT OUTER JOIN prix_ipa
                    ON PA_DETAILS.IDConvive = PRIX_IPA.IDCONVIVE
                       AND PA_DETAILS.IDIPA = PRIX_IPA.IDIPA
       LEFT OUTER JOIN LettresReperes LETTRE_REPERE_1
                    ON PA_DETAILS.GEMRCN_1 = LETTRE_REPERE_1.ID_LettresReperes
       LEFT OUTER JOIN LettresReperes LETTRE_REPERE_2
                    ON PA_DETAILS.GEMRCN_2 = LETTRE_REPERE_2.ID_LettresReperes
       LEFT OUTER JOIN LettresReperes LETTRE_REPERE_3
                    ON PA_DETAILS.GEMRCN_3 = LETTRE_REPERE_3.ID_LettresReperes
WHERE  PA_DETAILS.IDPA = 111

a +