Vous n'êtes pas identifié(e).
Pages : 1
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.
Hors ligne
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.
Hors ligne
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
Hors ligne
Non, je n'ai pas dit ça. Mieux vaut une grosse REQUETE qu'une petite fonction, là, oui.
Guillaume.
Hors ligne
OUI
en fait c ce que je voulais dire ...... mais pas comme il le fallait
merci encore
Hors ligne
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....... ;-)
Hors ligne
Ouch, impressionnant. Bien joué !
Guillaume.
Hors ligne
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 * * * * *
Hors ligne
Pages : 1
