Search Postgresql Archives

Re: Decrease the time required function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux