Re: Decrease the time required function

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