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
