Re: Stored procedures when and how: was: Sun acquires MySQL

>
> Yep, the more I read, the more I get confused.
> Java loading overhead is a common myth (I can't say if true or false),
> and what Tom writes above can find a tentative place in my mind.
> But still then I can't understand where plsql should or shouldn't be
> used.
>
> I really would enjoy to see some general guideline on how to chose.
>

1. use procedure lot of SQL statements --> use plpgsql
2. procedure needs some untrusted functionality -> use untrusted language
3. procedure contains only expressions
3.a) isn't too much important --> use plpgsql don't forgot IMMUTABLE flag
3.b) is important and is bottleneck --> try perl
3.c) is most important or is wide used --> use C
3.d) is simply implemented in C (some time, string fce) --> use C

learn some trick:

create or replace function list(int)
returns varchar as $$
declare s varchar = '';
begin
 for i in 1..$1 loop
   s := s || '<item>' || i || '</item>';
 end loop;
 return s;
end; $$ language plpgsql;

postgres=# select list(10);

list

-----------------------------------------------------------------------------------------------------------------------------------------------

<item>1</item><item>2</item><item>3</item><item>4</item><item>5</item><item>6</item><item>7</item><item>8</item><item>9</item><item>10</item>
(1 row)

Time: 0,927 ms -- well

number, time
100, 5ms
1000, 75ms   ...  usable
10000, 4s ... slow

so if I use fce list with param < 1000 I can use plpgsql without any
problems. With bigger value I have problem. But I forgot IMMUTABLE,
ook try again:

100, 4ms
1000, 70ms
10000, 3.8s   ok IMMUTABLE doesn't help here

what is bottleneck? FOR?

create or replace function list(int)
returns varchar as $$
declare s varchar = '';
 begin
   for i in 1..$1 loop
     perform  '<item>' || i || '</item>';
   end loop;
   return s;
end; $$ language plpgsql immutable;

10000, 443 ms ..

bottleneck is in repeated assign s := s || ..

I will try trick:

create or replace function list(int)
returns varchar as $$
 begin
   return array_to_string(array(select '<item>' || i || '</item>'
from generate_series(1, $1) g(i)), '');
 end$$ language plpgsql immutable;

test
100, 1.3ms
1000, 7.64ms
10000, 63ms -- nice I don't need C
100000, 350ms
Regards

Pavel Stehule


> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>