Discussion :

[JeanYvette]

Bonjour bonjour

J'aurais besoin de votre avis sur une optimisation de procèdure stockée. Celle-ci est assez longue donc je n'ai pas pris le temps de l'anonymiser mais ma demande est plus sur le théorique :

Je dois récupérer une procédures qui fait plusieurs actions mais à chaque fois du même type :

- 4 Curseurs différents avec, à chaque fois, une requête sur au moins 3 tables. Des filtres utilisant les index.
- 4 Boucles (spoiler alert, une par curseur) parcourant le curseur, faisant au moins un IF sur une colonne du curseur pour y changer des données
Exemple :

Code :

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

1
2
3
4
5
6
7
 
     for ligne in curseur1
     loop
           IF ligne.macolonne = 'FI' 
           THEN
                   ligne.registre = 0
           END IF

- Dans chacun des curseurs, un update puis un delete.
L'update met à jour des tables produits, le delete supprime le produit mis à jour d'une table "d'attente de mise à jour".


Le souci c'est que le code est bof provoquant ainsi des millions de delete/update alors qu'il n'y a clairement pas autant de lignes.

Pour l'optimisation, et ca va être là où j'ai besoin d'un avis, j'imaginais 3 étapes :

1) Revoir les requêtes SELECT qui sont dans les curseurs
2) Virer les clauses IF pour les mettre dans des CASE directement dans les SELECT
3) Virer les curseurs et les loop pour passer à un update avec select et la petite option RETURNING comme sur l'exemple suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  UPDATE t1
  SET    description = description
  RETURNING id BULK COLLECT INTO l_tab;
 
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;

Et me servir de la boucle pour la clause DELETE.

C'est sur le 3éme point que j'ai besoin de votre avis. Sur le papier, virer les curseurs pour passer par une requête directe me semble bien, surtout qu'en utilisant le RETURNING ensuite je peux enchainer le delete associé sans (normalement) de difficulté.


Est-ce une idée à la noix ?

Bisous bisous

[Ikebukuro]

Salut,

Je note
"le code est bof provoquant ainsi des millions de delete/update alors qu'il n'y a clairement pas autant de lignes."

Pour l'optimisation, et ca va être là où j'ai besoin d'un avis, j'imaginais 3 étapes :
1) Revoir les requêtes SELECT qui sont dans les curseurs"

Avant d'aller plus loin avec la clause Returning, le bulk collect, je te conseille de te focaliser sur le point 1 car si tu fais sauter des millions d'Update et de Delete, ton code devrait déjà être fortement plus rapide.
Reviens vers nous une fois le point 1 éclairci mais ça devrait déjà répondre à ton besoin.

[vanagreg]

Bonjour,

Oui c'est la bonne démarche que tu veux entreprendre. Les parcours de curseur pour la mise à jour de données c'était à la mode au siècle dernier. C'est en effet des instructions DML directes qu'il faut utiliser, avec des CASE dans les select pour filtrer/calculer des valeurs. Tu peux utiliser un RETURNING BULK COLLECT pendant l'update pour stocker des id. Cependant s'il y a des millions de lignes attention à ne pas exploser la PGA lors du parcours de la collection.
Sinon si tes 4 curseurs utilisent plus ou moins les mêmes données, tu peux utiliser une table temporaire (un global temporary table) pour y stocker les informations source qui serviront aux updates des données cibles.

[JeanYvette]

Merci pour vos réponses,

En effet, juste modifier les selects ne suffit pas même si j'ai pu bien améliorer les performances de la procédures stockée. Je voulais donc virer les curseurs pour faire des updates sauf que là, tout de suite, soit mon cerveau a fondu soit je suis débile…

Mon update concerne plusieurs colonnes et, dans la clause where, la jointure entre la table updaté et les colonnes du curseurs sont multiples du style :

Code :

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

1
2
3
 
UPDATE maTable set macolonne = 'O', maColonneDeux = 'traitement', dateJour = sysdate
WHERE idcols = curseurboucle.id AND colonneTruc = curseurboucle.truc [...]

Je pourrais remplacer cela par la clause MERGE mais mon souci est le suivant :
Si je fais un merge avec l'update dans la clause WHEN MATCHED, il faut que j'appelle une procédure stockée pour gérer en même temps le delete que j'ai suite à l'update => Niveau perf, j'ai peur que ça pue

J'essaye de faire dans un update comme je l'avais imaginé au début, avec l'exemple que j'avais donné mais là, je sais pas, je coince. Comment je peux faire un update où je joins plusieurs colonnes de la clause WHERE de l'update aux colonnes d'un select (le select me sert en fait à savoir quelles lignes modifier en fonction de diverses colonnes qui servent de clés multiple)

J'espère avoir été assez clair dans mes explications, j'ai un petit doute mais on verra...

Bisous bisous

[vanagreg]

Bonjour,

Avec un UPDATE on utilise souvent l'opérateur EXISTS pour sélectionner les lignes à mettre à jour:

Code :

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

1
2
3
4
5
6
UPDATE maTable t set macolonne = 'O', maColonneDeux = 'traitement', dateJour = sysdate
WHERE EXISTS (select 1 from tablesource
                      where t.idcols = tablesource.id 
                         AND t.colonneTruc = tablesource.truc
                      ...
                    ) ;

Sinon quand l'update nécessite des valeurs des données sources, alors j'utilise MERGE. La syntaxe est beaucoup plus souple. Puisque tu veux faire un DELETE, sache qu'il est possible d'ajouter une clause DELETE à la clause MATCHED d'un MERGE. Ceci te permet de supprimer des lignes pré ou post UPDATE (tu ajoutes une clause WHERE à la clause DELETE pour sélectionner les lignes que tu veux).

[JeanYvette]

Ah mais oui mais c'est bien sûr... J'avais pas pensé au EXIST, je me déteste si fort

Oui pour le merge, totalement d'accord. Mais la limite c'est que dans le MATCHED, je ne peux pas faire un update ET un delete, à moins que j'ai loupé quelque chose depuis ?