Re: pl/php for windows - Mailing list pgsql-php

Andrew McMillan schrieb:
> On Sat, 2009-03-07 at 18:51 +0100, Andreas Wenk wrote:
>> Hi Andrew,
>>
>> woooh .... sounds like giving the advice to reduce the usage of user
>> defined functions to a minimum. But in my opinion it should be a good
>> idea to move functionality to the database in case you work with a
>> Language like PHP. As I recognized it is a good idea to do so 'cause the
>> database is often faster than PHP.
>>
>> I am speaking of PL/pgsql and SQL udf's.
>>
>> Did I misunterstand you? Are there other experiences? In which cases is
>> the planner forced to have problems with statements containing udf's?
>
>From what I have seen, sometimes the planner can have no idea about the
> costs of a function.  This is essentially an unsolvable problem -
> consider the case where for certain values of input a complex select
> may/may not be performed.  Consequently the planner doesn't try very
> hard, and assigns a default performance cost for the function call which
> may be wildly wrong.
>
> But there can definitely be performance improvements from functions in
> the database, where communication may be faster.  I'm not blindly in
> favour of either approach, though I have had horrible experiences having
> to maintain an Oracle application where *everything* was in database
> functions, so serving most pages came down to something like:
>
>  SELECT some_function(input1,input2) FROM dual;
>
> There was a lot of developer overhead in writing in that way, because
> Oracle developers are (very) expensive, development is generally slower
> than in PHP or Perl or modern scripting languages.  Also, in that case,
> there was little value in the meagre performance increases.
>
> My experience strongly suggests that PHP programmers are many orders of
> magnitude easier to find than PL/PgSQL programmers, so I try not to
> write much more in PL/PgSQL than is necessary for performance (or
> simplicity) reasons, and I try and keep my functions small (not always
> successfully :-).
>
> That said, I think more people *should* understand PL/PgSQL, and I have
> a very successful 1-2 hour talk I can give at the drop of a hat which
> introduces people to many interesting features of PL/PgSQL by going over
> the solution to a particular problem which only an idiot would want to
> solve in the language.  And it turns out that my code to do that in
> PL/PgSQL is around 400 lines, compared with around 700 lines for some
> equivalent code I have written in PHP.
>
> So by no means am I saying "don't use in-database functions".  I'm
> saying think carefully about it, considering:
>
>  * development costs
>  * maintenance costs
>  * performance gains / losses
>  * additional per connection memory for the interpreter
>
> That last one doesn't apply so much for PL/SQL and probably not
> particularly for PL/PgSQL which has a low memory footprint, as far as I
> can see.
>
> But yeah, I guess I am advocating that development and maintenance costs
> are significantly greater for in-database functions, so don't use them
> everywhere. They can also have unanticipated effects on performance, so
> even when you do decide to use them make sure you check your assumptions
> about performance improvement before spending too much time and effort
> on it.
>
> Cheers,
>                     Andrew McMillan.
>
> ------------------------------------------------------------------------
> andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
>             Executive ability is prominent in your make-up.
> ------------------------------------------------------------------------

Hey Andrew,

thanks a lot for sharing your experiences. Seems that we have the same
point of view. Means one has to compare allways the performance
improvement between writing some functionality in the database or in the
code like PHP. And another point is having the ability with PL/pgSQL to
develop faster - just think about the lousy date calculating
implementation in PHP compared to the great date functions in posgresql
(and other db's). It turns out, that having this in mind will produce
the best results.

Cheers

Andy