Problème de performance sur un calcul en cascade

**mars13008** · 15/04/2011, 14h28

Bonjour,

Je souhaite calculer la quantité des composants achetés pour la production de mon produit finale.
On a trouvé une solution qui ne fonctionne que sur des petits volumes de données, donc dans l'exemple ci-dessous pas de soucis.
Mais sur des volumes plus importants, nous avons des problèmes de performances qui proviennent de l'utilisation de la fonction.
Sur notre jeu de données de test, avec fonction 15 min, sans fonction quasi-immédiat.
Avez-vous des pistes pour réaliser ce calcul de manière plus performante?

Merci pour votre aide.

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
 
CREATE OR REPLACE FUNCTION 
F_CALCULATION(CALCULATION IN VARCHAR2) RETURN NUMBER
IS
  REQUETE          VARCHAR2(512) ;
  TOTAL            NUMBER ;
  L_TXT           VARCHAR2 (4000) := CALCULATION;
BEGIN
  REQUETE := 'SELECT ' || L_TXT || ' FROM DUAL'  ;
  EXECUTE IMMEDIATE REQUETE INTO TOTAL ;
  RETURN TOTAL ;
EXCEPTION
   WHEN OTHERS THEN
   RETURN 0  ;
END ;
/
 
 
with TMP_PROD_FG as
(
select to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' from DUAL 
)
SELECT   T.W_DATE
        ,T.W_ORIG_RM
        , (SUM (T.W_UNIT_QTY_USED_FINAL) * T.W_PARENT_QTY_UOM2)  "W_QTY_USED"
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2
        ,T.W_LVL
        ,T.W_LEAF
    FROM (SELECT     CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
                    ,T2.W_DATE
                    ,T2.W_PARENT_MFG
                    ,T2.W_PARENT_QTY_UOM2
                    ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2))
                                                                            "W_UNIT_QTY_USED_FINAL"
                        ,LEVEL "W_LVL"
                        ,CONNECT_BY_ISLEAF "W_LEAF"
                FROM TMP_PROD_FG T2
          START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
          CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
                 AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
                 AND T2.W_SOURCING_TYPE <> 'PURCHASE'
                 ) T
GROUP BY T.W_DATE
        ,T.W_ORIG_RM
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2              
        ,T.W_LVL
        ,T.W_LEAF
order by w_leaf desc , w_parent_mfg

**ojo77** · 15/04/2011, 14h48

Il pourrait être intéressant de créer ta propre fonction d'aggregation pour le calcul d'un produit :

http://download.oracle.com/docs/cd/B...5/dci11agg.htm

**xdescamp** · 15/04/2011, 15h41

La fonction d'aggrégat est effectivement une piste intéressante.

Tu peux aussi essayer la solution suivante qui évite d'avoir une requête différente à chaque appel, ce qui provoque un PARSE à chaque fois :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION 
F_CALCULATION(CALCULATION IN VARCHAR2) RETURN NUMBER
IS
  TOTAL            NUMBER ;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :b1 := ' || CALCULATION || ' ; END;'
  USING OUT TOTAL ;
 
  RETURN TOTAL ;
EXCEPTION
   WHEN OTHERS THEN
   RETURN 0  ;
END ;
/

Ici on ne passe plu par une requête, mais on fait le calcul directement en PL/SQL.
A voir quel gain en temps de traitement ça donne, mais ça peut être intéressant.

**mars13008** · 15/04/2011, 15h49

Merci pour la piste.

Mais après lecture du site, j'ai l'impression que créée ma propre fonction d’agrégat ne répond pas à mon problème. L'idée serait plutôt de faire le calcul au fil des itération mais je pense pas que ce soit possible. Sinon il faudrait ne pas à avoir à "parser" ma chaîne de caractères dans la fonction.

Des nouvelles idées?

**xdescamp** · 15/04/2011, 16h16

La modification de fonction que j'ai proposée ne donne aucun gain en temps d'exécution ?

**ojo77** · 15/04/2011, 16h23

Je pense que la durée dépends plus de la hyérarchisation que de l'aggrégation. Sans la fonction, combien de temps dure le code suivant par rapport à la globalité de la requête ? :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT     CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
                    ,T2.W_DATE
                    ,T2.W_PARENT_MFG
                    ,T2.W_PARENT_QTY_UOM2
                    ,W_UNIT_QTY_USED
                    ,LEVEL "W_LVL"
                    ,CONNECT_BY_ISLEAF "W_LEAF"
                FROM TMP_PROD_FG T2
          START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
          CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
                 AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
                 AND T2.W_SOURCING_TYPE <> 'PURCHASE'

**mars13008** · 18/04/2011, 17h00

Envoyé par xdescamp

La modification de fonction que j'ai proposée ne donne aucun gain en temps d'exécution ?

Cette solution a été testé sans gain significatif de performance.
Ca a été notre premier essai d'optimisation.

**mars13008** · 19/04/2011, 11h38

Désolé la communauté...
Mais je reviens à la charge avec mon problème.
En fait j'ai l'impression que même avec la fonction analytique prodcumule, je ne pourrai pas arrivé au résultat voulu avec un jeu de donnés plus complexes.
En fait, mon premier exemple était un peu trop simplifié. Ci-dessous vous en trouverez un autre avec un niveau supplémentaire de complexité.

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
 
with TMP_PROD_FG as
(
select to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,11,1,100,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,11,0.5,100,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,11,1.5,100,'PURCHASE' from DUAL 
)
SELECT   T.W_DATE
        ,T.W_ORIG_RM
        , (SUM (T.W_UNIT_QTY_USED_FINAL) * T.W_PARENT_QTY_UOM2)  "W_QTY_USED"
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2
        ,T.W_LVL
        ,T.W_LEAF
        ,T.W_UNIT_QTY_USED
        ,T.EXPR
        ,T.CHAINE
        ,SUM (T.W_UNIT_QTY_USED_FINAL) W_UNIT_QTY_USED_FINAL
    FROM (SELECT     CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
                    ,T2.W_DATE
                    ,T2.W_PARENT_MFG
                    ,T2.W_PARENT_QTY_UOM2
                    ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2))
                                                                            "W_UNIT_QTY_USED_FINAL"
                        ,LEVEL "W_LVL"
                        ,CONNECT_BY_ISLEAF "W_LEAF"
                        ,SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2) EXPR
                        ,SUBSTR (SYS_CONNECT_BY_PATH (T2.W_ITEM, ' / '), 2) || ' / ' || T2.W_PARENT_MFG CHAINE
                    ,T2.W_UNIT_QTY_USED
                FROM TMP_PROD_FG T2
          START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
          CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
                 AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
                 AND T2.W_SOURCING_TYPE <> 'PURCHASE'
                 ) T
GROUP BY T.W_DATE
        ,T.W_ORIG_RM
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2              
        ,T.W_LVL
        ,T.W_LEAF
        ,t.EXPR
        ,T.CHAINE
        ,T.W_UNIT_QTY_USED
order by w_leaf desc , w_parent_mfg, chaine

J'ai un truc qui semble bien fonctionné sans l'utilisation de la fonction de calcul d'expression de calcul mais il me reste à la tester sur un jeu de données conséquent.

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
 
with TMP_PROD_FG as
(
select to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,11,1,100,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,11,0.5,100,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,11,1.5,100,'PURCHASE' from DUAL 
),
table_test as
(
SELECT   T.W_DATE
        ,T.W_ORIG_RM
        , (SUM (T.W_UNIT_QTY_USED_FINAL) * T.W_PARENT_QTY_UOM2)  "W_QTY_USED"
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2
        ,T.W_LVL
        ,T.W_LEAF
        ,T.W_UNIT_QTY_USED
        ,T.EXPR
        ,T.CHAINE
    FROM (SELECT     CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
                    ,T2.W_DATE
                    ,T2.W_PARENT_MFG
                    ,T2.W_PARENT_QTY_UOM2
                    ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2))
                                                                            "W_UNIT_QTY_USED_FINAL"
                        ,LEVEL "W_LVL"
                        ,CONNECT_BY_ISLEAF "W_LEAF"
                        ,SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2) EXPR
                        ,SUBSTR (SYS_CONNECT_BY_PATH (T2.W_ITEM, ' / '), 2) || ' / ' || T2.W_PARENT_MFG CHAINE
                    ,T2.W_UNIT_QTY_USED
                FROM TMP_PROD_FG T2
          START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
          CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
                 AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
                 AND T2.W_SOURCING_TYPE <> 'PURCHASE'
                 ) T
GROUP BY T.W_DATE
        ,T.W_ORIG_RM
        ,T.W_PARENT_MFG
        ,T.W_PARENT_QTY_UOM2              
        ,T.W_LVL
        ,T.W_LEAF
        ,t.EXPR
        ,T.CHAINE
        ,T.W_UNIT_QTY_USED
order by w_leaf desc , w_parent_mfg, chaine
)
select p.*
, EXP (SUM (LN (c.w_unit_qty_used )))*p.w_parent_qty_uom2 calc_test, P.w_qty_used Ctrl 
 from table_test p  
inner join table_test c on
instr( p.chaine,c.chaine )=1  
group by
P.W_DATE
        ,P.W_ORIG_RM
        ,p.W_QTY_USED
        ,P.W_PARENT_MFG
        ,P.W_PARENT_QTY_UOM2
        ,P.W_LVL
        ,P.W_LEAF
        ,P.W_UNIT_QTY_USED
        ,P.EXPR
        ,P.CHAINE
order by p.w_leaf desc , p.w_parent_mfg, p.chaine

Mais je suis sûr que le code ci-dessus peut titiller votre créativité pour trouver une solution plus efficace et plus élégante.

Merci d'avance pour votre aide qui m'a déjà été bien utile!

Mars

**skuatamad** · 15/04/2011, 20h33

Envoyé par mars13008

j'ai l'impression que créée ma propre fonction d’agrégat ne répond pas à mon problème. L'idée serait plutôt de faire le calcul au fil des itération mais je pense pas que ce soit possible.

Justement c'est ça qui est bien avec les User-Defined Aggregates c'est qu'elles sont aussi analytiques

Regarde cette discussion pour différentes méthodes de produit cummulé

Et en utilisant le cumulativeproduct de mnitu :

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
SELECT CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
       ,cumulativeproduct(T2.W_UNIT_QTY_USED) over (partition by CONNECT_BY_ROOT (T2.W_ITEM) order by level) as cumul
       ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2)) as "W_UNIT_QTY_USED_FINAL"
  FROM TMP_PROD_FG T2
 START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
               AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
               AND T2.W_SOURCING_TYPE <> 'PURCHASE'
 
 W_ORIG_RM      CUMUL W_UNIT_QTY_USED_FINAL
---------- ---------- ---------------------
         1         .5                    .5
         1          1                     1
         1          3                     3
         2          1                     1
         2          2                     2
         2          6                     6
         6         .5                    .5
         6        1.5                   1.5
         7        .25                   .25
         7        .25                   .25
         7        .75                   .75
        10        2.5                   2.5

**mars13008** · 18/04/2011, 11h24

Merci pour vos nombreux retours!
Je suis en train d'essayer les solutions fournies mais je rencontre des petits soucis.

Ma version d'Oracle:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Regarder ci-dessous les requêtes et résultats associés...
Vous avez déjà rencontré ce comportement étrange?

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
 
WITH TMP_PROD_FG AS
(
SELECT to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' FROM DUAL 
)
SELECT CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
       ,cumulativeproduct(T2.W_UNIT_QTY_USED) over (partition BY CONNECT_BY_ROOT (T2.W_ITEM) ORDER BY level) AS cumul
       ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2)) AS "W_UNIT_QTY_USED_FINAL"
  FROM TMP_PROD_FG T2
 START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
               AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
               AND T2.W_SOURCING_TYPE <> 'PURCHASE'
 
W_ORIG_RM	CUMUL	W_UNIT_QTY_USED_FINAL
 
10	         0.5	0
10           1	    0
10           3	    0
10           1	    0
10           2	    0
10           6	    0
10           0.5	0
10           1.5	0
10           0.25	0
10           0.25	0
10           0.75	0
10           2.5	0
 
 
WITH TMP_PROD_FG AS
(
SELECT to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' FROM DUAL UNION ALL
SELECT to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' FROM DUAL 
)
SELECT CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
--       ,cumulativeproduct(T2.W_UNIT_QTY_USED) over (partition BY CONNECT_BY_ROOT (T2.W_ITEM) ORDER BY level) AS cumul
       ,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2)) AS "W_UNIT_QTY_USED_FINAL"
  FROM TMP_PROD_FG T2
 START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
               AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
               AND T2.W_SOURCING_TYPE <> 'PURCHASE'  
 
W_ORIG_RM	W_UNIT_QTY_USED_FINAL
 
1   	    0.5
1	        1
1	        3
2	        1
2	        2
2	        6
6	        0.5
6	        1.5
7	        0.25
7	        0.25
7	        0.75
10	        2.5

**mars13008** · 18/04/2011, 11h25

Précision:
Si je remplace la fonction analytique fournie par un simple Sum(), j'ai le même comportement...

Problème de performance sur un calcul en cascade

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager