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

Barry,

The API spec says for PreparedStatement:

"An object that represents a precompiled SQL statement. A SQL statement is
precompiled and stored in a PreparedStatement object. This object can then
be used to efficiently execute this statement multiple times."

This tells me that the main purpose of this object type is to allow you to
multiple execution with possibly different parameters. IMHO, the setters are
there just in order to provide you a way to specify different parameters.
This also tells me that there is no alternative behaviour (i.e. the
statement represented by this kind of object IS PRECOMPILED). If the
PostgreSQL jdbc driver allows an alternative behaviour, this should not be
default and should be made available

I have to admit that linking the above question (behaviour of Prepared
Statement) with the question of how many queries a SQL statement is allowed
to contain may have been a bit arbitrary. (Actually, *I* did not make the
linkage, I was just probing the way along a course of thoughts already
offerred. :)) I wanted to tackle this question, because the practice of
putting multiple statements in one Statement object (separated with
semicolons) has already come up in connection with other --earlier not
implemented-- jdbc feature(s) and there the problem to handle this practice
also turned out to be a pain in the ass. Unfortunately, I do not have the
SQL 92 spec. I could only look into the Postgres Reference documentation.
Based on how it uses the term "statement", I would conclude that "statement"
is just an entry in the reference doc. As to the getMoreResults method:
issuing several queries is not the only way to obtain multiple results sets.
E.g: your query may involve a function which returns multiple result sets
(or just one result set and an update count). My personal opinion is that
putting multiple statements in one Statement object is just abusing the fact
that the backend allows to do, but the SQL 92 spec should be, of course, the
ultimate authority in this regard.

Cheers,

Peter

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Peter Kovacs" <peter.kovacs@sysdata.siemens.hu>
Cc: "Dmitry Tkach" <dmitry@openratings.com>; <pgsql-general@postgresql.org>;
<pgsql-jdbc@postgresql.org>
Sent: Friday, September 27, 2002 8:24 PM
Subject: Re: [JDBC] [GENERAL] Prepared statement performance...


> Cool an interesting thread on the jdbc list.
>
> First off I dissagree with your reading of the spec that the statement
> or prepared statement object are only intended for one sql statement.
> If that were true why would you have Statement.getMoreResults()?  The
> API clearly supports a single statement execute returning multiple
> result sets which implies to me that the single execute can be used for
> multiple individual sql statements chained together.
>
> The uses of the Statement object and PreparedStatement object do not
> IMHO have anything to do with you many times you use them.  They have to
> do with how you supply values in the sql statement.  If you use just the
> Statement object you are required to know the postgres date format in
> order to include a date value, where it is much easier (and more
> portable across databases) to just use a PreparedStatement, a ?, and a
> setDate() call.
>
> thanks,
> --Barry
>
> Peter Kovacs wrote:
>  > Barry,
>  >
>  > It's great to have this functionality.
>  >
>  > However, I am surprised about the need/requirement to explicitely
> turning on
>  > this feature in the JDBC driver. As you explain, the reason for this
>  > behaviour is to support the use of multiple statements with the
>  > prepareStatement method. But this is supporting incorrect us of the
JDBC
>  > interface, since the JDBC API says that the parameter of the
>  > prepareStatement method is one(!) SQL statetment. On the other hand,
> it is
>  > clear that the intended purpose of the prepareStatement method is to
use
>  > prepared statment -- exclusively and not by default. For statements
> executed
>  > only once, the designers of the API made available the method
>  > "createStatment".
>  >
>  > So the buttom line is that you have an irregular behaviour in support
> of an
>  > irregular coding practice. I understand that there is "legacy" code
that
>  > needs to use the prepareStatement method with multiple SQL
> statements. But
>  > since such practice was already incorrect at the time of its
> inception, the
>  > right way would be to allow a transition from bad to good by
> requiring the
>  > "sinners" to explicitly use some kind of
>  > "org.postgresql.PGStatement.setUseServerPrepare(false)" tweaking in
their
>  > code, and let new coders use standard code.
>  >
>  > Of course, you're running the show, so you will do it as you deem
> right, but
>  > IMHO the implementation as you described it is clearly not reasonable.
>  >
>  > Cheers,
>  >
>  > Peter
>  >
>  > ----- Original Message -----
>  > From: "Barry Lind" <barry@xythos.com>
>  > To: "Dmitry Tkach" <dmitry@openratings.com>
>  > Cc: <pgsql-general@postgresql.org>; <pgsql-jdbc@postgresql.org>
>  > Sent: Thursday, September 26, 2002 10:56 PM
>  > Subject: Re: [JDBC] [GENERAL] 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@postgresql.org)
>  >> >
>  >>
>  >>
>  >>
>  >>---------------------------(end of
broadcast)---------------------------
>  >>TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
>  >>
>  >
>  >
>  >
>  >
>
>