Discussion :

[Billy _IRIS1]

Bonjour,

Je me permets de vous demander de l'aide pour optimiser une réplication de datas. Je ne suis pas un As en SQL et je pense que beaucoup de chose vont vous choquer (index, format de la base...), alors n'hésiter pas !

Posons le contexte :

Je fais un export de données d'une base Informix, j'obtiens donc de nombreux fichiers ctl variant de 5Ko à 32Mo.
Ces fichiers sont stockés dans un répertoire partagé.

Un cron (je suis sur Aix 5.3) s'occupe ensuite d'importer ses données dans un infocentre, bdd Oracle 8i. J'ai modifié il y a peu de temps la taille des blocs (de 4ko à 16ko).

Mais c'est là que le bas blesse, l'import est très très long (>72 heures ).

Je ne peux pas vous énoncer les trop nombreuses étapes d'import mais je pense qu'il serait intéressant que je vous expose les principales requêtes qui posent problèmes...

On charge dans une table Result_Trait (aucun index) de très nombreux résultats qui sont ensuite triés dans d'autres tables suivant leur paramètres.
On utilise donc des curseurs, des loops, des access full...

Par exemple on insert une partie des résultats dans une autre table Result_Man par le sql suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
declare
        cursor c1 is select * from result_trait where celem = 'GER' order by nodem, noordre;
begin
        for c1_enreg in c1 loop
        insert into result_man values 
        (c1_enreg.nodem,c1_enreg.noordre,c1_enreg.celem,c1_enreg.cexam,c1_enreg.result,0);  
        end loop;
commit;
end;
/
exit;

Ou encore ce genre de chose dans une autre table Result_Cmi

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
declare
        cursor c1 is select a.nodem, a.noordre, b.result, b.celem from result_cmi a, result_trait b
                     where a.nodem = b.nodem and a.noordre = b.noordre
                     and b.celem like 'C%' order by a.nodem, a.noordre;
begin
        for c1_enreg in c1 loop
                 update result_int set cmi = c1_enreg.celem,
                                valeur_cmi = c1_enreg.result
                 where nodem = c1_enreg.nodem
                 and   num_ordre = (c1_enreg.noordre-1);
 commit;
        end loop;
commit;
end;
/
exit;

Et ces traitements sont affreusement longs...
Auriez vous des pistes à me proposer ? Y'a t'il du code choquants pour les expert du forum ?


Merci d'avance pour votre aide.

William

[orafrance]

pourquoi utiliser des curseurs et faire un ORDER BY?

Code :

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

1
2
INSERT INTO result_man 
(SELECT nodem,noordre,celem,cexam,result,0 FROM result_trait WHERE celem = 'GER');

Pour l'UPDATE c'est toujours un peu pénible... peut-être via un BULK

[Billy _IRIS1]

pourquoi utiliser des curseurs et faire un ORDER BY?

En effet... Il n'y a aucune utilité de mettre en ORDER BY (je ne suis pas la poule qui est pondu ces lignes de codes...) ? En le supprimant je devrais déjà gagné un peu de temps de traitement non ?

Pour l'UPDATE c'est toujours un peu pénible... peut-être via un BULK

Je ne connais pas cette transaction, je vais donc me documenter.

Merci de tes conseils.

[orafrance]

En le supprimant je devrais déjà gagné un peu de temps de traitement non ?

pas forcément, ça dépend du volume à trier... tu gagneras bien plus à éviter l'insertion ligne à ligne en te passant du curseur

[Billy _IRIS1]

pas forcément, ça dépend du volume à trier... tu gagneras bien plus à éviter l'insertion ligne à ligne en te passant du curseur

OK alors tu penses qu'un :

Code :

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

1
2
3
4
INSERT INTO result_man VALUES (nodem,c1_enreg.noordre,celem,cexam,result,0)
SELECT * 
FROM result_trait 
WHERE celem = 'GER' ;

Serait bien plus rapide en commitant seulement à la fin ?

[orafrance]

Oui, sauf que la syntaxe est incorrecte

[Billy _IRIS1]

Arf le mauvais...

Exemple 10 : http://sqlpro.developpez.com/cours/sqlaz/dml/#LII-E

Code :

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

1
2
3
INSERT INTO   T_CLIENT (CLI_ID, TIT_CODE,       CLI_NOM, CLI_PRENOM)
       SELECT           PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
       FROM   T_PROSPECT

Alors :

Code :

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

1
2
3
4
INSERT INTO result_man (nodem, noordre, celem, cexam, result, 0)
SELECT                                  nodem, noordre, celem, cexam, result 
FROM result_trait 
WHERE celem = 'GER' ;

???

[orafrance]

Non

Code :

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

1
2
3
4
5
 
INSERT INTO result_man (nodem, noordre, celem, cexam, result, MANQUE_UNE_COLONNE)
SELECT   nodem, noordre, celem, cexam, result, 0
FROM result_trait 
WHERE celem = 'GER' ;

Dans ton code, à la place des colonnes tu mets des valeurs et en plus il en manque une

C'est pourquoi j'avais proposé la syntaxe sans les colonnes :

Code :

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

1
2
INSERT INTO result_man 
(SELECT nodem,noordre,celem,cexam,result,0 FROM result_trait WHERE celem = 'GER');

Attention aux cours de SQLPro, il traite de la NORME SQL et pas du SQL Oracle qui peut-être différent (ce qui n'est pas le cas ici )

[Billy _IRIS1]

Décidément ... Merci pour la syntaxe, je vais faire des tests de performances avec le curseur et sans.

[Billy _IRIS1]

Pfffffffffff y'a pas photo... Dix fois plus rapide...

Sinon je recherche bien pour mon update avec BULK mais c'est difficile de trouver des exemples précis... C'est donc si j'ai bien compris des insert via un fichier de données ? Mais je n'ai trouvé aucun exemple pour un update ?

Sinon voici le dernier SQL qui pose un gros problème :

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
declare
        germe          varchar2(80);
        exam           varchar2(5);
        num_ordre      number(5);
        reccup_ordre   number(5);
        reccup_nodem   varchar2(11) := ' ';
        cursor c1 is select * from result_man order by nodem, noordre;
begin
        for c1_enreg in c1 loop
          germe := c1_enreg.result;
          exam  := c1_enreg.cexam;
          num_ordre := c1_enreg.noordre;
		  select min(noordre) into reccup_ordre 
          from result_man 
          where nodem = c1_enreg.nodem and 
          cexam = c1_enreg.cexam and noordre > num_ordre;
		  if reccup_ordre is null
          then
               reccup_ordre := 99999;
          end if;
          insert into result_int 
          select distinct nodem, celem antibio, c1_enreg.result germe, c1_enreg.cexam cexam, result resist, '' cmi, 0 valeur_cmi, noordre num_ordre
          from result_trait 
          where nodem = c1_enreg.nodem and cexam = c1_enreg.cexam
          and (noordre > num_ordre and noordre < reccup_ordre)
          and (result is null or result in ('R','S','I'));
	commit;
        end loop;
commit;
end;
/
exit;

[orafrance]

Pfffffffffff y'a pas photo... Dix fois plus rapide...

Une rêgle d'or : il vaut toujours mieux traiter 1000000 lignes une fois que une ligne 1000000 fois

[Billy _IRIS1]

J'imagine qu'en reprenant notre discussion précédente je peux commencer par enlever le :

Code :

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

... order by nodem, noordre

dans le curseur...

Car j'ai du mal à imaginer comment faire pour simplifier ce sql sans utiliser de cursor....