Обсуждение: 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.
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
Students Scientific Council
Informatics Science University
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
Albe Laurenz <laurenz.albe@wien.gv.at> wrote:=0A> Karel River=F3n wrote:=0A= =0A>> I have a PL/pgSQL function that it takes 4 seconds to execute.=0A=0A>= > OPEN casos_reales;=0A>> LOOP FETCH casos_reales into cr;=0A> [...]=0A>>= =A0=A0=A0=A0 OPEN criterios;=0A>>=A0=A0=A0=A0 LOOP FETCH criterios into c;= =0A> [...]=0A>>=A0=A0=A0=A0=A0=A0=A0=A0 SELECT c_r_c.id, valor INTO crc=0A>= >=A0=A0=A0=A0=A0=A0=A0=A0 FROM caso_real_criterio c_r_c, caso_real c_r,crit= erio c_=0A> [...]=0A>>=A0=A0=A0=A0=A0=A0=A0=A0 SELECT c_e_c.id, valor INTO = cec=0A>>=A0=A0=A0=A0=A0=A0=A0=A0 FROM caso_escenario_criterio c_e_c, caso_e= scenario c_e,criterio c_=0A> [...]=0A>>=A0=A0=A0=A0 END LOOP;=0A> [...]=0A>= > END LOOP;=0A>=0A> I did not study your processing in detail, but it looks= =0A> like most of that could be expressed as a single=0A> SQL statement tha= t joins the four tables=0A=0A+1=0A=0AOn a quick look, it seems like sometin= g along these lines might be=0Awhat you want (although I had to guess a lit= tle at schema, since=0Ayou didn't show it):=0A=0ASELECT cr.*=0A=A0 FROM (SE= LECT sum(impactos) AS sum_impactos FROM criterio) si=0A=A0 CROSS JOIN crite= rio c=0A=A0 JOIN caso_real_criterio crc ON (crc.criterio_id =3D c.id)=0A=A0= JOIN caso_real cr ON (cr.id =3D crc.caso_real_id)=0A=A0 JOIN caso_escenari= o_criterio cec ON (cec.criterio_id =3D c.id)=0A=A0 JOIN caso_escenario ce O= N (ce.id =3D cec.caso_escenario_id)=0A=A0 WHERE ce.id =3D escenario_id=0A= =A0 GROUP BY cr.id=0A=A0 HAVING sum(c.impacto * (1 - abs(crc.valor - cec.va= lor) / 5))=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 / si.sum_impactos >=3D 0.75=0A;= =0A=0AYou might need to adjust the GROUP BY clause if you're not running=0A= a recent major release.=A0 If you want to keep it as a function, you=0Acan = throw out the DECLARE section and everything between the=0Aoutermost BEGIN = and END, and replace it with RETURN QUERY and the=0Aabove query, or turn it= into a SQL function to avoid the overhead=0Aof materializing the entire re= sult set.=0A=0AIf you get some form of that to run, please post back with a= =0Acomparison of run times.=0A=0A-Kevin