Re: Decrease the time required function

Karel River=F3n wrote:
> I have a PL/pgSQL function that it takes 4 seconds to execute. This is my=
 function:
>=20
> 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 :=3D 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