Decrease the time required function
Hi everyone,
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
$BODY$
DECLARE
criterios CURSOR FOR SELECT * FROM criterio;
casos_reales CURSOR FOR SELECT * FROM caso_real;
sum_impactos NUMERIC DEFAULT 0;
sum_impacto_modulo NUMERIC DEFAULT 0;
impacto NUMERIC DEFAULT 0;
valor_caso_real_criterio NUMERIC DEFAULT 0;
valor_caso_escenario_criterio NUMERIC DEFAULT 0;
s NUMERIC DEFAULT 0.0;
c RECORD;
cr RECORD;
crc RECORD;
cec RECORD;
casos_escenarios_criterios RECORD;
casos_reales_criterios RECORD;
BEGIN
/*
* RECORRER CURSOR DE CRITERIOS Y
* SUMATORIA DE LOS IMPACTOS DE LOS CRITERIOS
*/
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;
/*
* OBTENER CRITERIOS DEL CASO ESCENARIO PASADO POR PARAMETRO
*/
SELECT * INTO casos_escenarios_criterios FROM caso_escenario_criterio
WHERE caso_escenario_id = $1;
/*
* RECORRER CURSOR DE CASOS REALES *
*/
BEGIN
OPEN casos_reales;
LOOP FETCH casos_reales into cr;
IF NOT FOUND THEN
EXIT;
ELSE
sum_impacto_modulo := 0;
impacto := 0;
valor_caso_real_criterio := 0;
valor_caso_escenario_criterio := 0;
/*
* OBTENER CRITERIOS DEL CASO REAL EN CUESTI�N
*/
SELECT * INTO casos_reales_criterios FROM caso_real_criterio WHERE
caso_real_id = cr.id;
OPEN criterios;
LOOP FETCH criterios into c;
IF NOT FOUND THEN
EXIT;
ELSE
SELECT c_r_c.id, valor INTO crc
FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
WHERE c_.id = c_r_c.criterio_id
AND c_.id = c.id
AND c_r_c.caso_real_id = c_r.id
AND c_r.id = cr.id;
valor_caso_real_criterio := crc.valor;
SELECT c_e_c.id, valor INTO cec
FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
WHERE c_.id = c_e_c.criterio_id
AND c_.id = c.id
AND c_e_c.caso_escenario_id = c_e.id
AND c_e.id = escenario_id;
valor_caso_escenario_criterio := cec.valor;
impacto := c.impacto;
sum_impacto_modulo := sum_impacto_modulo + impacto * (1 -
abs(valor_caso_real_criterio - valor_caso_escenario_criterio)/5);
END IF;
END LOOP;
CLOSE criterios;
s := sum_impacto_modulo / sum_impactos;
IF s >= 0.75 THEN
RETURN NEXT cr;
END IF;
END IF;
END LOOP;
CLOSE casos_reales;
END;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION listarcasosrecuperados(integer)
OWNER TO postgres;
I need to decrease the time required function. Please, anyone helpme.
Regards, Karel River�n
Students Scientific Council
Informatics Science University
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Karel Riverón wrote:
I have a PL/pgSQL function that it takes 4 seconds to execute.
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
+1
On a quick look, it seems like someting along these lines might be
what you want (although I had to guess a little at schema, since
you didn't show it):
SELECT cr.*
FROM (SELECT sum(impactos) AS sum_impactos FROM criterio) si
CROSS JOIN criterio c
JOIN caso_real_criterio crc ON (crc.criterio_id = c.id)
JOIN caso_real cr ON (cr.id = crc.caso_real_id)
JOIN caso_escenario_criterio cec ON (cec.criterio_id = c.id)
JOIN caso_escenario ce ON (ce.id = cec.caso_escenario_id)
WHERE ce.id = escenario_id
GROUP BY cr.id
HAVING sum(c.impacto * (1 - abs(crc.valor - cec.valor) / 5))
/ si.sum_impactos >= 0.75
;
You might need to adjust the GROUP BY clause if you're not running
a recent major release. If you want to keep it as a function, you
can throw out the DECLARE section and everything between the
outermost BEGIN and END, and replace it with RETURN QUERY and the
above query, or turn it into a SQL function to avoid the overhead
of materializing the entire result set.
If you get some form of that to run, please post back with a
comparison of run times.
-Kevin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general