Re: calling procedures is slow and consumes extra much memory against calling function

so 16. 5. 2020 v 13:40 odesílatel Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
napsal:

> Em sáb., 16 de mai. de 2020 às 01:10, Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com> escreveu:
>
>>
>>
>> so 16. 5. 2020 v 5:55 odesílatel Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
>> napsal:
>>
>>> Em sáb., 16 de mai. de 2020 às 00:07, Pavel Stehule <
>>> pavel(dot)stehule(at)gmail(dot)com> escreveu:
>>>
>>>>
>>>>
>>>> so 16. 5. 2020 v 0:34 odesílatel Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
>>>> napsal:
>>>>
>>>>> Em dom., 10 de mai. de 2020 às 17:21, Pavel Stehule <
>>>>> pavel(dot)stehule(at)gmail(dot)com> escreveu:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> I try to use procedures in Orafce package, and I did some easy
>>>>>> performance tests. I found some hard problems:
>>>>>>
>>>>>> 1. test case
>>>>>>
>>>>>> create or replace procedure p1(inout r int, inout v int) as $$
>>>>>> begin v := random() * r; end
>>>>>> $$ language plpgsql;
>>>>>>
>>>>>> This command requires
>>>>>>
>>>>>> do $$
>>>>>> declare r int default 100; x int;
>>>>>> begin
>>>>>> for i in 1..300000 loop
>>>>>> call p1(r, x);
>>>>>> end loop;
>>>>>> end;
>>>>>> $$;
>>>>>>
>>>>>> about 2.2GB RAM and 10 sec.
>>>>>>
>>>>> I am having a consistent result of 3 secs, with a modified version
>>>>> (exec_stmt_call) of your patch.
>>>>> But my notebook is (Core 5, 8GB and SSD), could it be a difference in
>>>>> the testing hardware?
>>>>>
>>>>
>>>> My notebook is old T520, and more I have a configured Postgres with
>>>> --enable-cassert option.
>>>>
>>> The hardware is definitely making a difference, but if you have time and
>>> don't mind testing it,
>>> I can send you a patch, not that the modifications are a big deal, but
>>> maybe they'll help.
>>>
>> With more testing, I found that latency increases response time.
> With 3 (secs) the test is with localhost.
> With 6 (secs) the test is with tcp (local, not between pcs).
>
> Anyway, I would like to know if we have the number of parameters
> previously, why use List instead of Arrays?
> It would not be faster to create plpgsql variables.
>

Why you check SPI_processed?

+ if (SPI_processed == 1)
+ {
+ if (!stmt->target)
+ elog(ERROR, "DO statement returned a row, query \"%s\"", expr->query);
+ }
+ else if (SPI_processed > 1)
+ elog(ERROR, "Procedure call returned more than one row, query \"%s\"",
expr->query);

CALL cannot to return rows, so these checks has not sense

> regards,
> Ranier Vilela
>