Forums PostgreSQL.fr

shared_buffers = 1GB
work_mem = 512MB
effective_cache_size = 2GB
temp_buffers = 512MB

-- point d'entrée, executé en 70 min
procedureA
    dos_encours CURSOR FOR
        SELECT *
        FROM table1
        WHERE colA <= date AND TRIM(colB) IS NOT NULL AND (colB) <> '        ' ORDER BY colA;
    
    FOR l_dos IN dos_encours LOOP
        PERFORM procedureB('E',l_dos.colA,l_dos.colB,...);
    END LOOP;

-- appelé 28 000 fois
procedureB
    ...
    IF ancien > 0 THEN
        PERFORM procedureC(...);
        PERFORM procedureD(...);
        PERFORM procedureE(...);
        PERFORM procedureD(...);
        PERFORM procedureE(...);
    END IF;
    
-- appelé 28 000 fois
procedureC
    WHILE
        ...
        IF
            ...
            FOR
            ...
                procedureF(...);
        ELSE IF
        ...
    ...
    procedureD(...);
    procedureE(...);    

-- appelé entre 28 000 et 84 000
procedureD et procedureE
    IF 
        PERFORM procedureF(...);
    ELSE
        PERFORM procedureF(...);


-- appelé 335000 fois
procedureF
  IF quel = 1 THEN
    PERFORM procedureG(...);
  ELSIF quel = 2 THEN
    ...

-- appelé au total 112 000 fois, self_time : 65 min
procedureG
  IF moy>0 THEN
    UPDATE table2
    SET colA=to_char(to_number_arob(rtrim(colA),'9999990.99')+moy,'9999990.99')
    WHERE colB=param1 AND colC=param2;
  END IF;
  
  UPDATE wingescph.f_suivi
  SET colA=to_char(moy,'9999990.99')
  WHERE colB=param1 AND colC=param2 AND to_number_arob(TRIM(colA),'9999990.99')<moy;