Re: PreparedStatement clearParameters

Hello all
First, i'm not sure about if this is the right place to post it, but
didn't see any bugtracker on the web site.
I don't know if it's a known bug, i didn't found any reference to it
on the Todo page, or in mailing list archives
Then, i don't even know if it's a bug, maybe it's a feature (?)

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

Tested on
Postgresql : 8.1 (MacOsX)
JDBC driver : release 404

sample code :

create table test(id int, desc text);

int n=1000;
PreparedStatement pst = db.prepareStatement("INSERT INTO test(id,
desc) VALUES (?, ?)");
for(int i=0; i< n; i++){
pst.setInt(1, i);
pst.setString(2, "haha");
pst.executeUpdate();
}

//opposite to

PreparedStatement pst2 = db.prepareStatement("INSERT INTO test(id,
desc) VALUES (?, ?)");
for(int i=0; i< n; i++){
pst.clearParameters();
pst.setInt(1, i);
pst.setString(2, "hoho");
pst.executeUpdate();
}

To check the communication between jdbc driver and postgresql server,
you can use this simple shell script :
sudo tcpdump -i lo0 -A -s 65535 tcp port 5432 (assuming the
postgresql port is 5432)

Thomas

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

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

Thomas Burdairon <tburdairon(at)entelience(dot)com> writes:

> 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.

> Then, i don't even know if it's a bug, maybe it's a feature (?)

I have never read anything in the specifications of the JDBC interface
that _guarantees_ anything concerning performance. I only found more
or less fuzzy statements like "it is pre-compiled" (what does that
mean formally? to what extent?) or that "execution can be faster".

The corresponding ODBC documentation is a bit more precise
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcprepared_execution.asp>
except it specifically caters for the zero-preparation case:

If the driver cannot emulate statement preparation, it stores the
statement when SQLPrepare is called and submits it for execution when
SQLExecute is called.
Because emulated statement preparation is not perfect, SQLExecute can
return any errors normally returned by SQLPrepare.

So this definitely look like a feature ;-)

Please correct me when I am wrong.

By the way ODBC does not confuse parametrization and preparation like
JDBC does.