Karel Riverón wrote: > I have a PL/pgSQL function that it takes 4 seconds to execute. This is my function: > > CREATE OR REPLACE FUNCTION listarcasosrecuperados(escenario_id integer) > RETURNS SETOF caso_real AS [...] > OPEN criterios; > LOOP FETCH criterios into c; > IF NOT FOUND THEN > EXIT; > ELSE > sum_impactos := sum_impactos + c.impacto; > END IF; > END LOOP; > CLOSE criterios; This is very inefficient. You should use as much SQL as possible: SELECT sum(impacto) INTO sum_impactos FROM criterio; > OPEN casos_reales; > LOOP FETCH casos_reales into cr; [...] > OPEN criterios; > LOOP FETCH criterios into c; [...] > SELECT c_r_c.id, valor INTO crc > FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_ [...] > SELECT c_e_c.id, valor INTO cec > FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_ [...] > END LOOP; [...] > END LOOP; I did not study your processing in detail, but it looks like most of that could be expressed as a single SQL statement that joins the four tables caso_real, criterio, caso_real_criterio and caso_escenario_criterio. Instead you program a nested loop in PL/pgSQL. That is going to be inefficient. > I need to decrease the time required function. Please, anyone helpme. Write as much of your function as you can in SQL. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
