On Mon, 20 Feb 2006, Thomas Burdairon wrote:

> I've seen a strange comportment of implementation of PreparedStatement
> and especially the clearParameters method. The normal functionnement of
> this is that if I create a PreparedStatement, the query is sent once to
> the server, and then only parameters are sent to the server.
> clearParameters erase parameters already set in the PreparedStatement
> object.
>
> But, it seems the clearParameters method remove us all the benefit of
> prepare statements, because the query is sent again on each
> executeQuery/executeUpdate call. But without using clearParameters, the
> comportment is correct.

Yes, this is a problem. The coding of clearParameters clears the type
information associated with the PreparedStatement as well as the bind
values. This causes the query to get reprepared as it thinks the types
have changed. This code is necessary because the statement must
be replanned when something like this happens:

ps.setInt(1, 1);
ps.execute();
ps.setLong(1, 1);
ps.execute();

I suspect that just not clearing the type information will fix this, but
I'll need to test that a little. Thanks for the report.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Mon, 20 Feb 2006, Kris Jurka wrote:

> On Mon, 20 Feb 2006, Thomas Burdairon wrote:
>
>> I've seen a strange comportment of implementation of PreparedStatement and
>> especially the clearParameters method. The normal functionnement of this is
>> that if I create a PreparedStatement, the query is sent once to the server,
>> and then only parameters are sent to the server. clearParameters erase
>> parameters already set in the PreparedStatement object.
>>
>> But, it seems the clearParameters method remove us all the benefit of
>> prepare statements, because the query is sent again on each
>> executeQuery/executeUpdate call. But without using clearParameters, the
>> comportment is correct.
>
> Yes, this is a problem. The coding of clearParameters clears the type
> information associated with the PreparedStatement as well as the bind values.
> This causes the query to get reprepared as it thinks the types have changed.

Actually I was too quick to agree with you. Testing this doesn't show
anything going wrong. I suspect you are seeing the effect of the
prepareThreshold URL parameter. By default we only reuse a
PreparedStatement on the fifth execution of it, before that we reprepare
it every time because of the potential performance differences from
generating a plan with exact vs default values. Many people use
PrepareStatements only for the cleanliness and portability of avoiding
interpolating parameters manually and never reuse the statement. For
single use queries we want the planner/optimizer to create the best
available plan for that query, so we do not reuse the plan for later
executions.

Please try your testcase again adding loglevel=2 to your URL to
show when the driver is actually sending Parse messages. Then try adding
prepareThreshold=1 to your URL to tell the driver that you want to reuse
the query plan after one execution.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq