Re: Decrease the time required function

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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