Forums PostgreSQL.fr

gilou974

Membre

Fonctions imbriquées -- optimisation

Bonjour à tous et meilleurs voeux pour 2011 ......

Voilà, je dois réaliser des écritures d'à nouveaux et pour ce faire je dispose de quatres fonction comme suit

1) Celle en entrée qui donne tous les societaires consernés :

CREATE OR REPLACE FUNCTION parcours_plancomptable(annee integer, idutilisateur integer)
  RETURNS integer AS
$BODY$

DECLARE
    nbLigne integer;                -- nombre de lignes crées pour un plan comptable
    nbLigneTotal integer :=0;            -- nombre de lignes totales crées pour l'execution du script
    soctaire RECORD;                -- Ligne contenant un societaire
    idNaturePiece integer := 9;            -- id nature piece : A nouveau
BEGIN
     -- Création pîece comptable --
    INSERT INTO piece_comptable(id_pct, id_npc, bl_valide_pct)
        VALUES (nextval('piece_comptable_id_seq'), idNaturePiece, false);
       
    FOR soctaire IN (
        SELECT distinct soc.id_plc FROM societaire soc
            JOIN ligne_ecriture le ON soc.id_plc = le.id_plc
            WHERE st_etat_soc IN ('SP', 'DO', 'EX') AND nm_soc_soc <10000
            AND EXTRACT(YEAR FROM le.d_dateecriture_lec) = annee - 1 AND id_grp not between 36 and 51
            ORDER BY soc.id_plc ASC)
    LOOP
        EXECUTE 'SELECT parcours_groupe(' || soctaire.id_plc || ', currval(''piece_comptable_id_seq''), ' || annee || ', ' || idUtilisateur || ')' INTO nbLigne;
        nbLigneTotal := nbLigneTotal + nbLigne;
    END LOOP;
    RETURN nbLigneTotal;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION parcours_plancomptable(integer, integer) OWNER TO postgres;

2 ) une pour les groupes comptables

CREATE OR REPLACE FUNCTION parcours_groupe(idplancomptable integer, idpiececomptable bigint, annee integer, idutilisateur integer)
  RETURNS integer AS
$BODY$
DECLARE
    solde numeric(18,2) := 0.00;         -- variable récupéré de la fonction solde_groupe()
    grp RECORD;                -- Ligne contenant un groupe pour tout les groupes sauf (7,8,9,10,11,12)
    ligneEcriture RECORD;            -- Ligne contenant un groupe uniquement pour les groupes (7,8,9,10,11,12)
    cotis RECORD;                -- Ligne contenant un groupe uniquement pour les groupes (22,23,24,25,26,27)
    txt text :='';                -- log
    dateEcriture date;            -- Date avec annee courante
    lbl text :='';                -- libelle de la ligne ecriture
    debit numeric(18,2) := 0.00;        -- Debit pour la ligne écriture
    credit numeric(18,2) := 0.00;        -- Debit pour la ligne ecriture
    nbLigne integer :=0;            -- Compte le nombre de ligne crée
    idJournal integer := 3;            -- Identifiant du journal : A NOUVEAU
    anneeN1 integer := 0;            -- Annee + 1
    anneeSolde integer := 0;        -- Annee - 1
    trancheGar integer := 0;        -- Tranche garantie
    idJournalCotis integer := 16;        -- Identifiant du journal : COTISATION EXERCICE
    lblCotisation text := '';        -- Libelle de la ligne écriture cotisation
    soldeCotis numeric(18,2) := 0.00;    -- Solde d'un des groupes COTIS_
    debitCotis numeric(18,2) := 0.00;    -- Debit d'un des groupes COTIS_
    creditCotis numeric(18,2) := 0.00;    -- Credit d'un des groupes COTIS_
    dateEcritureCotis date;            -- Date pour les lignes écriture des groupes COTIS_
    groupeCotis integer := 66;        -- Groupe COMPTABILITE GENERALE
    totalCotis numeric(18,2) := 0.00;    -- Total de tout les soldes groupe des cotisations
BEGIN   
    -- Déclaration des variables --
    anneeN1 = annee + 1;                            -- Annee + 1
    anneeSolde = annee - 1;                            -- Annee -1
    dateEcriture := to_date(annee || '-01-01', 'YYYY-MM-DD');         -- Date au 01/01/anneeCourante
    lbl := 'A NOUVEAU AU 1/1/' || annee;                    -- Creation libelle
    lblCotisation := 'COTISATION EXERCICE ' || anneeSolde;            -- Création libelle cotisation
    dateEcritureCotis := to_date(anneeSolde || '-12-31', 'YYYY-MM-DD');    -- Date au 31/12/anneeCourante -1

    -- Génération des lignes écritures --
    --execute la fonction solde_groupe pour tous les groupes hormis ceux de fdg, de HB et de cotis ainsi que les fonds restituables
    FOR grp IN (SELECT g.id_grp
        FROM groupe g
        WHERE g.id_grp NOT IN (7,8,9,10,11,12,13,22,23,24,25,26,27)     -- 7 => FDG_S - 8 => FDG_T - 9 => FDG_G - 10 => FDG_P - 11 => FDG_M - 12 => FDG_NP -
        AND g.id_grp NOT BETWEEN 36 AND 51 )                                -- 13 => FondsRest - 22 => COTIS_S - 23 => COTIS_T - 24 => COTIS_G - 25 => COTIS_P -
                                        -- 26 => COTIS_M - 27 => COTIS_EX_NC
    LOOP
        EXECUTE 'SELECT solde_groupe(' || grp.id_grp || ', ' || anneeSolde || ', ' || idPlanComptable || ')' INTO solde;
       
        IF (solde<>0 OR solde <> NULL) THEN                    -- Si solde != 0 alors injecte une ligne
           
            IF (solde > 0) THEN
                debit := solde;
                credit := 0.00;
            ELSE
                debit := 0.00;
                credit := ABS(solde);
            END IF;
           
            INSERT INTO ligne_ecriture(
                id_lec, id_pct, id_grp, id_plc, id_jnx, id_bqe, d_dateecriture_lec,
                st_lbl_lec, nm_debit_lec, nm_credit_lec, d_datesaisie_lec, nm_tranchegar_lec,
                st_anneegar_lec, nm_numremise_lec, nm_numcheque_lec, id_usr,
                bl_transfere_lec)
            VALUES (nextval('ligne_ecriture_id_seq'), idPieceComptable, grp.id_grp, idPlanComptable, idJournal, null, dateEcriture,
                lbl, debit, credit, NOW(), 0,
                annee, null, null, idUtilisateur,
                false);
               
            nbLigne    := nbLigne + 1;
            --RAISE NOTICE 'Log pour le groupe %  avec le plan comptable % (solde %) ', grp.id_grp, idPlanComptable, solde;
        END IF;
    END LOOP;
   
    -- Pour chaque ligne ecriture dont l'année correspond à l'année courante et au groupe de Fonds de Garantie
   
    FOR ligneEcriture IN (SELECT le.id_grp, le.nm_debit_lec, le.nm_credit_lec, le.nm_tranchegar_lec, st_anneegar_lec FROM ligne_ecriture le
        WHERE (EXTRACT(YEAR FROM le.d_dateecriture_lec)) = '2010'
        AND le.id_plc = idPlanComptable
        AND le.id_grp IN (7,8,9,10,11,12)) -- 7 => FDG_S - 8 => FDG_T - 9 => FDG_G - 10 => FDG_P - 11 => FDG_M - 12 => FDG_NP
    LOOP
    EXECUTE 'SELECT solde_groupe(' || grp.id_grp || ', ' || anneeSolde || ', ' || idPlanComptable || ')' INTO solde;
        IF (solde<>0 OR solde <> NULL) THEN
            IF (ligneEcriture.nm_tranchegar_lec <> null) THEN
                trancheGar := ligneEcriture.nm_tranchegar_lec;
            ELSE
                trancheGar := 0;
            END IF;
           
            INSERT INTO ligne_ecriture(
                id_lec, id_pct, id_grp, id_plc, id_jnx, id_bqe, d_dateecriture_lec,
                st_lbl_lec, nm_debit_lec, nm_credit_lec, d_datesaisie_lec, nm_tranchegar_lec,
                st_anneegar_lec, nm_numremise_lec, nm_numcheque_lec, id_usr,
                bl_transfere_lec)
            VALUES (nextval('ligne_ecriture_id_seq'), idPieceComptable, ligneEcriture.id_grp, idPlanComptable, idJournal, null, dateEcriture,
                lbl, ligneEcriture.nm_debit_lec, ligneEcriture.nm_credit_lec, NOW(), trancheGar,
                ligneEcriture.st_anneegar_lec, null, null, idUtilisateur,
                false);
               
            nbLigne    := nbLigne + 1;
            --RAISE NOTICE 'Log pour le groupe %  avec le plan comptable % (solde %): %', grp.id_grp, idPlanComptable, solde, txt;
        END IF;
    END LOOP;

    -- On calcul le solde total pour les groupes cotisation. Si le solde est négatif erreur
    EXECUTE 'SELECT solde_groupeCotis(' || idPlanComptable || ', '|| annee ||')' INTO totalCotis;
    IF (totalCotis > 0) THEN
        RAISE EXCEPTION 'ERREUR la somme des soldes groupes des cotisations est négatif';
        RETURN nbLigne;
    END IF;
   
    -- Pour les groupes de cotisation : COTIS_S, COTIS_T, COTIS_G, COTIS_P, COTIS_M, COTIS_EX_NC
    FOR cotis IN (SELECT le.id_grp, le.nm_debit_lec, le.nm_credit_lec, le.nm_tranchegar_lec, st_anneegar_lec FROM ligne_ecriture le
        WHERE (EXTRACT(YEAR FROM le.d_dateecriture_lec)) = annee
        AND le.id_plc = idPlanComptable
        AND le.id_grp IN (22,23,24,25,26,27))         -- 22 => COTIS_S - 23 => COTIS_T - 24 => COTIS_G - 25 => COTIS_P - 26 => COTIS_M - 27 => COTIS_EX_NC
    LOOP
        EXECUTE 'SELECT solde_groupe(' || cotis.id_grp || ', ' || anneeSolde || ', ' || idPlanComptable || ')' INTO soldeCotis;

        IF (soldeCotis < 0) THEN
            debitCotis := soldeCotis;
            creditCotis := 0.00;
        ELSE
            debitCotis := 0.00;
            creditCotis := ABS(soldeCotis);
        END IF;
       
        INSERT INTO ligne_ecriture(
            id_lec, id_pct, id_grp, id_plc, id_jnx, id_bqe, d_dateecriture_lec,
            st_lbl_lec, nm_debit_lec, nm_credit_lec, d_datesaisie_lec, nm_tranchegar_lec,
            st_anneegar_lec, nm_numremise_lec, nm_numcheque_lec, id_usr,
            bl_transfere_lec)
        VALUES (nextval('ligne_ecriture_id_seq'), idPieceComptable, cotis.id_grp, idPlanComptable, idJournalCotis, null, dateEcritureCotis,
            lblCotisation, debitCotis, creditCotis, NOW(), 0,
            anneeSolde, null, null, idUtilisateur,
            false);
           
        nbLigne    := nbLigne + 1;
       
        -- Contre partie   
        INSERT INTO ligne_ecriture(
            id_lec, id_pct, id_grp, id_plc, id_jnx, id_bqe, d_dateecriture_lec,
            st_lbl_lec, nm_debit_lec, nm_credit_lec, d_datesaisie_lec, nm_tranchegar_lec,
            st_anneegar_lec, nm_numremise_lec, nm_numcheque_lec, id_usr,
            bl_transfere_lec)
        VALUES (nextval('ligne_ecriture_id_seq'), idPieceComptable, groupeCotis, idPlanComptable, idJournalCotis, null, dateEcritureCotis,
            lblCotisation, 0.00, totalCotis, NOW(), 0,
            anneeSolde, null, null, idUtilisateur,
            false);
           
        nbLigne    := nbLigne + 1;
    END LOOP;
    RETURN nbLigne;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION parcours_groupe(integer, bigint, integer, integer) OWNER TO postgres;

3 ) une autre pour le solde des groupes permettant de ne générer des ecritures que pour les groupes avec un solde non null et différent de 0

CREATE OR REPLACE FUNCTION solde_groupe(idgroupe integer, annee integer, idplancomptable integer)
  RETURNS numeric AS
$BODY$

DECLARE
    solde numeric(18,2) := 0.00;
    ecriture RECORD;
BEGIN
    SELECT sum(le.nm_debit_lec) as sDebit, sum(le.nm_credit_lec) as sCredit
        INTO ecriture FROM ligne_ecriture le
        WHERE le.id_grp = idGroupe
        AND le.id_plc = idPlanComptable
        AND EXTRACT(YEAR FROM le.d_dateecriture_lec) = annee;
    solde = ecriture.sDebit -ecriture.sCredit;                -- Calcule SOMME(credit) - SOMME(debit)
    --RAISE NOTICE 'solde = % ', solde;
    IF (solde IS NOT NULL) THEN
        RETURN solde;
    ELSE
        RETURN 0.00;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION solde_groupe(integer, integer, integer) OWNER TO postgres;

4) une dernière spécifique à une catégorie de groupe comptable

CREATE OR REPLACE FUNCTION solde_groupecotis(idplancomptable integer, annee integer)
  RETURNS numeric AS
$BODY$

DECLARE
    soldeGroupe numeric(18,2) := 0.00;             -- Solde du groupe
    soldeTotal numeric(18,2) := 0.00;            -- Solde total de tout les groupes
    anneeSolde integer := 0;                -- Annee - 1
    cotis RECORD;
BEGIN
    anneeSolde := annee - 1;
    FOR cotis IN (SELECT le.id_grp, le.nm_debit_lec, le.nm_credit_lec, le.nm_tranchegar_lec, st_anneegar_lec FROM ligne_ecriture le
        WHERE (EXTRACT(YEAR FROM le.d_dateecriture_lec)) = anneeSolde
        AND le.id_plc = idPlanComptable
        AND le.id_grp IN (22,23,24,25,26,27))         -- 22 => COTIS_S - 23 => COTIS_T - 24 => COTIS_G - 25 => COTIS_P - 26 => COTIS_M - 27 => COTIS_EX_NC
    LOOP
        EXECUTE 'SELECT solde_groupe(' || cotis.id_grp || ', ' || anneeSolde || ', ' || idPlanComptable || ')' INTO soldeGroupe;
        soldeTotal := soldeTotal + soldeGroupe;
    END LOOP;
    RETURN soldeTotal;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION solde_groupecotis(integer, integer) OWNER TO postgres;

VOilà. Mon problème, vous aurez compris vu le sujet serait de pouvoir optimiser le traitement.

Pour 7770 societaire concernés et 339494 lignes dans ma table d'écriture, mon traitement n'est toujours pas terminé après plus de trois heures.

Pourriez - vous me donner des astuces notement sur le fait d'apeler les fonctions à l'intérieur d'autres fonctions et ce que çà coute en tps et me dire si le mieux ne serait pas de faire tout le traitement dans une seule fonction.

Merci bcp à tous et encore plus à ceux qui pourraient m'aider.

gleu

Administrateur

Re : Fonctions imbriquées -- optimisation

Vu qu'il s'agit simplement de calcul, le mieux serait d'écrire la procédure en C. C'est là que vous aurez les meilleures performances.

Quant à améliorer les procédures stockées, oui, c'est possible. Prenez la dernière par exemple. Vous pouvez la faire en une seule instruction SQL, ce qui devrait être bien plus rapide.

SELECT sum(solde_groupe(le.id_grp, annee, idPlanComptable))
FROM ligne_ecriture le
WHERE (EXTRACT(YEAR FROM le.d_dateecriture_lec)) = annee -1
  AND le.id_plc = idPlanComptable
  AND le.id_grp IN (22,23,24,25,26,27)

Du coup, cela devient une procédure SQL, ce qui permet encore de grapiller un peu.

Bref, évitez de récupérer des colonnes que vous n'utilisez pas, évitez les boucles inutiles, etc. Utilisez des procédures SQL plutôt que PL/pgsql lorsque c'est possible.

---

Guillaume.

gilou974

Membre

Re : Fonctions imbriquées -- optimisation

Tout d'abord merci à vous,

on est donc d'accord mieux vaut une procédure très longue que plusieures petites fonctions dès lors que l'on peut tout réaliser en sql...

Merci encore

gleu

Administrateur

Re : Fonctions imbriquées -- optimisation

Non, je n'ai pas dit ça. Mieux vaut une grosse REQUETE qu'une petite fonction, là, oui.

---

Guillaume.

gilou974

Membre

Re : Fonctions imbriquées -- optimisation

OUI

en fait c ce que je voulais dire ...... mais pas comme il le fallait

merci encore

gilou974

Membre

Re : Fonctions imbriquées -- optimisation

Merci encore

juste pour dire, en suivant les conseils et en mettant tout dans une seule et grosse requete le tps d'exécution tombe, que dis-je : CHUTE à 14 secondes....... ;-)

gleu

Administrateur

Re : Fonctions imbriquées -- optimisation

Ouch, impressionnant. Bien joué !

---

Guillaume.

SQLpro

Membre

Re : Fonctions imbriquées -- optimisation

Merci encore

juste pour dire, en suivant les conseils et en mettant tout dans une seule et grosse requete le tps d'exécution tombe, que dis-je : CHUTE à 14 secondes....... ;-)

Normal; un traitement ensembliste (requêtes SQL)  est optimisable. C'est d'ailleurs le rôle de l'optimiseur !  Et tout bon SGBDR comme PostGreSQL en est doté !!!
Un traitement itératif ne l'est pas car le code est statique !

De plus en ajoutant les bons index, vous arriverez certainement à diminuer encore les temps de réponse !

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *