Re: Performance sur des fonctions plpgsql

Bonsoir,

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.

> ? 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. Le type
character a pour principal défaut de padder avec des espaces en début de
chaîne... difficile ensuite de faire des comparaisons sans supprimer les
espaces.

> 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 fonction est intéressante car elle
fait l'insertion en une seule transaction. Utilisiez-vous bien une
transaction pour englober vos transactions ? Vous pouvez soit englober
un gros ensemble d'INSERT dans une transaction, soit utiliser COPY, soit
utiliser l'insertion multilignes comme proposer par Christophe.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com