Amélioration d'un process d'écriture

**Waldar** · 29/03/2011, 15h25

Bonjour à tous,

Dans le cadre d'un développement je dois extraire depuis une base de données des fichiers XML, afin de les envoyer à des tiers.

Pour générer le XML, j'ai codé ce qu'il faut dans une vue en passant pas des objets créé pour le besoin : pas de soucis de ce côté là, ça répond plutôt bien.

L'envoi des fichiers est piloté par une table tierce qui contient les méta-données.

Là où je ne suis pas satisfait de mon process, c'est sur l'extraction de ces XML vers les fichiers.

Certaines limitations me sont imposées :

dans ma table d'envoi figure un code et je dois déclarer un fichier par code.
un fichier ne doit pas dépasser 10.000 objets.

Mon environnement est Oracle Database Enterprise Edition 11gR1 11.1.0.7, 64 bits qui opère sur Windows Server 2003.

J'ai créé un jeu de test relativement simple pour illustrer tout ceci :

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
create directory TST as 'c:\TEMP\TST';
 
create table donnees
(
    col1      number(1)         not null,
    col2      varchar2(10 char) not null,
    constraint pk_donnees
      primary key (col1)
      using index
);
 
insert into donnees (col1, col2) values (1, 'ABC');
insert into donnees (col1, col2) values (2, 'DEF');
insert into donnees (col1, col2) values (3, 'GHI');
insert into donnees (col1, col2) values (4, 'JKL');
insert into donnees (col1, col2) values (5, 'MNO');
insert into donnees (col1, col2) values (6, 'PQR');
insert into donnees (col1, col2) values (7, 'STU');
insert into donnees (col1, col2) values (8, 'VWX');
insert into donnees (col1, col2) values (9, 'YZ' );
 
commit;
 
create view v_donnees_xml
as
select col1, XMLElement("SuperBalise", col2) as col_XML
  from donnees;
 
create table envoi
(
    col1      number(1)         not null,
    code      varchar2(1 char)  not null,
    chk       varchar2(1 char)      null,
    constraint pk_envoi
      primary key (col1)
      using index
);
 
insert into envoi (col1, code, chk) values (1, 'A', 'X');
insert into envoi (col1, code, chk) values (2, 'A', 'X');
insert into envoi (col1, code, chk) values (3, 'A', 'X');
insert into envoi (col1, code, chk) values (4, 'A', 'X');
insert into envoi (col1, code, chk) values (5, 'A', 'X');
insert into envoi (col1, code, chk) values (6, 'B', 'X');
insert into envoi (col1, code, chk) values (7, 'B', 'X');
insert into envoi (col1, code, chk) values (8, 'B', 'X');
insert into envoi (col1, code, chk) values (9, 'C', 'X');
 
commit;

D'après ce que j'ai lu ici (probablement un lien posté par mnitu, comme souvent

), le plus rapide sans parallélisation pour exporter des données dans un fichier plat sont de passer par un CLOB et de ne l'écrire qu'une fois.

Dans le code PL/SQL qui va suivre, ce qui me déplaît c'est que j'ai créé un premier curseur pour parcourir les éléments à envoyer, et à partir des données de celui-ci je vais chercher le bout de XML qui me convient.
Avec ma volumétrie, effectuer la jointure à ce niveau en pur SQL est malheureusement trop coûteux, j'annule le teste après plus de trente minutes sans rien voir arriver.

Dans ce code j'ai simulé la limitation à trois objets par fichier :

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
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2(30);
    v_nb_rows               NUMBER(10) DEFAULT 0;
    v_file_name             VARCHAR2(50);
    v_file_dir     CONSTANT VARCHAR2( 3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
 
begin
 
    for c_query in
    (
    select col1, code,
           lag(code, 1, ' ') over(order by code asc) as code_prec
      from envoi
     where chk = 'X'
    )
    loop
 
      if (c_query.code <> c_query.code_prec or v_nb_rows = 3)
      then -- Nouveau fichier 
 
        if c_query.code_prec <> ' '
        then -- Écriture du fichier précédent
          v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
          -- Rajout du footer
          DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
          DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
          DBMS_LOB.FREETEMPORARY(v_file);
        end if;
 
        -- Initialisation du CLOB 
        DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
        v_nb_rows      := 0;
        v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
        v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
        v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
        -- Rajout du header
        DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      end if;
 
      -- Sélection du XML dans la vue 
      select lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) into v_clob
        from v_donnees_xml x
       where x.col1 = c_query.col1;
 
      -- Concaténation du XML dans le CLOB
      DBMS_LOB.APPEND(v_file, v_clob);
 
      v_nb_rows := v_nb_rows + 1;
 
    end loop;
 
    -- Écriture du dernier fichier
    v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
    -- Rajout du footer
    DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
    DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
    DBMS_LOB.FREETEMPORARY(v_file);
 
end;
/

Évidemment sur ce genre de jeu de test, tout est instantané.

Voici le contenu d'un fichier, qui est bien celui escompté :

Code :

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

1
2
3
4
5
6
7
<MonFichier>
    <MonCode>A</MonCode>
    <DateEnvoi>20110329_151939417000000</DateEnvoi>
        <SuperBalise>JKL</SuperBalise>
        <SuperBalise>MNO</SuperBalise>
    <NbItem>2</NbItem>
</MonFichier>

Sur mon environnement de développement, j'écris environ 1Go de données en quinze minutes.
Ce n'est pas tellement l'écriture qui est lente, c'est vraiment d'aller chercher les bouts de XML un par un.

C'est sur ce point qu'à mon avis le code peut être améliorer, je suis quasiment sûr que ce temps de traitement est divisible par deux ou trois.
J'ai imaginé stocker tous mes identifiants dans une collection et la passer d'un coup à la vue, avec du XMLAgg afin de ne faire plus qu'un seul appel à ce niveau, mais je n'ai pas réussi à l'implémenter.

Ayant relativement peu de pratique en pur PL/SQL, toutes expériences et idées sont les bienvenues !

Merci d'être parvenu au terme de ce long sujet.

PS : Pour nettoyer votre environnement :

Code :

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

1
2
3
4
5
6
7
drop view v_donnees_xml;
 
drop table envoi;
 
drop table donnees;
 
drop directory TST;

**mnitu** · 29/03/2011, 15h53

Salut Waldar,
Oui travailler ligne par ligne n’est pas bon pour les performances.
Si j’ai bien compris ton problème il te faut grouper les donnés des le départ et parcourir les données dans un seul passage.

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Select code, XmlAgg(col_XML)
  2    From (
  3          Select t.*, trunc(t.rn/4) grp
  4          From (
  5            SELECT e.code, x.col_xml, row_number() over (partition by code order by rownum)rn
  6              FROM v_donnees_xml x
  7                   Join
  8                   envoi e
  9                On e.col1 = x.col1
 10            Where  e.chk = 'X'
 11          ) t
 12  )
 13  Group By code, grp
 14  /
 
CODE XMLAGG(COL_XML)
---- --------------------------------------------------------------------------------
A    <SuperBalise>ABC</SuperBalise><SuperBalise>DEF</SuperBalise><SuperBalise>GHI</Su
A    <SuperBalise>JKL</SuperBalise><SuperBalise>MNO</SuperBalise>
B    <SuperBalise>PQR</SuperBalise><SuperBalise>STU</SuperBalise><SuperBalise>VWX</Su
C    <SuperBalise>YZ</SuperBalise>
 
SQL>

PS. Ce n’était pas moi. J’ai simplement participé à la discussion pour signaler que l’utilisation du Clob n’est pas meilleure que l’utilisation correcte de l’UTL_FILE

**Waldar** · 29/03/2011, 16h29

Tout simplement le trunc du row_number(), je n'y ai pas pensé.

Merci mnitu, je reviens avec le différentiel de temps de traitement.

**Waldar** · 29/03/2011, 17h27

Aïe, même si l'idée est excellente, elle a mis le serveur sur les rotules.
Mon temps de référence est de treize minutes et après une demie-heure je n'ai toujours rien qui vient.

Les CPU sont à 100%, je pense que le XMLAgg sur toutes les lignes est trop violent.

Je vais regarder si je peux implémenter une solution intermédiaire, peut-être essayer de passer les id 100 par 100, mais là je retombe dans ma connaissance trop faible des collections.

Ou alors si une autre idée surgit !

**mnitu** · 29/03/2011, 20h55

C'est le fait du chercher ligne à ligne qui prends le plus du temps ou la concaténation des lobs ? C'est dans ce cas que le profil du traitement devrait indiquer dans où le temps passe et par conséquence comment il faut intervenir.
Quelle est la volumétrie de la table ?

**skuatamad** · 30/03/2011, 02h55

Salut Waldar,

Alors effectivement nous ne savons pas encore lequel, du select dans la boucle ou de la concaténation des lobs, est le plus lent, mais au cas où le select dans la boucle soit en cause je propose une autre approche (mais franchement je ne sais pas ce que ça vaut, et vu que vous êtes tous les deux plus compétent que moi... j'ai de gros doutes...)

Mon idée est, plutôt que de générer du XML en SQL et découper les fichiers en PL/SQL, pourquoi ne pas faire l'inverse... découper en SQL et générer le XML dans le code PL/SQL (à la manière du header et du footer déjà codés), au moins il n'y a plus de select dans une boucle.

J'ai utilisé une variable globale pour "simuler" le paramètre v_nb_rows tel qu'il était utilisé dans le découpage, mais une modification assez simple te l'épargnera si tu préfères.

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
create or replace package param as
  g_nb_rows NUMBER(10) DEFAULT 3;
  function getNbRows return number;
end;
/
create or replace package body param as
  function getNbRows return number is
    begin
      return g_nb_rows;
    end;
end;
/
create or replace view v_donnees_per_file as
with rows_per_files as (
  select param.getNbRows as nb_rows from dual
)
  select t.code,t.col2,
         mod(t.nb_rows - 1,rpf.nb_rows) as new_file
    from (select e.code,d.col2,
                 count(*) over (partition by e.code order by d.col2) as nb_rows
            from envoi e
            join donnees d on e.col1 = d.col1
           where e.chk = 'X'
         ) t
   cross join rows_per_files rpf
/

et la procédure (très proche de la tienne):

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
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2(30);
    v_nb_rows               NUMBER(10) DEFAULT 0;
    v_file_name             VARCHAR2(50);
    v_file_dir     CONSTANT VARCHAR2( 3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
    v_first_row             VARCHAR2(1) DEFAULT 'T';
 
begin
 
    FOR c_query IN
    (
    select code,col2,new_file from v_donnees_per_file
    )
    loop
 
      IF (c_query.new_file = 0)
      then -- Nouveau fichier 
 
        IF v_first_row <> 'T'
        then -- Écriture du fichier précédent
          v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
          -- Rajout du footer
          DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
          DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
          DBMS_LOB.FREETEMPORARY(v_file);
        end IF;
        v_first_row := 'F';    
        -- Initialisation du CLOB 
        DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
        v_nb_rows      := 0;
        v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
        v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
        v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
        -- Rajout du header
        DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      end IF;
 
      -- Sélection du XML dans la vue 
      -- Plus utile maintenant
      -- SELECT lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) INTO v_clob
      --  FROM v_donnees_xml x
      -- WHERE x.col1 = c_query.col1;
 
      v_clob := lpad(' ', 8, ' ') || '<SuperBalise>'||c_query.col2||'</SuperBalise>' || chr(13) || chr(10);
 
      -- Concaténation du XML dans le CLOB
      DBMS_LOB.APPEND(v_file, v_clob);
 
      v_nb_rows := v_nb_rows + 1;
 
    end loop;
 
    -- Écriture du dernier fichier
    v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
    -- Rajout du footer
    DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
    DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
    DBMS_LOB.FREETEMPORARY(v_file);
 
end;
/

Mais je ne sais pas dans quelle mesure il sera simple d'adapter mon approche à tes données, notamment quand tu dis :

j'ai codé ce qu'il faut dans une vue en passant par des objets créé pour le besoin

Amélioration d'un process d'écriture

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager