Re: Prepared statement performance...

Dimitry,

I have some good news for you. All of this functionality is in 7.3 of
the server and the current development build of the jdbc driver. Of
course 7.3 isn't quite production yet, but beta 2 should be out this week.

Your first option is what has been implemented. In 7.3 the server now
supports 'prepare <name> as <sql>' and 'execute <name>' (and variants
for passing bind variables). The jdbc driver also now has a method on
org.postgresql.PGStatement.setUseServerPrepare(true) to enable the the
use of server side prepared statements (so you can cast your Statement
object to an org.postgresql.PGStatement object and enable the
functionality for a specific statement).

This hasn't yet made its way into the jdbc documentation for 7.3, but
this will be documented before 7.3 goes production.

This implementation does still require that you make some changes to
your app in order to enable it but that is by design for the following
reasons:
1) In order to be useful you need to reuse a statement object
multiple times. Most code generally will just create a statement and
use it only once, and since more work is being done to prepare the
statement that would have a negative performance impact for the majority
of statements being executed in normal code.
2) Prepare functionality will not work for all types of sql that you
can execute through the jdbc driver. For example you can quite easily
do the following: conn.prepareStatement("update foo set a = ?; insert
into bar values (?,?,?);");
By issuing both sql statements in one call only one network roundtrip is
needed to execute both statements providing what is likely a more
performant application. However this doesn't work with prepare because:
prepare foo as update foo set a = ?; insert into bar values(?,?,?); -
is one prepared statement and one regular statement which will cause
very strange things to happen.

It is for the reasons above that the developer needs to explicitly turn
on the use of server side prepared statements when it makes sense to do so.

thanks,
--Barry

PS. I would really appreciate it if you could test this new
functionality. As far as I know I am the only one who has used it. But
for one of my frequently used and complex sql statements the time to
execute dropped by 60% using server side prepared statements.

Dmitry Tkach wrote:
> Hi, everybody.
>
> I am running into huge performance problems, due to JDBC not being able
> to cache query plans :-(
> My java program runs the (set of about 5) identical statements with
> different parameters for about 30 million times...
> What I am talking about below has to do with JDBC, but is not really
> specific to it - it seems to me, that the backend itself
> could be improved to better handle this kind of scenario, when the same
> statement is executed many times in the same session.
>
> It takes about 3 weeks(!) to run, and it looks like about *half* of that
> time is spent by the query planner,
> creating and recreating the query plans every time I ran the damn
> statement....
>
> I am looking into implementing some kind of a solution, that would let
> me work around that problem...
> So far, I only see two possibilities:
>
> - a general solution, that would involve extending postgres SQL gramma
> to include a 'prepare' statement
> - or am ugly work around, that would involve moving all my sql
> statements into stored procedures, and have
> those cache the query plans inside...
>
> The second solution is not only ugly (because it requires the
> application code to be changed and to have a specialized
> stored procedure for every query), but also requires some additional
> hacks (to overcome the hard limit on the number of
> function arguments and the inability for functions to return tuples) -
> the only way I imagine this can be made to work is
> to glue all the arguments together into a text string, and have the
> stored procedure parse it back, execute the query, then
> glue the resulting tuple(s) into another text string, return it, and
> have the application (or, perhaps, JDBC layer) parse it back
> into columns...
>
> I was wonderring if anybody has any better ideas how this can be made to
> work (I am looking for a solution that would
> minimize changes to the existing JDBC applications that use
> PreparedStatements)?
>
> If the maintainers of the involved code are interested, I would be
> willing to implement and contribute the solution we come up with ...
> (I figure, nobody would really be interested in getting that second
> solution I mentioned into the mainstream :-), but, if we are
> able to come up with something more general and less ugly, perhaps, I am
> not the only one who would be able to contribute from
> it)...
>
> For example, I believe, it should not be too complicated to implement
> that first possibility I described above...
> The way I see it would involve adding two statements to postgres SQL
> syntax:
>
> prepare <name> as <sql statement>
> and
> execute <name> with (<parameter list>)
>
> For example:
>
> prepare mystatement as select * from mytable where id = $1 and name like
> $2;
>
> and then
>
> execute mystatement with (1, 'Dima');
> execute mystatement with (2, 'John');
>
> etc....
>
> The JDBC driver would then send the 'prepare' command to the backend in
> Connection.prepareStatement (), and
> use the 'execute' in PreparedStatement.execute ();
>
> One potential problem with implementation I see here is that the query
> planner wants to know the argument types ahead of time...
> I guess, I could get around that by making all the arguments 'text', and
> having them casted into the right types when the
> statement is actually executed.
>
> There is, probably a need to also have some kind of a 'close' command to
> throw away the prepared query plans... or we could just
> make them last forever until, say, the end of transaction (or, perhaps,
> the end of the session?)...
>
> If there is anyone interested in discussing various possibilities, and
> getting this implemented one way or another,
> I would like to hear from you!
>
> Thanks!
>
> Dima.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>