Problème de lenteur lors de l'exécution d'une requête en pl/sql [Résolu]

yoni75 · 28/09/2011, 13h07

Bonjour,

J'ai un problème pour la création d'une requête en PL/SQL (Oracle).

Voici le problème,
Je dois afficher des données mensuelles pour les diffèrents produits que nous avons (nombre totals de produit par mois, la moyenne du nombre de produit journalier, le pic du nombre de produit dans le mois et la date de ce pic) comme suit :

Dans un mois nous pouvons avoir plusieurs date pour un même pic.
[IMG]C:\tmp\tabDonnesPrdt.bmp[/IMG]

Pour cela je voudrai faire la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT	to_char(eventdate, 'yyyy/mm') AS MOIS,
	Nom_Produit AS Nom,
	SUM(Nb_Produits) AS Nb_de_produits_Total, 
	AVG(Nb_Produits) AS Moyenne_nb_de_produits_journalier, 
	MAX(Nb_Produits)  AS Pic_Nb_Produit,
	date AS Date_Du_Pic
      FROM historic_1656379283 
      GROUP BY to_char(eventdate, 'yyyy/mm'),Nom_Produit
      ORDER BY  to_char(eventdate, 'yyyy/mm') DESC;

Le problème est au niveau de l'affichage de la Date du pic, je ne peux pas le mettre dans le regroupement(group by) car sinon je n'ai plus les moyennes et les sommes par mois mais par jour.
Cette requête envoie donc une erreur si je ne mets pas date dans le group by.

Pour contourner le problème j'ai créé deux curseurs une qui me donne tout les résultats sauf la date du pic et l'autre qui récupère la date du pic en fonction du mois, de nom du produit et du pic récupérés par les résultats du premier curseur.

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
Curseur statProduit IS
SELECT	to_char(eventdate, 'yyyy/mm') AS MOIS,
	Nom_Produit AS Nom,
	SUM(Nb_Produits) AS Nb_de_produits_Total, 
	AVG(Nb_Produits) AS Moyenne_nb_de_produits_journalier, 
	MAX(Nb_Produits)  AS Pic_Nb_Produit
      FROM historic_1656379283 
      GROUP BY to_char(eventdate, 'yyyy/mm'),Nom_Produit
      ORDER BY  to_char(eventdate, 'yyyy/mm') DESC;
 
Curseur DatePic(mois varchar, nomPdt varchar, picduMois int) IS
SELECT	date AS Date_Du_Pic
      FROM historic_1656379283 
      WHERE Nom_Produit = nomPdt 
      	   AND to_char(eventdate, 'yyyy/mm')=mois
      	   AND Nb_Produits=picduMois
      GROUP BY to_char(eventdate, 'yyyy/mm'),Nom_Produit
      ORDER BY  to_char(eventdate, 'yyyy/mm') DESC;

Ce qui me donne le code suivant dans le begin :

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
begin
 
  OPEN statProduit;
       LOOP
       FETCH statProduit INTO ChaquestatProduit;
       Exit when statProduit%NOTFOUND ;
 
             OPEN DatePic(ChaquestatProduit.MOIS,ChaquestatProduit.Nom,ChaquestatProduit.Pic_Nb_Produit);
             LOOP
             FETCH DatePic INTO ChaqueDatePic;
                   Exit when DatePic%NOTFOUND ;
 
                    REQ:='insert into TAB_STATSCRE_MEMensuel(MOIS,NOMPDT,MOYENNEPDT,PICPDT,DATE_DU_PIC) values (' || '''' || ChaquestatProduit.MOIS   || '''' ||  ',' || '''' || ChaquestatProduit.Nom || '''' || ',' || '''' || ChaquestatProduit.Nb_de_produits_Total || '''' || ',' || '''' || ChaquestatProduit.Pic_Nb_Produit || '''' || ',' ||  '''' || ChaqueDatePic.Date_Du_Pic || '''' || ')';
                    dbms_output.put_line('insert into TAB_STATSCRE_MEMensuel(MOIS,NOMPDT,MOYENNEPDT,PICPDT,DATE_DU_PIC) values (' || '''' || ChaquestatProduit.MOIS   || '''' ||  ',' || '''' || ChaquestatProduit.Nom || '''' || ',' || '''' || ChaquestatProduit.Nb_de_produits_Total || '''' || ',' || '''' || ChaquestatProduit.Pic_Nb_Produit || '''' || ',' ||  '''' || ChaqueDatePic.Date_Du_Pic ||  '''' || ')');
                    EXECUTE IMMEDIATE REQ;
             END LOOP;
             CLOSE DatePic;
       END LOOP;
 CLOSE statProduit;

Le problème de cette méthode et qu'elle prend enormément de temps à s'exécuter lors de la recherche de la date pour chaque ligne (pour 400 lignes de résultat = env 1h)

Y'a t'il un moyen de récupérer la date avec la première requête sans mettre la date dans le group by?
Ou bien avez vous une idée pour réduire le temps de recherche de la date?

Merci d'avance.

ojo77 · 28/09/2011, 13h15

=> http://lalystar.developpez.com/fonctionsAnalytiques/

Waldar · 28/09/2011, 13h32

Que voulez-vous obtenir quand le pic se produit à plusieurs dates distinctes ?

ORA-007 · 28/09/2011, 13h50

Bonjour ,

en essayant de reprendre le besoin et la requette initiale :

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 historic_1656379283 AS
(
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9  AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0  AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,11 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,12 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,13 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,14 AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,15 AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,16 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,17 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,18 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9  AS Nb_Produits,to_date('04/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0  AS Nb_Produits,to_date('05/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL 
)
SELECT DISTINCT
to_char(eventdate, 'yyyy/mm') AS MOIS,
Nom_Produit AS Nom,
SUM(Nb_Produits) OVER (partition BY to_char(eventdate, 'yyyy/mm'),Nom_Produit) AS Nb_de_produits_Total,
AVG(Nb_Produits) OVER (partition BY to_char(eventdate, 'yyyy/mm'),Nom_Produit) AS Moyenne_nb,
MAX(Nb_Produits) OVER (partition BY to_char(eventdate, 'yyyy/mm'),Nom_Produit) AS Pic_Nb_Produit,
MAX(date_vente)  KEEP (DENSE_RANK FIRST ORDER BY Nom_Produit,Nb_Produits DESC) OVER (partition BY to_char(eventdate, 'yyyy/mm'),Nom_Produit) AS Date_Du_Pic
FROM historic_1656379283 t1
ORDER BY to_char(eventdate, 'yyyy/mm') DESC;

yoni75 · 28/09/2011, 14h05

Citation:

Envoyé par Waldar

Que voulez-vous obtenir quand le pic se produit à plusieurs dates distinctes ?

Bonjour,

Quand le pic se produit à plusieurs dates distinctes je veux les afficher aussi ex :
Mois;Nom Nb_de_prouits_Total;Moyenne_nb_de_produits_journalier;Pic_Nb_Produit;Date_Du_Picnalier
2011/08 tata 3600 196.54 354 12/08/2011
2011/08 tata 3600 196.54 354 18/08/2011

yoni75 · 28/09/2011, 14h16

Citation:

Envoyé par ORA-007

en essayant de reprendre le besoin et la requête initiale :

Bonjour,

Merci je vais tester.

Cordialement.

Waldar · 28/09/2011, 14h24

Dans ce cas-là, on peut résoudre le problème avec une jointure.
Je reprends les données d'ORA-007 que je modifie pour introduire plusieurs dates avec le même pic :

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
WITH historic_1656379283 AS
(
SELECT date '2011-01-01' AS eventdate, 'jouet' AS nom_produit, 10 AS nb_produits FROM dual union ALL
SELECT date '2011-01-02'             , 'jouet'               , 11                FROM dual union ALL
SELECT date '2011-01-03'             , 'jouet'               , 35                FROM dual union ALL
SELECT date '2011-01-04'             , 'jouet'               ,  9                FROM dual union ALL
SELECT date '2011-01-05'             , 'jouet'               ,  0                FROM dual union ALL
SELECT date '2011-01-06'             , 'jouet'               , 58                FROM dual union ALL
SELECT date '2011-01-07'             , 'jouet'               , 58                FROM dual union ALL
SELECT date '2011-01-08'             , 'jouet'               , 10                FROM dual union ALL
SELECT date '2011-01-01'             , 'yoyo'                , 11                FROM dual union ALL
SELECT date '2011-01-02'             , 'yoyo'                , 12                FROM dual union ALL
SELECT date '2011-01-03'             , 'yoyo'                , 13                FROM dual union ALL
SELECT date '2011-01-04'             , 'yoyo'                , 14                FROM dual union ALL
SELECT date '2011-01-05'             , 'yoyo'                , 15                FROM dual union ALL
SELECT date '2011-01-06'             , 'yoyo'                , 16                FROM dual union ALL
SELECT date '2011-01-07'             , 'yoyo'                , 17                FROM dual union ALL
SELECT date '2011-01-08'             , 'yoyo'                , 18                FROM dual union ALL
SELECT date '2011-02-01'             , 'jouet'               , 10                FROM dual union ALL
SELECT date '2011-02-02'             , 'jouet'               , 11                FROM dual union ALL
SELECT date '2011-02-03'             , 'jouet'               , 35                FROM dual union ALL
SELECT date '2011-02-04'             , 'jouet'               ,  9                FROM dual union ALL
SELECT date '2011-02-05'             , 'jouet'               ,  0                FROM dual union ALL
SELECT date '2011-02-06'             , 'jouet'               , 58                FROM dual union ALL
SELECT date '2011-02-07'             , 'jouet'               , 16                FROM dual union ALL
SELECT date '2011-02-08'             , 'jouet'               , 10                FROM dual 
)
  ,  sr AS
(
  SELECT to_char(eventdate, 'yyyy/mm') AS mois
       , nom_produit
       , sum(nb_produits) AS nb_de_produits_total 
       , avg(nb_produits) AS moyenne_nb_produits 
       , max(nb_produits) AS pic_nb_produit
    FROM historic_1656379283
GROUP BY to_char(eventdate, 'yyyy/mm')
       , nom_produit
)
  SELECT sr.mois
       , sr.nom_produit
       , sr.nb_de_produits_total
       , sr.moyenne_nb_produits
       , sr.pic_nb_produit
       , tb.eventdate
    FROM SR
         INNER JOIN historic_1656379283 tb
           ON tb.nom_produit                   = sr.nom_produit
          AND to_char(tb.eventdate, 'yyyy/mm') = sr.mois
   WHERE tb.nb_produits = sr.pic_nb_produit
ORDER BY sr.mois DESC
       , sr.nom_produit ASC;
 
 
MOIS    NOM_P NB_DE_PRODUITS_TOTAL MOYENNE_NB_PRODUITS PIC_NB_PRODUIT EVENTDATE 
------- ----- -------------------- ------------------- -------------- ----------
2011/02 jouet                  149              18.625             58 06/02/2011
2011/01 jouet                  191              23.875             58 06/01/2011
2011/01 jouet                  191              23.875             58 07/01/2011
2011/01 yoyo                   116                14.5             18 08/01/2011

C'est d'ailleurs ce que vous faites avec vos curseurs !

mnitu · 28/09/2011, 14h56

Alors plutôt requête analytique de type TopN

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> WITH historic_1656379283 AS
  2  (
  3    SELECT date '2011-01-01' AS eventdate, 'jouet' AS nom_produit, 10 AS nb_produits FROM dual union ALL
  4    SELECT date '2011-01-02'             , 'jouet'               , 11                FROM dual union ALL
  5    SELECT date '2011-01-03'             , 'jouet'               , 35                FROM dual union ALL
  6    SELECT date '2011-01-04'             , 'jouet'               ,  9                FROM dual union ALL
  7    SELECT date '2011-01-05'             , 'jouet'               ,  0                FROM dual union ALL
  8    SELECT date '2011-01-06'             , 'jouet'               , 58                FROM dual union ALL
  9    SELECT date '2011-01-07'             , 'jouet'               , 58                FROM dual union ALL
 10    SELECT date '2011-01-08'             , 'jouet'               , 10                FROM dual union ALL
 11    SELECT date '2011-01-01'             , 'yoyo'                , 11                FROM dual union ALL
 12    SELECT date '2011-01-02'             , 'yoyo'                , 12                FROM dual union ALL
 13    SELECT date '2011-01-03'             , 'yoyo'                , 13                FROM dual union ALL
 14    SELECT date '2011-01-04'             , 'yoyo'                , 14                FROM dual union ALL
 15    SELECT date '2011-01-05'             , 'yoyo'                , 15                FROM dual union ALL
 16    SELECT date '2011-01-06'             , 'yoyo'                , 16                FROM dual union ALL
 17    SELECT date '2011-01-07'             , 'yoyo'                , 17                FROM dual union ALL
 18    SELECT date '2011-01-08'             , 'yoyo'                , 18                FROM dual union ALL
 19    SELECT date '2011-02-01'             , 'jouet'               , 10                FROM dual union ALL
 20    SELECT date '2011-02-02'             , 'jouet'               , 11                FROM dual union ALL
 21    SELECT date '2011-02-03'             , 'jouet'               , 35                FROM dual union ALL
 22    SELECT date '2011-02-04'             , 'jouet'               ,  9                FROM dual union ALL
 23    SELECT date '2011-02-05'             , 'jouet'               ,  0                FROM dual union ALL
 24    SELECT date '2011-02-06'             , 'jouet'               , 58                FROM dual union ALL
 25    SELECT date '2011-02-07'             , 'jouet'               , 16                FROM dual union ALL
 26    SELECT date '2011-02-08'             , 'jouet'               , 10                FROM dual
 27  ), topN AS (
 28    SELECT to_char(eventdate, 'yyyy/mm') AS Mois,
 29           eventdate, nom_produit, nb_produits,
 30           sum(nb_produits) over (partition BY to_char(eventdate, 'yyyy/mm') , nom_produit) AS nb_de_produits_total,
 31           avg(nb_produits) over (partition BY to_char(eventdate, 'yyyy/mm') , nom_produit) AS moyenne_nb_produits,
 32           max(nb_produits) over (partition BY to_char(eventdate, 'yyyy/mm') , nom_produit) AS pic_nb_produit,
 33           dense_rank()     over (partition BY to_char(eventdate, 'yyyy/mm') , nom_produit ORDER BY nb_produits DESC) rn
 34      FROM historic_1656379283
 35  )
 36  SELECT Mois, Nom_Produit, nb_de_produits_total, moyenne_nb_produits,  pic_nb_produit, eventdate
 37    FROM TopN
 38   WHERE rn = 1
 39  ORDER BY Mois DESC, nom_produit  ASC
 40  /
 
MOIS    NOM_PRODUIT NB_DE_PRODUITS_TOTAL MOYENNE_NB_PRODUITS PIC_NB_PRODUIT EVENTDATE
------- ----------- -------------------- ------------------- -------------- -----------
2011/02 jouet                        149              18,625             58 06/02/2011
2011/01 jouet                        191              23,875             58 06/01/2011
2011/01 jouet                        191              23,875             58 07/01/2011
2011/01 yoyo                         116                14,5             18 08/01/2011
 
SQL>

yoni75 · 28/09/2011, 16h18

Bonjour,

Je vous remercie beaucoup pour votre aide je suis passé de plus d'une heure à moins de 15 secondes!!!!

Encore Merci

Cordialement.

28/09/2011, 13h32	#3
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 686 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 686 Points : 10 431 Points : 10 431	Que voulez-vous obtenir quand le pic se produit à plusieurs dates distinctes ? __________________ Email : http://scr.im/waldar
	00

28/09/2011, 13h15	#2
ojo77 Membre chevronné O. Joly Support Inscription : décembre 2010 Messages : 287 Détails du profil Informations personnelles : Nom : O. Joly Âge : 38 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Support Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : décembre 2010 Messages : 287 Points : 617 Points : 617	=> http://lalystar.developpez.com/fonctionsAnalytiques/
	00

28/09/2011, 16h18	#9
yoni75 Invité de passage Inscription : août 2007 Messages : 4 Détails du profil Informations forums : Inscription : août 2007 Messages : 4 Points : 1 Points : 1	Bonjour, Je vous remercie beaucoup pour votre aide je suis passé de plus d'une heure à moins de 15 secondes!!!! Encore Merci Cordialement.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email