Discussion :

[mirumoto]

Bonjour à tous c'est mon premier message ici!

J'ai un gros souci de performance sur un programme et j'aimerai avoir votre point sur ce qu'il faudrait faire pour que ce soit un peu plus rapide.

J'explique l'algo en faisant simple:

J'ai deux tables :
- une table de données très volumineuse (potentiellement des dizaines d emillions de lignes)- une table de résultat qui synthétise les données (21000 lignes)

Mon programme traite les lignes de ma table de données unes à unes, prend le montant de cette ligne et le décompose sur certaines lignes de ma table de résultat.

Aujourd'hui avec mon programme actuel j'ai créé un curseur sur ma table de données qui balaye toutes les lignes (après les avoir regroupées) et je balaye ensuite les lignes de ma table de résultat qui correspondent à cette ligne de données.
En fait sur le deuxième curseur j'ai un créé un SQL dynamique en fonction de ma ligne de données.
Le deuxième curseur peut contenir entre quelques enregistrements à plusieurs milliers.

Enfin sur chacune des lignes de résultat je fais un calcul et j'update la ligne. Donc en fait je peux faire plusieurs milliers d'update par ligne de ma table de données!

Je mets ci-dessous une partie du 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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
 
cursor mont_tous_cri is select DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9, SUM(Montant) as montant 
from Tbl_donnees where DC1 is NOT NULL and DC2 is NOT NULL and DC3 is NOT NULL and DC4 is NOT NULL 
and DC5 is NOT NULL and DC6 is NOT NULL and DC7 is NOT NULL and DC8 is NOT NULL and DC9 is NOT NULL GROUP BY DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9;
montant_tout_cri mont_tous_cri%rowtype;
 
for montant_1_CRI in mont_1_CRI loop
 
  demande(1) := montant_1_CRI.DC1;
  if demande(1) is not null then
    cpt_zero := cpt_zero + 1;
    critere1 := ' C1 = '|| demande(1) ;
  end if;
 
  demande(2) :=montant_1_CRI.DC2;
 
  if demande(2) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C2 = '|| demande(2) ;
      else
      critere1 := critere1 || ' and C2 = '|| demande(2) ;
    end if;
  end if;
 
  demande(3) :=montant_1_CRI.DC3;
 
  if demande(3) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C3 = '|| demande(3) ;
      else
      critere1 := critere1 || ' and C3 = '|| demande(3) ;
    end if;
  end if;
 
  demande(4) :=montant_1_CRI.DC4;
 
  if demande(4) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C4 = '|| demande(4) ;
      else
      critere1 := critere1 || ' and C4 = '|| demande(4) ;
    end if;
  end if;
 
  demande(5) :=montant_1_CRI.DC5;
 
  if demande(5) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C5 = '|| demande(5) ;
      else
      critere1 := critere1 || ' and C5 = '|| demande(5) ;
    end if ;
  end if;
 
  demande(6) :=montant_1_CRI.DC6;
 
  if demande(6) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C6 = '|| demande(6) ;
      else
      critere1 := critere1 || ' and C6 = '|| demande(6) ;
    end if;
  end if;
 
  demande(7) :=montant_1_CRI.DC7;
 
  if demande(7) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C7 = '|| demande(7) ;
      else
      critere1 := critere1 || ' and C7 = '|| demande(7) ;
    end if; 
  end if;
 
  demande(8) :=montant_1_CRI.DC8;
 
  if demande(8) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C8 = '|| demande(8) ;
      else
      critere1 := critere1 || ' and C8 = '|| demande(8) ;
    end if;
  end if;
 
  demande(9) :=montant_1_CRI.DC9;
 
  if demande(9) is not null then
    cpt_zero := cpt_zero + 1;
    if cpt_zero = 1 then
      critere1 := ' C9 = '|| demande(9) ;
      else
      critere1 := critere1 || ' and C9 = '|| demande(9);
    end if;
  end if;
 
  sum_montant := montant_1_CRI.montant;
  strsql := 'select * from VW_TBL_RES_UTILE where' || critere1 || '';
 
  open c_cur for strsql;
  loop
    fetch c_cur into C_final;
    exit when c_cur%notfound;
    res(1) :=C_final.C1;
    res(2) :=C_final.C2;
    res(3) :=C_final.C3;
    res(4) :=C_final.C4;
    res(5) :=C_final.C5;
    res(6) :=C_final.C6;
    res(7) :=C_final.C7;
    res(8) :=C_final.C8;
    res(9) :=C_final.C9;
    pres_donnes :=C_final.pres_donnees;
    montant1CRI :=C_final.montant;
 
    montant_tot_CRI := pres_donnes / nombre * sum_montant;
    montant_tot2 := montant_tot_CRI + montant1CRI;
 
    UPDATE Tbl_res set Tbl_res.montant = montant_tot2 where C1 = offre(1)  and C2 = offre(2) and C3 = offre(3) and C4 = offre(4) 
    and C5 = offre(5) and C6 = offre(6) and C7 = offre(7) and C8 = offre(8) and C9 = offre(9);
 
  end loop;
  close c_cur;
  -- mise à zero des donnees
  cpt_zero := 0;
  critere1 := '';
end loop;

Bref pour traiter 10000 lignes mon programme met 27 min. C'est juste horrible.
De mon avis de profane avec de faibles connaissances en architecture il me semble que faire pleins d'updates ralentit considérablement les perfs car on écrit des données à chaque fois.Ou alors est ce que c'est le fait d'utiliser deux curseurs imbriqués?
Est ce que ce ne serait pas mieux de passer par un tableau de valeurs ou par une table temporaire gérée uniquement en mémoire?
J'alimente ma table au fur et à mesure du code puis je fais un update juste à la fin du programme.

Si vous avez des idées d'amélioration je suis preneur de toute information!

Merci d'avance pour vos lumières!

[Waldar]

Curseurs imbriqués = le mal.
Update ligne à ligne = le mal.
SQL dynamique par concaténation lorsqu'on peut utiliser des binds variable = le mal.

Je n'ai pas cerné tout votre besoin, mais je suis perduadé que ça passe en une seule commande SQL.

[mirumoto]

OK merci pour l'info c'est bien ce que je pensais. En fait c'est carrément une usine à gaz mon truc.

je vais essayer de passer par une vue qui imbrique les deux tables dans un premier temps histoire de limiter les curseurs et le nombre d'updates. Je reviendrai vers vous pour le gain de temps.

[mirumoto]

J'ai pu bien avancer sur le sujet :

Après analyse le temps de traitement uniquement de mes updates lignes à lignes était de 1600 secondes sur les 1800 secondes de mon programme soit 90% du temps.
J'ai amélioré mon SQL et stocké tous mes calculs dans un tableau de données pour faire un update de ma table avec les résultats du tableau et j'arrive à un temps de 83 secondes soit 20 fois plus rapide! J'ai gardé le SQL dynamique le temps d'exécution du SQL était négligeable à comparaison du temps d'update.

Il me reste d'autres choses à optimiser sur la table de données mais je suis sur la bonne voie!

[Waldar]

Je pense que ça irait encore plus vite si vous écriviez un simple ordre SQL complet, sans étape intermédiaire.

N'hésitez pas à poster votre dernier code afin de voir si c'est encore améliorable !

[mnitu]

...J'ai gardé le SQL dynamique le temps d'exécution du SQL était négligeable à comparaison du temps d'update.
...

Avez-vous testé en utilisant N utilisateurs connectés simultanément et faisant à peu prés la même chose ?

[mirumoto]

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
 
--tableau de résultats annoté
type res is record (ID number,
                         Montant number,
                         pres_donnees number,
                         Ordre number(5,0));
 
type tableau_res is table of res not null index by varchar(20);
resultat tableau_res;
type Tab_Offre is table of number index by binary_integer;
Offre Tab_Offre;
type Tab_demande is table of number index by binary_integer;
demande Tab_demande;
 
cursor mont_1_CRI is select DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9, SUM(Montant) as montant from Tbl_donnees 
where (DC1 <> 0 or DC2 <> 0 or DC3 <> 0 or DC4 <> 0 or DC5 <> 0 or DC6 <> 0 or DC7 <> 0 or DC8 <> 0 or DC9 <> 0)
and not (DC1 is not null and DC2 is not null and DC3 is not null and DC4 is not null and DC5 is not null 
and DC6 is not null and DC7 is not null and DC8 is not null and DC9 is not null) group by DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9;
montant_1_CRI mont_1_CRI%rowtype;
 
for montant_1_CRI in mont_1_CRI loop
 
  demande(1) := montant_1_CRI.DC1;
 
  if demande(1) is not null then
    cpt_zero := cpt_zero + 1;
    critere1 := ' DC1 = '|| demande(1) ;
    jointure := 'C1 = DC1';
    else
    critere1 := critere1 || ' DC1 is null';
    cpt_zeroj := cpt_zeroj + 1;
  end if;
 
  demande(2) :=montant_1_CRI.DC2;
 
  if demande(2) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC2 = '|| demande(2) ;
    if cpt_zero = 1 then
      jointure := 'C2 = DC2';
      else
      jointure := jointure || ' and C2 = DC2';
    end if;
    else
        critere1 := critere1 || ' and DC2 is null';
  end if;
 
  demande(3) :=montant_1_CRI.DC3;
 
  if demande(3) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC3 = '|| demande(3) ;
    if cpt_zero = 1 then
      jointure := 'C3 = DC3';
      else
      jointure := jointure || ' and C3 = DC3';
    end if;
    else
        critere1 := critere1 || ' and DC3 is null';
  end if;
 
  demande(4) :=montant_1_CRI.DC4;
 
  if demande(4) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC4 = '|| demande(4) ;
    if cpt_zero = 1 then
      jointure := 'C4 = DC4';
      else
      jointure := jointure || ' and C4 = DC4';
    end if;
    else
        critere1 := critere1 || ' and DC4 is null';
  end if;
 
  demande(5) :=montant_1_CRI.DC5;
 
  if demande(5) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC5 = '|| demande(5) ;
    if cpt_zero = 1 then
      jointure := 'C5 = DC5';
      else
      jointure := jointure || ' and C5 = DC5';
    end if;
    else
        critere1 := critere1 || ' and DC5 is null';
  end if;
 
  demande(6) :=montant_1_CRI.DC6;
 
  if demande(6) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC6 = '|| demande(6) ;
    if cpt_zero = 1 then
      jointure := 'C6 = DC6';
      else
      jointure := jointure || ' and C6 = DC6';
    end if;
    else
        critere1 := critere1 || ' and DC6 is null';
  end if;
 
  demande(7) :=montant_1_CRI.DC7;
 
  if demande(7) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC7 = '|| demande(7) ;
    if cpt_zero = 1 then
      jointure := 'C7 = DC7';
      else
      jointure := jointure || ' and C7 = DC7';
    end if;
    else
        critere1 := critere1 || ' and DC7 is null';
  end if;
 
  demande(8) :=montant_1_CRI.DC8;
 
  if demande(8) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC8 = '|| demande(8) ;
    if cpt_zero = 1 then
      jointure := 'C8 = DC8';
      else
      jointure := jointure || ' and C8 = DC8';
    end if;
    else
        critere1 := critere1 || ' and DC8 is null';
  end if;
 
  demande(9) :=montant_1_CRI.DC9;
 
  if demande(9) is not null then
    cpt_zero := cpt_zero + 1;
     critere1 := critere1 || ' and DC9 = '|| demande(9) ;
    if cpt_zero = 1 then
      jointure := 'C9 = DC9';
      else
      jointure := jointure || ' and C9 = DC9';
    end if;
    else
        critere1 := critere1 || ' and DC9 is null';
  end if;
 
  sum_montant := montant_1_CRI.montant;
 
  strsql := 'select VW_TBL_RES_UTILE.ID, Ordre, DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9, SUM(tbl_donnees.Montant) as montant from TBL_DONNEES 
  INNER JOIN VW_TBL_RES_UTILE on ' || jointure || ' where' || critere1 || ' group by VW_TBL_RES_UTILE.ID, VW_TBL_RES_UTILE.Ordre, DC1, DC2, DC3, DC4, DC5, DC6, DC7, DC8, DC9';
  --dbms_output.put_line(strsql);
  --dbms_output.put_line('C1 ' || demande(1) || ' C2 ' || demande(2) || ' C3 ' || demande(3) || ' C4 ' || demande(4) ||' C5 '
  --|| demande(5) || ' C6 ' || demande(6) || ' C7 ' || demande(7) || ' C8 ' || demande(8) ||' C9 ' ||demande(9) ||' ');
 
  open c_cur for strsql;
 
  loop
 
   fetch c_cur into C_final;
 
    exit when c_cur%notfound;
 
    ID_FIN := C_final.ID;
    Ordre_tab := C_final.Ordre;
 
    --calcul nouveau montant
 
    pres_donnes :=resultat(Ordre_tab-1).pres_donnees;
    montant1CRI :=resultat(Ordre_tab-1).montant;   
    montant_tot_CRI := pres_donnes / nombre * sum_montant;
 
    montant_tot2 := montant_tot_CRI + montant1CRI;
    resultat(Ordre_tab).montant := montant_tot2;
 
  end loop;
 
  close c_cur;
 
  -- mise à zero des donnees
  cpt_zero := 0;
  critere1 := '';
  jointure := '';
end loop;
 
--update final
for i in 0..cpt_res-1 loop
  update TBL_RES set TBL_RES.montant = resultat(i).montant where resultat(i).ID=TBL_RES.ID;
end loop;

Merci pour vos commentaires. En fait il n'y a pas d'utilisateurs sur cette base je sais c'est bizarre. On lance nos programmes et les données sont envoyées à un outil de restitution.

J'ai remis mon code retravaillé avec le tableau de données que j'alimente. J'ai ajouté dans mon SQL dynamique une jointure sur une vue VW_TBL_RES_UTILE. Je ne vois pas comment je peux me passer du SQL dynamique car ma jointure change à chaque ligne!

Je m'explique j'ai 9 critères dans ma table 1 appelé DC1 à DC9. Mon premier curseur liste toutes les lignes à traiter dans ma table de données. Ensuite je lance un deuxième curseur qui va pointé chaque ligne en faisant une jointure sur ma vue. La jointure en question ne portera que les champs DC1 à DC9 non null.
Pour moi elle est là la vraie complexité du code. Le code n'est peut être pas très clair à la lecture.
exemple : si ma première ligne a les critères 1 à 4 remplis alors je ferais une jointure sur ma vue uniquement sur ces 4 critères.

Je pensais dans un second temps découper ma table de données en plusieurs vues en fonction du nombre de critères remplis.

Toute idée est la bienvenue!
merci

[Waldar]

Déjà votre première requête, vos filtres sont bien justifiés ?

Il faut que parmi vos colonnes DC1 à 9, au moins une ne vaille pas zéro mais qu'au moins une également soit nulle, c'est bien ça ?

Je vois déjà ce qu'on peut simplifier, l'idéal serait que vous fournissiez quelques données représentatives de votre table et votre vue (une dizaine de lignes pour chacune, qui correspondent), et ce que vous cherchez à obtenir à la fin.

[mirumoto]

Non effectivement les filtres du premier curseur ne sont plus justifiés avec mes dernières modifications.
Je pourrais faire un simple :

Code :

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

1
2
 
select XX from tbl_donnees group by DC1, DC2... DC9

J'ai mis en PJ un tout petit exemple de ce que je recherche avec une table de données de 3 lignes. Le SQL dynamique crée les relations entre les tables + les critères correspondants. Une fois que j'ai cette table je fais mes calculs dessus.
Je ne sais pas si c'est très clair mais une valeur null dans la table de données peut être n'importe quelle valeur dans ma table de résultat.


J'en profite pour poser une autre question sur le temps d'exécution :
J'ai lancé un jeu d'essai avec ma procédure le temps d'exécution était de 19 secondes. J'ai relancé ensuite le même programme immédiatement après et là le temps est passé à 12 secondes.

Pourquoi le temps à t-il drastiquement baissé? J'aurais tendance à penser intuitivement qu'ORACLE a gardé en mémoire les curseurs ou mes tableaux et donc l'exécution serait plus rapide.

Je n'ai pas créé de package je lance juste ma procédure.

Merci!

[Waldar]

Effectivement, votre requête étant déjà exécutée une partie (ou la totalité) se trouve dans la mémoire, les accès physiques sont donc moindre sur les exécutions suivantes.

Dans votre exemple, il y a une incohérence enfin je crois.
La ligne de résultat (2, 3) peut prendre soit Y soit Z selon vos critères.
Quel résultat souhaitez-vous voir mis à jour dans votre table ?

[mirumoto]

Ben justement c'est ce que je veux. Je veux prendre la valeur Y, faire des calculs dessus et ensuite prendre la valeur Z et refaire des calculs. en fait une ligne de la table de résultat peut être calculée X fois en fonction des données de la table en entrée.

Pour mes requêtes je suis parti de la table de données et je pointe sur la table de résultat est ce que ce ne serait pas mieux de faire l'inverse?

Il y a nettement moins de lignes dans la table de résultat!

[Waldar]

Oui mais à la fin de votre procédure vous faites un UPDATE de votre table de résultats.
Donc si vous avez n valeurs concurrentes, vous en calculez (n-1) pour rien.

[mirumoto]

oui j'ai compris ce que vous voulez dire je vais être plus clair :

Dans mon exemple j'ai deux valeur Y et Z.
En fait je veux faire :
- Prendre la valeur Y
- faire mes calculs sur cette valeur
- enregistrer ce résultat dans un tableau que j'ai appelé "res" sur la ligne correspondante à ma valeur Y
- faire défiler le curseur et prendre la valeur Z
- refaire les calculs
- enregistrer la nouvelle valeur sur la même ligne de mon tableau en l'ajoutant au resultats précédents (calcul Y). Je tiens compte de ce fait des 2 résultats.
- une fois que j'ai tout calculé je prend chaque ligne de mon tableau et je les copie dans ma table de résultat dans l'UPDATE.

merci pour vos réflexions

[Waldar]

D'accord j'ai compris. Bon dans votre exemple, on ne peut pas additionner Y et Z, il aurait été plus judicieux de mettre des nombres

C'est pour ça que je préfère un exemple réel avec les calculs que vous faites !

Supposons que vous vouliez en faire la somme, j'ajuste votre exemple ainsi :

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
CREATE TABLE DONNEES
(
    DC1    NUMBER(1), 
    DC2    NUMBER(1), 
    VALEUR NUMBER(1)
);
 
Insert into DONNEES (DC1,DC2,VALEUR) values (1,2,7);
Insert into DONNEES (DC2,VALEUR) values (3,8);
Insert into DONNEES (DC1,VALEUR) values (2,9);
 
 
CREATE TABLE RESULTAT
(
    C1       NUMBER(1), 
    C2       NUMBER(1), 
    RESULTAT INTEGER
);
 
Insert into RESULTAT (C1,C2) values (1,1);
Insert into RESULTAT (C1,C2) values (2,2);
Insert into RESULTAT (C1,C2) values (1,3);
Insert into RESULTAT (C1,C2) values (2,1);
Insert into RESULTAT (C1,C2) values (1,2);
Insert into RESULTAT (C1,C2) values (2,3);
 
commit;

On peut directement, en une seule requête, allez mettre à jour les données tout en effectuant le calcul :

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
 merge into resultat r
 using (select r.c1, r.c2, sum(d.valeur) as resultat
          from resultat r
               inner join donnees d
                 on (r.c1 = d.dc1 and r.c2 = d.dc2)
                 or (r.c1 = d.dc1 and d.dc2 is null)
                 or (r.c2 = d.dc2 and d.dc1 is null)
      group by r.c1, r.c2) s
    on (r.c1 = s.c1 and r.c2 = s.c2)
  when matched then
update set r.resultat = s.resultat;
-- 5 rows merged.
 
select * from resultat;
 
C1                     C2                     RESULTAT               
---------------------- ---------------------- ---------------------- 
1                      1                                             
2                      2                      9                      
1                      3                      8                      
2                      1                      9                      
1                      2                      7                      
2                      3                      17

[mirumoto]

OK j'ai compris effectivement ça marche mais il y a un hic :
Là on était sur un cas simple avec 2 critères, dans ma table j'en ai 9 cela fait quelques dizaines de milliers de conditions à mettre dans le inner join.

Ca ne va pas faire un peu trop gros?

[Waldar]

Vous pouvez utiliser la fonction nvl ou coalesce pour réduire le nombre de critères... à 9 !

Code :

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

1
2
3
4
5
6
7
8
9
10
 merge INTO resultat r
 USING (SELECT r.c1, r.c2, sum(d.valeur) AS resultat
          FROM resultat r
               INNER JOIN donnees d
                 ON r.c1 = coalesce(d.dc1, r.c1)
                AND r.c2 = coalesce(d.dc2, r.c2)
      GROUP BY r.c1, r.c2) s
    ON (r.c1 = s.c1 AND r.c2 = s.c2)
  when matched then
UPDATE SET r.resultat = s.resultat;

[mirumoto]

OK merci!!!

je vais analyser tout ça et je reviendrai poster les améliorations!!

[mirumoto]

J'ai refait des tests en utilisant ta méthode et c'est pas convaincant malheureusement!

En fait j'ai créé un curseur qui donne quasiment le résultat voulu en utilisant les coalesce. Donc dans mon code je n'ai plus que le curseur de sélection des lignes. J'ai viré le deuxième curseur et le SQL dynamique.

Je l'ai lancé sur mon jeu d'essai de 300 lignes, impeccable, 5 fois plus rapide.
Je l'ai ensuite lancé sur un jeu d'essai de 10000 lignes qui mettait 15 s avec le SQL dynamique.
Sur la nouvelle méthode je lance le programme ça mouline, ça mouline le résultat finit par tomber : 658 s! En fait c'est le select du curseur qui est méga long.
Je suis en train de me demander qu'en essayant de tout faire d'un coup c'est pas si bien que ça. Par contre le code est dix fois plus petit!

le SQL du curseur mega long :

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
 
cursor mont_1_CRI is SELECT count(*) as compte, sum(d.montant) as resultat, r.ordre, sum(nb_occ) as nombre_occ 
          FROM vw_tbl_res_utile r
               INNER JOIN tbl_donnees d
                 ON r.c1 = coalesce(d.dc1, r.c1)
                AND r.c2 = coalesce(d.dc2, r.c2)
                AND r.c3 = coalesce(d.dc3, r.c3)
                AND r.c4 = coalesce(d.dc4, r.c4)
                AND r.c5 = coalesce(d.dc5, r.c5)
                AND r.c6 = coalesce(d.dc6, r.c6)
                AND r.c7 = coalesce(d.dc7, r.c7)
                AND r.c8 = coalesce(d.dc8, r.c8)
                AND r.c9 = coalesce(d.dc9, r.c9) 
                INNER JOIN VW_NBRE_OCC_1_CRI_11 V
                ON V.resultat=d.montant GROUP BY r.ordre

Je suis passé par une vue complémentaire VW_NBRE_OCC_1_CRI_11 pour me donner une table consolidant le nombre de lignes à traiter par montant.

Ca vous parait normal que ce soit si long? Dans mon jeu d'essai de 10000 lignes ce SQL retourne 2 Millions de lignes. Ca va devenir démeusurément grand si j'aggrandis la table de données.

[mnitu]

...

Code :

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

1
2
3
4
5
6
7
...          FROM vw_tbl_res_utile r
               INNER JOIN tbl_donnees d
                 ON r.c1 = coalesce(d.dc1, r.c1)
                AND r.c2 = coalesce(d.dc2, r.c2)
                AND r.c3 = coalesce(d.dc3, r.c3)
...

...
Ca vous parait normal que ce soit si long? Dans mon jeu d'essai de 10000 lignes ce SQL retourne 2 Millions de lignes. Ca va devenir démeusurément grand si j'aggrandis la table de données.

Ughhh, je n'aime pas. Ca peut être un produit cartésien

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> with tab1 as (
  2    select 1 id, 'toto' val from dual union all
  3    select 2 id, 'toto' val from dual union all
  4    select 3 id, 'toto' val from dual
  5  ), tab2 as (
  6    select cast (Null as number) id, 'titi' val from dual union all
  7    select cast (Null as number) id, 'titi' val from dual
  8  )
  9  select count(*)
 10    from tab1 join tab2 on (tab1.id = coalesce(tab2.id,tab1.id))
 11  /
 
  COUNT(*)
----------
         6
 
SQL>

Ce qui explique pourquoi ça rame.

[Waldar]

Finalement votre première condition sur le fait qu'au moins une des colonnes de votre table soit non-nulle était peut-être nécessaire, comme le démontre mnitu si toutes les colonnes de jointures sont nulles, on arrive directement sur un produit cartésien.

Essayez en reprenant ce code-ci :

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
  SELECT count(*) AS compte, sum(d.montant) AS resultat,
         r.ordre, sum(nb_occ) AS nombre_occ 
    FROM vw_tbl_res_utile r
         INNER JOIN tbl_donnees d
           ON r.c1 = coalesce(d.dc1, r.c1)
          AND r.c2 = coalesce(d.dc2, r.c2)
          AND r.c3 = coalesce(d.dc3, r.c3)
          AND r.c4 = coalesce(d.dc4, r.c4)
          AND r.c5 = coalesce(d.dc5, r.c5)
          AND r.c6 = coalesce(d.dc6, r.c6)
          AND r.c7 = coalesce(d.dc7, r.c7)
          AND r.c8 = coalesce(d.dc8, r.c8)
          AND r.c9 = coalesce(d.dc9, r.c9) 
         INNER JOIN VW_NBRE_OCC_1_CRI_11 V
           ON V.resultat= d.montant
   WHERE d.dc1 is not null
      OR d.dc2 is not null
      OR d.dc3 is not null
      OR d.dc4 is not null
      OR d.dc5 is not null
      OR d.dc6 is not null
      OR d.dc7 is not null
      OR d.dc8 is not null
      OR d.dc9 is not null
GROUP BY r.ordre;