Re: [GENERAL] Prepared statement performance... - Mailing list pgsql-jdbc

Barry Lind <barry@xythos.com> writes:
> It is a bit more complex than just looking for a ; since a ; is a
> valid character in a quoted string.  But as Bruce has mentioned in a
> followup, psql does it so it is doable.
>
> I think the real question here is when does it make sense to use
> server side prepared statements.  In the little bit of testing I have
> done, I would say the answer is rarely.  You need many factors to come
> into place for it to make sense to use server side prepared statements:
>
> 1) The statement needs to be big and complex such that there is
> significant overhead in the parsing and planning stages of
> execution. The query that I test with is about 3K in size and joins
> about 10 different tables and includes a union.  In this case there is
> a significant overhead involved in both parsing and planning.
> However for a simple query that affects only one or two tables there
> is little overhead.
>
> 2) The statement object needs to be reused multiple times.  Using a
> server prepared statement requires at a minimum three sql statements
> to be executed to do the work of one original statement:
> select foo from bar;
> becomes
> prepare <name> as select foo from bar;
> execute <name>;
> deallocate <name>;

Note that DEALLOCATE is not really necessary -- prepared statements
are flushed from memory when the backend exits (I suppose if you're
using a connection pool, however, you should still explicitely
DEALLOCATE prepared statements when you're done with them).

> We can do the first two together in one roundtrip to the server, but
> the last one requires a separate roundtrip.  So if you are only using
> a the statement object/query only once then using server side prepared
> statements will make performance worse than not.  The vast majority of
> statement objects are created, executed once and then closed.  I don't
> think it makes sence to turn on a feature that will make the overall
> performance for most users worse.  There is very little jdbc code that
> I have seen that creates a statement, call execute multiple times on
> that same statement object and then finally closes it.
>
> This is the first version of the feature.  Improvements will come with
> implementation feedback and I welcome all feedback.

As do I (in reference to the backend part of the feature).

> In the long term what I would really like to see is this
> functionality done at the BE/FE protocol level instead of at the sql
> level as I have seen other databases do.

Yeah, that would be good. However, it requires a protocol change,
which wasn't feasible during the 7.3 development cycle. However, it
looks like there will be an FE/BE protocol change for 7.4, so that
might be a good opportunity to add protocol-level support.

>
> Someone has suggestted a jdbc url arguement to enable the
> functionality by default and that is probably a good idea if we learn
> from real use that it makes sense to have all statements use this.
> But I am not convinced (given my reasons above) that this really makes
> sense.

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC