Thread: Decrease the time required function

Hi everyone,<br /><br /> I have a PL/pgSQL function that it takes 4 seconds to execute. This is my function:<br /><br
/><fontcolor="#666666" face="Courier New, Courier, monospace">CREATE OR REPLACE FUNCTION
listarcasosrecuperados(escenario_idinteger)<br />   RETURNS SETOF caso_real AS<br /> $BODY$ <br /><br /> DECLARE <br
/><br/>     criterios CURSOR FOR SELECT * FROM criterio;<br /><br />     casos_reales CURSOR FOR SELECT * FROM
caso_real;<br/><br />     sum_impactos NUMERIC DEFAULT 0;<br /><br />     sum_impacto_modulo NUMERIC DEFAULT 0;<br
/><br/>     impacto NUMERIC DEFAULT 0;<br /><br />     valor_caso_real_criterio NUMERIC DEFAULT 0;<br /><br />    
valor_caso_escenario_criterioNUMERIC DEFAULT 0;<br /><br />     s NUMERIC DEFAULT 0.0;<br /><br />     c RECORD;<br
/><br/>     cr RECORD;<br /><br />     crc RECORD;<br /><br />     cec RECORD;<br /><br />    
casos_escenarios_criteriosRECORD;<br /><br />     casos_reales_criterios RECORD;<br /><br /> BEGIN<br /><br /> /*<br />
 *RECORRER CURSOR DE CRITERIOS Y <br />  * SUMATORIA DE LOS IMPACTOS DE LOS CRITERIOS<br />  */<br /> OPEN
criterios;<br/><br /> LOOP FETCH criterios into c;<br /><br /> IF NOT FOUND THEN<br />     EXIT;<br /> ELSE<br />    
sum_impactos:= sum_impactos + c.impacto;<br /> END IF;<br /><br /> END LOOP;<br /><br /> CLOSE criterios;<br /><br />
/*<br/>  * OBTENER CRITERIOS DEL CASO ESCENARIO PASADO POR PARAMETRO<br />  */<br /><br /> SELECT * INTO
casos_escenarios_criteriosFROM caso_escenario_criterio WHERE caso_escenario_id = $1;<br /><br /> /*<br />  * RECORRER
CURSORDE CASOS REALES * <br />  */<br /> BEGIN<br /> OPEN casos_reales;<br /><br /> LOOP FETCH casos_reales into cr;<br
/><br/> IF NOT FOUND THEN<br />     EXIT;<br /> ELSE<br />     sum_impacto_modulo := 0;<br /><br />     impacto :=
0;<br/><br />     valor_caso_real_criterio := 0;<br /><br />     valor_caso_escenario_criterio := 0;<br /><br />    
/*<br/>     * OBTENER CRITERIOS DEL CASO REAL EN CUESTIÓN    <br />     */<br /><br />     SELECT * INTO
casos_reales_criteriosFROM caso_real_criterio WHERE caso_real_id = cr.id;<br /><br />     OPEN criterios;<br /><br />
   LOOP FETCH criterios into c; <br /><br />     IF NOT FOUND THEN<br />         EXIT;    <br />     ELSE<br />     <br
/>        SELECT c_r_c.id, valor INTO crc <br />         FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_ <br
/>        WHERE c_.id = c_r_c.criterio_id <br />         AND c_.id = c.id <br />         AND c_r_c.caso_real_id =
c_r.id<br />         AND c_r.id = cr.id;<br /><br />         valor_caso_real_criterio := crc.valor;<br /><br />        
SELECTc_e_c.id, valor INTO cec <br />         FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_ <br />
       WHERE c_.id = c_e_c.criterio_id <br />         AND c_.id = c.id <br />         AND c_e_c.caso_escenario_id =
c_e.id<br />         AND c_e.id = escenario_id;<br /><br />         valor_caso_escenario_criterio := cec.valor;<br
/><br/>         impacto := c.impacto;<br /><br />         sum_impacto_modulo := sum_impacto_modulo + impacto * (1 -
abs(valor_caso_real_criterio- valor_caso_escenario_criterio)/5);<br />     <br />     END IF;<br /><br />     END
LOOP;<br/><br />     CLOSE criterios;<br /><br />     s := sum_impacto_modulo / sum_impactos;<br /><br />     IF s
>=0.75 THEN<br />            RETURN NEXT cr;         <br />         END IF;    <br />     <br /> END IF;<br />    
<br/> END LOOP;<br /><br /> CLOSE casos_reales;<br /><br /> END;<br /><br /> END <br /><br /> $BODY$<br />   LANGUAGE
plpgsqlVOLATILE<br />   COST 100<br />   ROWS 1000;<br /> ALTER FUNCTION listarcasosrecuperados(integer)<br />   OWNER
TOpostgres;</font><br /><font face="Times New Roman, Times, serif"><br /> I need to decrease the time required
function.Please, anyone helpme.<br />  <br /></font><div class="moz-signature"><div style="font: 16px Times New
Roman;color:black"><font face="Times New Roman, Times, serif"> Regards, Karel Riverón<br /> Students Scientific
Council<br/> Informatics Science University</font><br /><br /><br /></div></div> 

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