Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Mon 15 October 2018 10:59

tevrard
Participant assidu
Date d'inscription: 23 May 2016
Messages: 319

Optimisation trigger d'insertion

Bonjour,

Je débute dans l'utilisation des triggers, et celui-ci prends beaucoup trop de temps.

L'objectif du trigger est de mettre à jour une table à chaque fois qu'une insertion de données est réalisée dans une table "source"
Le trigger est paramétré depuis pgadmin, il se déclenche "after insert" sur la table bdd_territoires.data_insee_menages_source
Voici le code de la fonction trigger (je vous épargne la liste des champs):

Code:

DECLARE
BEGIN

TRUNCATE bdd_territoires.data_insee_menages;

INSERT INTO bdd_territoires.data_insee_menages (
    [champs de la table cible])

SELECT [champs de la table source à importer]
FROM bdd_territoires.data_insee_menages_source;

RETURN NULL;
END;

J'ai fait un COPY pour insérer dans la table source, il a fallu 5h pour que la table data_insee_menages soit remplie (36000 lignes)


=> Un problème dans le code ?
=> Faut il chercher autre part pour expliquer ce temps? Si oui où?


Merci d'avances pour vos réponses !

Dernière modification par tevrard (Mon 15 October 2018 11:01)

Hors ligne

 

#2 Mon 15 October 2018 11:08

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Optimisation trigger d'insertion

Bonjour,

De ce que je comprends du code, a chaque insert dans la table bdd_territoires.data_insee_menages_source, vous videz la table bdd_territoires.data_insee_menages et vous réinsérer toutes les lignes (pas de condition where): ca fait beaucoup.

Votre triggger est défini FOR EACH ROW ou FOR EACH STATEMENT ?

Nicolas

Hors ligne

 

#3 Mon 15 October 2018 11:18

tevrard
Participant assidu
Date d'inscription: 23 May 2016
Messages: 319

Re: Optimisation trigger d'insertion

D'après ce que j'ai sous pgadmin, je dirais for each row car "chaque ligne?" est cochée

cf pj


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#4 Mon 15 October 2018 11:31

Pierre
DesCartesPourUnMondeMeilleur
Date d'inscription: 22 Sep 2005
Messages: 1643

Re: Optimisation trigger d'insertion

Aloha,

Je ne suis pas sûr de comprendre non plus le besoin.
Lorsqu'un utilisateur insère une donnée dans t_A, il faut que qqqs colonnes de t_A de ce/ces nouveaux enregistrements soient répliqués dans t_data_insee... ?

Si c'est cela, je pense qu'il faudrait passer par des déclarations de type :

Code:

EXECUTE 'INSERT INTO t_data_insee VALUE '||new.champ1||','|| new.champ2...';

Du moins c'est vers ce type de fonction que j'irai.
Après je déclarerai le trigger comme appelant cette fonction.

PS. Le truncate lancé à chaque exécution du trigger est une très mauvaise idée.

Dernière modification par Pierre (Mon 15 October 2018 11:33)


art X I. Déclaration des Droits de l’Homme et du Citoyen 1789
La libre communication des pensées et des opinions est un des droits les plus précieux de l’Homme : tout Citoyen peut donc parler, écrire, imprimer librement, sauf à répondre de l’abus de cette liberté, dans les cas déterminés par la Loi.

Hors ligne

 

#5 Mon 15 October 2018 11:54

tevrard
Participant assidu
Date d'inscription: 23 May 2016
Messages: 319

Re: Optimisation trigger d'insertion

Pierre a écrit:

Je ne suis pas sûr de comprendre non plus le besoin.
Lorsqu'un utilisateur insère une donnée dans t_A, il faut que qqqs colonnes de t_A de ce/ces nouveaux enregistrements soient répliqués dans t_data_insee... ?


Non. Le trigger intervient lors une mise à jour annuelle sur l'ensemble des lignes. De plus je ne souhaite pas conserver de données "historique" dans ces tables.

Cette table n'est jamais mise à jour "au fil de l'eau".


Peux-tu développer concernant le Truncate stp ?

Dernière modification par tevrard (Mon 15 October 2018 11:55)

Hors ligne

 

#6 Tue 16 October 2018 11:08

JP LLORENS
Participant assidu
Date d'inscription: 12 Nov 2008
Messages: 231

Re: Optimisation trigger d'insertion

Bonjour
Comme le précise N. Ribot, a chaque insertion vous déclenchez un truncate et un INSERT INTO bdd_territoires.data_insee_menages => soit pour 36000 lignes, 36000 truncate ... d'où un temps d'exécution de 5h et la remarque de Pierre  !
Peut-être auriez vous intérêt à faire une fonction avec le truncate et les insert, que vous lanceriez une fois par an après avoir mis à jour votre table source.
JP

Hors ligne

 

#7 Tue 16 October 2018 11:22

Pierre
DesCartesPourUnMondeMeilleur
Date d'inscription: 22 Sep 2005
Messages: 1643

Re: Optimisation trigger d'insertion

Aloha

Pour faire suite au message de JP

Quelque chose comme

Code:

CREATE OR REPLACE FUNCTION maj_annuelle() RETURNS SETOF character varying 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

DECLARE
   
BEGIN
TRUNCATE bdd_territoires.data_insee_menages;

INSERT INTO bdd_territoires.data_insee_menages (
    [champs de la table cible])

SELECT [champs de la table source à importer]
FROM bdd_territoires.data_insee_menages_source;

RETURN NULL;
END;
$BODY$;

Et une fois par an vous faites

Code:

select maj_annuelle()

Ou alors utilisez pgagent qui semble permettre de planifier l'exécution de fonction à une date spécifique.


art X I. Déclaration des Droits de l’Homme et du Citoyen 1789
La libre communication des pensées et des opinions est un des droits les plus précieux de l’Homme : tout Citoyen peut donc parler, écrire, imprimer librement, sauf à répondre de l’abus de cette liberté, dans les cas déterminés par la Loi.

Hors ligne

 

#8 Tue 16 October 2018 11:28

tevrard
Participant assidu
Date d'inscription: 23 May 2016
Messages: 319

Re: Optimisation trigger d'insertion

JP LLORENS a écrit:

Bonjour
Comme le précise N. Ribot, a chaque insertion vous déclenchez un truncate et un INSERT INTO bdd_territoires.data_insee_menages => soit pour 36000 lignes, 36000 truncate ... d'où un temps d'exécution de 5h et la remarque de Pierre  !


Oui, après avoir décoché l'option "Chaque ligne?" (For each row) le trigger s'exécute en 1.5 sec smile

JP LLORENS a écrit:

Peut-être auriez vous intérêt à faire une fonction avec le truncate et les insert, que vous lanceriez une fois par an après avoir mis à jour votre table source.


C'est la ou j'ai du mal à voir la différence entre ce que vous me proposez et mon trigger. Avec ce dernier, j'ai bien une fonction qui s'exécute après avoir une fois par an, après mise à jour ma table source. L'avantage du trigger, c'est que cela me fait une manip en moins non?
En quoi le fait de vider ma table dans ma fonction de trigger est gênant?

EDIT : Ayant vu le msg de Pierre, j'ai bien du mal à voir l'avantage de ne pas passer par un trigger. PgAgent me permet de lancer une fonction à une date précise, mais le trigger permet de lancer une fonction lors d'un évènement particulier... et c'est bien ce que je veux : après insertion dans ma table source.


En tout cas, merci à tous pour votre aide smile

Dernière modification par tevrard (Tue 16 October 2018 11:34)

Hors ligne

 

#9 Tue 16 October 2018 12:17

JP LLORENS
Participant assidu
Date d'inscription: 12 Nov 2008
Messages: 231

Re: Optimisation trigger d'insertion

En effet si le trigger ne se déclenche qu'à la 36000ème insertion en basculant sur le mode "For each statement" alors oui, il n'y a aucun intérêt à lancer une fonction à la mimine.
Je crois que je viens de comprendre la notion de "simplement pour chaque instruction SQL" décrite dans la doc postgres sur les trigger qui différencie eachrow / each statement => je suppose donc que le trigger ne se déclenchera qu'à la fin de votre insert dans la table source, et non à chaque ligne insérée (each row)

Hors ligne

 

#10 Tue 16 October 2018 14:21

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Optimisation trigger d'insertion

Bonjour,

Oui, une des caractéristiques importantes des triggers est le mode de déclenchement:
FOR EACH ROW ou FOR EACH STATEMENT.

On utilise FOR EACH ROW quand le trigger doit "jouer" avec la valeur de chaque record concerné par l'opération. Cas le plus fréquent.

FOR EACH STATEMENT effectivement indique au trigger qu'il ne s'execute qu'une fois le statement terminé (peu importe que ce statement concerne une ligne ou plusieurs).
On utilise souvent ce mode pour tracer les modifs sur une table, si on est juste intéressé par qui et quand la table est modifiée. Le trigger remplit alors une table d'historique disant "telle table a ete modifiée (update/delete/insert) par machin a telle date.

Nico

Hors ligne

 

#11 Tue 16 October 2018 15:55

JP LLORENS
Participant assidu
Date d'inscription: 12 Nov 2008
Messages: 231

Re: Optimisation trigger d'insertion

Merci beaucoup pour ce complément d'information
JP

Hors ligne

 

Pied de page des forums

Powered by FluxBB