Problème de performance avec mes requêtes update

Version imprimable

Bonjour,

J'ai éxécuté les requetes d'update sur une table document et radiologie.

Sur ma base de test voici les caractéristiques :

Nombre d’éléments dans la table Radiologie : 84
Nombre d’éléments dans la table Document : 1586
Nombre d’éléments dans la table SAG_DATA_TEMP : 8890

Pour faire une update de la table radiologie cela ne prend que 2 secondes. Par contre si je fais une update de la table document seulement cela fait 2 minutes et 11 secondes !

J'ai besoin d'améliorer les performances car la base de production a les caracteristiques suivantes :

Nombre d’éléments dans la table Radiologie : 139119
Nombre d’éléments dans la table Document : 197145
Nombre d’éléments dans la table SAG_DATA_TEMP : 70369

J'ai déjà essayé le script suivant sur la base de prod mais sur putty l'execution dure plus longtemps que le temps de la session. La durée est trop longue. Comment améliorer mon script suivant :

Je pense que la commande EXISTS pose probleme.

Il ya des index sur la table Document , radiologie mais pas sur SAG_DATA_TEMP. J'essaye de faire un execution plan mais jusqu'ici je n'ai pas réussi a le faire.

Voici mon script :
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 DECLARE CURSOR c_SAG_DATA IS SELECT * FROM SAG_DATA_TEMP; BEGIN FOR j IN c_SAG_DATA LOOP UPDATE radiologie rad SET rad.uh_demandeuse = j.code_uh_demande , rad.nip = j.nip_actif WHERE rad.ConcatID = j.s_aphp_reference_acte_rados; UPDATE document doc SET doc.nda = j.nda , doc.noip = j.nip_actif WHERE EXISTS ( SELECT 1 FROM radiologie rad WHERE rad.ConcatID = j.s_aphp_reference_acte_rados AND rad.id_document_lie = doc.id_document ); END LOOP; END; /
Merci

28/07/2011, 12h15
al1_24

Quelle est la nécessité de passer par un curseur sur SAG_DATA_TEMP ?
Y a-t-il une partie de ton code que tu ne nous donnes pas ?
28/07/2011, 12h18
orafrance

Faudrait au moins le plan d'exécution pour se prononcer :?

Sinon, essaye IN à la place d'EXISTS.

Pour moi ce serait plutôt le curseur le problème :?

Sans curseur, la mise à jour pourrait s'écrire comme cela :

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
UPDATE  radiologie rad
SET (   rad.uh_demandeuse
    ,   rad.nip
    ) = (   SELECT  j.code_uh_demande
                ,   j.nip_actif
            FROM    SAG_DATA_TEMP   j
            WHERE   rad.ConcatID = j.s_aphp_reference_acte_rados
        )
WHERE   EXISTS
        (   SELECT  1
            FROM    SAG_DATA_TEMP   j
            WHERE   rad.ConcatID = j.s_aphp_reference_acte_rados
        )
;
 
UPDATE  document doc
SET (   doc.nda
    ,   doc.noip
    ) = (   SELECT  j.nda
                ,   j.nip_actif
            FROM    radiologie rad
                  INNER JOIN
                      SAG_DATA_TEMP   j
            WHERE   rad.ConcatID        = j.s_aphp_reference_acte_rados
                AND rad.id_document_lie = doc.id_document
          )
WHERE   EXISTS
        (   SELECT  1
            FROM    radiologie rad
                INNER JOIN
                    SAG_DATA_TEMP   j
            WHERE   rad.ConcatID        = j.s_aphp_reference_acte_rados
                AND rad.id_document_lie = doc.id_document
        )
;

Pour améliorer les performances, il faut des index sur les colonnes utilisées dans les jointures donc en particulier sur SAG_DATA_TEMP.s_aphp_reference_acte_rados

Hello,

Assumons que ta table SAG_DATA_TEMP est grosse.

Essaie ca:
L'idée est de limiter les lectures sur cette table, si elle est grosse, et de ne prendre que les valeurs distinctes des 3 colonnes que tu utilise dans tes updates.
Je pense que tu dois faire un grand nombre d'updates inutiles.

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
 
Create index ind_SAG_DATA_TEMP on SAG_DATA_TEMP (code_uh_demande,nip_actif,s_aphp_reference_acte_rados);
 
DECLARE
 
CURSOR c_SAG_DATA IS
SELECT distinct code_uh_demande,nip_actif,s_aphp_reference_acte_rados FROM SAG_DATA_TEMP order by code_uh_demande,nip_actif,s_aphp_reference_acte_rados;
 
 
BEGIN
 
    FOR j IN c_SAG_DATA LOOP
 
	UPDATE  radiologie rad 
	SET     rad.uh_demandeuse   = j.code_uh_demande
		,   rad.nip             = j.nip_actif
		WHERE   rad.ConcatID = j.s_aphp_reference_acte_rados;
 
 
	UPDATE  document doc 
	SET     doc.nda     = j.nda
		,   doc.noip    = j.nip_actif
	WHERE   EXISTS
			(   SELECT  1
				FROM    radiologie rad
				WHERE   rad.ConcatID = j.s_aphp_reference_acte_rados
					AND rad.id_document_lie = doc.id_document
			);
 
    END LOOP;
 
END;
/

Jko

28/07/2011, 14h08
orafrance

Limiter les lectures sur la table en faisant un tri dessus... quelle drole d'idée :?

Pour limiter les lectures faudrait limiter le nombre de lignes lues. Après, c'est sûr que si le nombre de lignes traitées par le curseur est significativement réduit, ça peut avoir du sens mais on peut tout de même espérer que cette table temporaire ne contiennent que les infos utiles et suffisantes ;)
28/07/2011, 14h13
jkofr

C'est juste pour garantir que le tris de la relation va générer l'utilisation de l'index et un "Index fast full scan".

C'est mieux que de lire toute la table pour n'utiliser que 3 colonnes. non?

Mais comme je l'ai dit, je ne connais pas la structure de la table source et j'assume dans ma proposition qu'elle est grosse.

L'auteur du post pourra certainement nous renseigner...

Jko
28/07/2011, 14h27
Battosaiii

Merci de vos réponses.

Il y a beaucoup d'avis differents donc j'ai du mal a choisir la solution.
Je vais d'abord tenter de faire un index sur SAG_DATA_TEMP sur la colonne j.s_aphp_reference_acte_rados.

jkofr:

Je pensais que faire un index sur plusieurs colonnes en meme temps ralentaissaient les performances d'une requete ? J'ai lu sur un article quelque part.

En effet la table SAG_DATA_TEMP a beaucoup de lignes.
Nombre d’éléments dans la table SAG_DATA_TEMP : 70369

al1_24 :

J'ai déja essayé une solution sans curseur. J'ai d'ailleurs réalisé une requete similaire preque comme la tienne. Malheureusement cette requete dure plusieurs jours sur le cas suivant de base de données :

Nombre d’éléments dans la table Radiologie : 139119
Nombre d’éléments dans la table Document : 197145
Nombre d’éléments dans la table SAG_DATA_TEMP : 70369
28/07/2011, 14h33
jkofr

Plusieurs jours!

Alors la table SAG_DATA_TEMP est peut-être très grosse! Avec des BLOB ou des LONG?

Dans ce cas je maintien ma proposition avec un index sur les 3 colonnes de SAG_DATA_TEMP.

Citation:

Envoyé par Battosaiii

jkofr:

Je pensais que faire un index sur plusieurs colonnes en meme temps ralentaissaient les performances d'une requete ? J'ai lu sur un article quelque part.

Teste sur ta petite base pour voir...

Voilou!

Jko
28/07/2011, 15h30
orafrance

Citation:

Envoyé par jkofr

C'est juste pour garantir que le tris de la relation va générer l'utilisation de l'index et un "Index fast full scan".

C'est mieux que de lire toute la table pour n'utiliser que 3 colonnes. non?

Que tu fasses un index fast full scan ou un table full scan ça change pas grand chose. Ne lire que l'index n'a d'intérêt que si quand tu ne le fais pas tu as un accès à la table en plus, mais là toute la table est lue de toute façon. Par contre t'ajoute un tri :?
28/07/2011, 15h34
orafrance

Vraiment, j'vois pas l'intérêt de l'INDEX, je ne vois pas comment la création de l'index + lecture de l'index pourrait aller plus vite que le FTS sans index :koi:

L'index ne peut être intéressant qu'en supprimant le curseur me semble-t-il.

Le DISTINCT en revanche peut être intéressant s'il limite le nombre d’occurrences du curseur :)

Sinon, on a toujours pas de trace ou de plan d'exécution ;)
28/07/2011, 15h51
jkofr

Citation:

Envoyé par orafrance

Que tu fasses un index fast full scan ou un table full scan ça change pas grand chose. Ne lire que l'index n'a d'intérêt que si quand tu ne le fais pas tu as un accès à la table en plus, mais là toute la table est lue de toute façon. Par contre t'ajoute un tri :?

Hello, suis d'accord avec toi.
Pour info j'ai oublie la colonne nda qui doit aussi être dans l'index.
De ce fait, la table ne sera PAS lue et SEUL l'index SERA lu.

C'est bien la condition que tu cite non?
Ici, seul 4 colonnes de la table SAG_DATA_TEMP sont utiles.

Je ne propose pas de créer l'index juste avant le traitement.
L'index est permanent.

Jko
28/07/2011, 15h53
jkofr

Maintenant j'ai hâte de voir le modèle de données.
jko

Bonjour,

Il y a plusieurs points à considérer :

(1) c'est un update basé sur des selects. Ce qui veut dire que le temps d'exécution n'est pas concentré uniquement dans les selects. Le temps des "updates" est également à considérer surtout que ces deux tables contiennent des indexes. Quelques questions, comme les suivantes, auraient normalement dues être posées en premier lieu:

(a) Est ce que les deux tables document et radiologie ont des triggers?
(b) Est ce que ces deux tables possèdent des contraintes d'intégrités?

(2) vous dites que ces deux tables possèdent des indexes; combien d'indexes? quelles sonts leurs descriptions? quelle est la description de ces deux tables

(3) La seule table qui n'est pas mise à jour (sag_data_temp) n'a pas d'index, pourtant elle pourrait accélerer les différents selects

(4) vous commencez par un update de la table radiologie (2 sec) suivi par un update de la table document; mais cet update nécessitte un select sur la table radiologie qui vient juste d'être mise à jour. Il très probable que ce select sur la table radiologie fasse intervenir une lecture et une reconstruction de l'image à partir des rollbacks segment (read consistency) faisant d'un simple select une opération plus compliquée dans ce cas.

(5) comme l'a si bien présenté al_124, pourquoi faire cet update avec du PL/SQL quand on peut le faire avec du SQL?

Essayez de nous donner l'explain plan approximatif de vos updates en suivant l'exemple simple suivant:

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
 
mhouri@mhouri> explain plan for
  2   update emp
  3   set sal = 5000
  4   where empno = 7839;
 
Explicité.
 
mhouri@mhouri> select * from table (dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                                                                       
---------------------------------------------------------------------------------------------
Plan hash value: 1494045816                                                                                             
 
---------------------------------------------------------------------------                                             
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                             
---------------------------------------------------------------------------                                             
|   0 | UPDATE STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |                                             
|   1 |  UPDATE            | EMP  |       |       |            |          |                                             
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |                                             
---------------------------------------------------------------------------                                             
 
Predicate Information (identified by operation id):                                                                                                                                   
 
   2 - filter("EMPNO"=7839)                                                                                             
 
14 ligne(s) sélectionnée(s).

Une version de al_124 (non testée) légèrement modifiée que vous pouvez testez est la suivante

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
UPDATE document doc
SET ( doc.nda
    , doc.noip
    ) = (SELECT  j.nda
                ,j.nip_actif
         FROM    sag_data_temp   j
         WHERE  exists (select null
                        from radiologie rad
		where rad.ConcatID        = j.s_aphp_reference_acte_rados
                        and   rad.id_document_lie = doc.id_document
						)
        )
WHERE   EXISTS
        (SELECT  1
         FROM   radiologie rad
         WHERE  rad.id_document_lie = doc.id_document
          AND   exists (select null  
		                 from sag_data_temp   j
			             where j.s_aphp_reference_acte_rados = rad.ConcatID        
        )
;

Bien respectueusement

Mohamed Houri

29/07/2011, 10h31
orafrance

Citation:

Envoyé par jkofr

C'est bien la condition que tu cite non?

En effet, mais dans ce cas, on pourrait même lui conseiller de passer pas une IOT :)

Citation:

Envoyé par Mohamed.Houri

(3) La seule table qui n'est pas mise à jour (sag_data_temp) n'a pas d'index, pourtant elle pourrait accélerer les différents selects

Encore une fois, toutes les lignes étant sélectionnées, c'est pas déconnant ;)
29/07/2011, 11h13
jkofr

Citation:

Envoyé par orafrance

En effet, mais dans ce cas, on pourrait même lui conseiller de passer pas une IOT :)

Aussi :ccool:

Jko
29/07/2011, 12h27
Mohamed.Houri

Citation:

Envoyé par orafrance

Encore une fois, toutes les lignes étant sélectionnées, c'est pas déconnant ;)

Oui effectivement mais dans la version avec curseur. Dans la version réecrite cela peut eventuellement servir.

Bien à vous

Mohamed Houri
29/07/2011, 13h18
orafrance

Citation:

Envoyé par Mohamed.Houri

Oui effectivement mais dans la version avec curseur. Dans la version réecrite cela peut eventuellement servir.

Non plus ;) Eventuellement avec IN au lieu de EXISTS mais même pas puisque de toute façon toutes les lignes seront parcourues ;)
03/08/2011, 09h24
Battosaiii

Merci,

J'ai choisi une solution similaire à celle proposé par al1_24.
J'ai simplifié les requêtes en enlevant les exists et en trouvant une condition plus approprié. C'est beaucoup plus rapide sans curseur.
03/08/2011, 09h38
orafrance

Citation:

Envoyé par Battosaiii

C'est beaucoup plus rapide sans curseur.

CQFD ;)