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

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.

regards,
Ranier Vilela