Re: Performance sur des fonctions plpgsql

Bonjour Guillaume,

>philippe(dot)beaudoin(at)bull(dot)net a écrit :
>> [...]
>> Une fois la mécanique mise au point, j'ai sorti mon chronomètre et
>> j'obtiens des temps d'insertion entre 25 et 40 fois plus longs qu'avec
les
> inserts simples !!!
>> Sur le serveur Linux, top montre des process PostgreSQL entre 96 et 100%
de
>> cpu, (au lieu des 27% pour les INSERT). Visiblement mes fonctions sont
très
>> cpu-vores ! Pourtant, d'après la documentation, j'ai compris qu'à la
>> première exécution, un plan est créé pour la fonction, permettant
ensuite
>> des exécutions performantes.
>>
>> Je me demande donc si :
>> ? soit la technologie plpgsql est intrinsèquement coûteuse en cpu,
>
>Oui, surtout de le façon dont vous l'utilisez. Autrement dit, les substr
>et les types numeric coûtent très chers.
>
>Une lecture qui pourrait vous intéresser :
> http://www.postgresqlfr.org/?q=node/1378
>
>Regardez notamment le graphique de comparaison de temps de réponse.

Le graphique est effectivement très parlant !
Pour manipuler mon buffer, plpgsql n'est donc pas la bonne solution.

>
>> ? soit j'ai raté quelque chose dans l'utilisation que j'en fait.
>>
>> Voici un exemple pour une table créée par :
>>
>> CREATE TABLE CDAC0 (
>> NDOMAT NUMERIC(7) NOT NULL,
>> CDNER001 BYTEA NOT NULL,
>> DIMPCO NUMERIC(9) NOT NULL,
>> AC0RT0 BYTEA NOT NULL,
>> HCOMME NUMERIC(7) NOT NULL,
>> NETIMA NUMERIC(1) NOT NULL,
>> NAGCAF CHARACTER(5) NOT NULL,
>> LICOM1 BYTEA NOT NULL,
>> LICOM2 BYTEA NOT NULL,
>> LICOM3 BYTEA NOT NULL,
>> CPRICO CHARACTER(1) NOT NULL)
>> TABLESPACE TSPD
>> ;
>>
>
>Pour informations, le type NUMERIC est responsable de lenteur.

OK.
Mais il a l'avantage de bien coller au format de données Cobol numérique
étendu. C'est en outre le format utilisé sur les bases actuelles (le DDL
restant inchangé sur cet aspect). Mais nous allons reconsidérer le sujet à
la lumière de ta remarque.

>Le type
>character a pour principal défaut de padder avec des espaces en début de
>chaîne...

Le padding ne serait'il pas plutôt en fin de chaine ?

>difficile ensuite de faire des comparaisons sans supprimer les
>espaces.

D'accord avec des langages comme le C où les chaines sont intrinsèquement
de longueur variable (\0 marquant la fin de la chaine).
Mais en cobol, une chaine est intrinsèquement de longueur fixe. Donc si par
exemple une host-variable est déclarée alphanumérique de longueur n - PIC
X(n) -, une comparaison du type WHERE col = :HV où la colonne col est aussi
déclarée CHAR (n) fonctionnera sans problème. Si la colonne est de type
TEXT, alors il faut utiliser une structure de host-variable avec une partie
longueur et une partie contenu, en calculant la longueur effective (sans
blanc à droite) de la variable (ou bien peut-être coder une condition du
type WHERE col = RTRIM (:HV)). Bref, c'est moins simple.

>
>> et la fonction (qui reçoit en entrée le nombre de lignes à insérer et le
>> buffer contenant les données et qui retourne en sortie le nombre de
lignes
>> réellement insérées) :
>>
>> CREATE OR REPLACE FUNCTION INSCDAC0
>> (NBLIG SMALLINT, ENTREE CHARACTER(32000))
>
>À mon avis, vous gagneriez à remplacer character(32000) (qui doit
>prendre une place monstrueuse en mémoire) par un bête champ text.
>
>> RETURNS INTEGER AS $$
>> DECLARE
>> OFS INTEGER;
>> RET INTEGER;
>> LG INTEGER;
>> HV_NDOMAT NUMERIC(7) ;
>> HV_CDNER001 BYTEA ;
>> HV_DIMPCO NUMERIC(9) ;
>> HV_AC0RT0 BYTEA ;
>> HV_HCOMME NUMERIC(7) ;
>> HV_NETIMA NUMERIC(1) ;
>> HV_NAGCAF TEXT ;
>> HV_LICOM1 BYTEA ;
>> HV_LICOM2 BYTEA ;
>> HV_LICOM3 BYTEA ;
>> HV_CPRICO TEXT ;
>> BEGIN
>> OFS=1; RET=0;
>> FOR I IN 1 .. NBLIG LOOP
>
>À mon avis, vous perdez du temps d'ici :
>
>> HV_NDOMAT = TO_NUMBER(SUBSTR(ENTREE,OFS,8),'S9999999');
>> OFS=OFS + 8;
>> LG = TO_NUMBER(SUBSTR(ENTREE,OFS,5),'99999');
>> OFS=OFS + 5;
>> HV_CDNER001 = SUBSTR(ENTREE,OFS,LG);
>> OFS=OFS + LG;
>> HV_DIMPCO = TO_NUMBER(SUBSTR(ENTREE,OFS,10),'S999999999');
>> OFS=OFS + 10;
>> LG = TO_NUMBER(SUBSTR(ENTREE,OFS,5),'99999');
>> OFS=OFS + 5;
>> HV_AC0RT0 = SUBSTR(ENTREE,OFS,LG);
>> OFS=OFS + LG;
>> HV_HCOMME = TO_NUMBER(SUBSTR(ENTREE,OFS,8),'S9999999');
>> OFS=OFS + 8;
>> HV_NETIMA = TO_NUMBER(SUBSTR(ENTREE,OFS,2),'S9');
>> OFS=OFS + 2;
>> HV_NAGCAF = SUBSTR(ENTREE,OFS,5);
>> OFS=OFS + 5;
>> LG = TO_NUMBER(SUBSTR(ENTREE,OFS,5),'99999');
>> OFS=OFS + 5;
>> HV_LICOM1 = SUBSTR(ENTREE,OFS,LG);
>> OFS=OFS + LG;
>> LG = TO_NUMBER(SUBSTR(ENTREE,OFS,5),'99999');
>> OFS=OFS + 5;
>> HV_LICOM2 = SUBSTR(ENTREE,OFS,LG);
>> OFS=OFS + LG;
>> LG = TO_NUMBER(SUBSTR(ENTREE,OFS,5),'99999');
>> OFS=OFS + 5;
>> HV_LICOM3 = SUBSTR(ENTREE,OFS,LG);
>> OFS=OFS + LG;
>> HV_CPRICO = SUBSTR(ENTREE,OFS,1);
>> OFS=OFS + 1;
>
>à ici.
>
>Sans la partie ci-dessus, j'obtiens une exécution en 55 ms pour insérer
>1000 lignes, soit 0,05 ms par ligne.
>
>> INSERT INTO CDAC0 VALUES (
>> HV_NDOMAT,
>> HV_CDNER001,
>> HV_DIMPCO,
>> HV_AC0RT0,
>> HV_HCOMME,
>> HV_NETIMA,
>> HV_NAGCAF,
>> HV_LICOM1,
>> HV_LICOM2,
<> HV_LICOM3,
>> HV_CPRICO
>> );
>> RET=RET+1;
>> END LOOP;
>> RETURN RET;
>> END;
>> $$ LANGUAGE PLPGSQL;
>>
>> La fonction est créée en début de programme puis est supprimée à la fin
du
>> programme.
>>
>> Toute info pour me permettre de comprendre sera la bienvenue...
>>
>
>À mon avis, la meilleure solution revient à recoder la fonction en
>PL/perl, voire en C.
>
>Sinon, l'autre possibilité est de voir pourquoi vous aviez des lenteurs
>en ne passant pas par la fonction.
La lenteur est intrinsèquement liée à la communication inter-serveur. Les
temps obtenus sont conformes à ce qui est attendu par cette technologie. Je
cherche juste à aller plus vite compte tenu des volumes qu'il y aurait à
charger.

>La fonction est intéressante car elle
>fait l'insertion en une seule transaction. Utilisiez-vous bien une
>transaction pour englober vos transactions ?
Oui. Les insertions dans chaque table sont gérées par une unique
transaction.

>Vous pouvez soit englober
>un gros ensemble d'INSERT dans une transaction, soit utiliser COPY, soit
>utiliser l'insertion multilignes comme proposer par Christophe.
Je vais creuser l'idée de l'insertion multiligne.
Mais je vais regarder aussi de plus près la réalisation de procédure en C.
Cela pourrait être utile pour d'autres types d'optimisation dans le futur.
J'ai vu qu'il y a un chapitre asseez long dans la documentation. Un peu de
lecture studieuse à venir...

Merci à tous pour l'aide et la réactivité.
Philippe.