Forums PostgreSQL.fr

bert2713d

Membre

Lenteur d’exécution d'un Update simple

Bonjour ,

Je débute sur Postgresql . Mon projet est le suivant. J'importe des fichier csv dans une table qui se nomme "eniram". Une fois importé , j'ai environ 29 000 lignes pour 558 colonnes. Il s'agit de relevé temporel de capteurs pression , températures , etc. Après j'utilise grafana pour faire de jolis dashboard.
Mon problème est que j'ai besoin de faire de petits calculs  entre les capteurs . Ex : conso = capteur1 - capteur 2. Donc j'ai voulu faire une moulinette en PL . ça marche. Mais c'est hyper long (des heures) et pourtant je travail en local sur mon PC. 
J'aurais voulu savoir si cela vient de mon code ou si l'update d'une table est de toute façon très long et qu'il faut mieux le faire en amont sur les différents CSV importé.

Voici mon code, j'ai ici limité la modification à l'insertion de deux colonnes(tag_559 & tag_560) . A partir de deux capteurs je fais juste deux petits calculs que j'UPDATE dans les nouvelles colonnes.
Au début je ne comprenait pas pourquoi les valeurs renvoyés des Select dans la boucle faisaient n'importe quoi , j'ai resolu le problème avec les "order by" (apparemment il garde pas le classement entre deux select).

ALTER TABLE eniram ADD COLUMN tag_559 NUMERIC (20,6);
ALTER TABLE eniram ADD COLUMN tag_560 NUMERIC (20,6);
SELECT addcolum();

CREATE OR REPLACE FUNCTION addcolum () RETURNS VOID
AS $$

DECLARE
   
    I                 INTEGER;
    compteur     NUMERIC(20,0);
    jour            TIMESTAMP;
    flowin         Numeric(20,6);
    flowout      Numeric(20,6);
    resultat     Numeric (20,6);
    charge       Numeric (20,6);
    resultat2     NUMERIC(20,6);

BEGIN

flowin = 0;
flowout=0;
resultat=0;
I=0;
compteur=0;

SELECT COUNT(*) INTO compteur  FROM eniram;

FOR I IN 1..compteur
    LOOP
   
SELECT tag_2 INTO jour FROM eniram ORDER BY tag_2 OFFSET I LIMIT 1;

SELECT tag_23 INTO flowin FROM eniram ORDER BY tag_2 OFFSET I LIMIT 1;

SELECT tag_30 INTO flowout FROM eniram ORDER BY tag_2 OFFSET I LIMIT 1;

SELECT tag_220 INTO charge FROM eniram ORDER BY tag_2 OFFSET I LIMIT 1;

resultat = flowin - flowout ;
if charge =0 then resultat2=0; else resultat2= resultat *10 / charge; end if;

UPDATE eniram SET tag_559 = resultat  WHERE tag_2 = jour ;
UPDATE eniram SET tag_560 = resultat2  WHERE tag_2 = jour ;

END LOOP;

END;
$$ language plpgsql ;

merci d'avance

et bonne soirée

Bertr2713d

rjuju

Administrateur

Re : Lenteur d’exécution d'un Update simple

Bonjour,

Ce n'est pas très surprenant.  En effet, effectuer ces opérations lignes par ligne est peut être plus simple à appréhender mais côté performance cela ne marche pas du tout.  Il vous faut réécrire le traitement non pas en procédure stockée / langage impératif mais avec du SQL, de façon ensembliste.

En gros, un simple "UPDATE ... SET ..." (cf https://www.postgresql.org/docs/current/sql-update.html ).  Vous aurez à priori besoin de CASE (https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE ) pour la gestion de votre "resultat2".

---

Julien.
https://rjuju.github.io/

bert2713d

Membre

Re : Lenteur d’exécution d'un Update simple

Donc j'ai fait pour ma première colonne juste un :

UPDATE eniram SET tag_559 = tag_23 - tag_30;

ça marche en 128ms....purée que je suis con, je dois être trop habitué au boucle en c et VB .... je vois pour le CASE....merci beaucoup !

bert2713d

Membre

Re : Lenteur d’exécution d'un Update simple

Donc juste pour finir la conversation je met ici le bon code :

UPDATE eniram SET tag_559 = tag_23 - tag_30;

UPDATE eniram SET tag_560=CASE WHEN tag_220=0 THEN 0
            ELSE  (tag_559 *1000) / ((tag_220/100)*11553)
           END;

Je suis passé de 6 heures à 2s .... merci encore

gleu

Administrateur

Re : Lenteur d’exécution d'un Update simple

Alors, autant c'est mieux, autant ça cause un autre problème. Votre premier UPDATE double la taille de la table, et le deuxième immédiatement après la triple. Tout ça parce qu'avec PostgreSQL, un UPDATE, c'est l'équivalent d'un DELETE suivi d'un INSERT. Vous gagneriez à faire les deux UPDATE en même temps, donc :

UPDATE eniram SET
    tag_559 = tag_23 - tag_30,
    tag_560=CASE WHEN tag_220=0 THEN 0 ELSE  ((tag_23 - tag_30) *1000) / ((tag_220/100)*11553) END;

Ça double toujours la taille de la table, mais au moins ça ne la triple pas. Et du coup, ça doit aussi être plus rapide.

---

Guillaume.

bert2713d

Membre

Re : Lenteur d’exécution d'un Update simple

compris , merci !
Pour une base quasi complète à 112 000 lignes ,  moi : + de 6 heures , moi après vos explications 1 minutes  , vous  21s ....

ça va être utile car au final je pense rajouté une dizaine de colonnes.

merci encore et bonne soirée

gleu

Administrateur

Re : Lenteur d’exécution d'un Update simple

Je suppose qu'avant le tag_559, il y avait 558 autres tag_, et vous parlez d'en rajouter. À un moment, vous allez atteindre une limite, PostgreSQL n'acceptant pas un nombre illimité de colonnes (de souvenir, c'est entre 250 et 1600 suivant la taille (et donc le type) des colonnes). Peut-être qu'il serait bon de passer à une colonne de type tableau.

---

Guillaume.