Thread: FW: PreparedStatement#setString on non-string parameters

FW: PreparedStatement#setString on non-string parameters

From
"Silvio Bierman"
Date:
Sorry, I originally sent sent this in a reply to a message CC-ed to me
directly...

-----Original Message-----
From: Silvio Bierman [mailto:sbierman@jambo-software.com]
Sent: Tuesday, March 08, 2005 2:51 PM
To: pg@fastcrypt.com
Subject: RE: [JDBC] PreparedStatement#setString on non-string parameters


Hello Dave,

Thanks for the reply and thanks to Guillaume for clearing up my not very
well expressed intentions. I was indeed talking about using
setString(String) where the parameter refers to a numeric value.

The way I read the spec only conversions from Java types to SQL types are
specified. Explicitly the specification says that setObject(Object) should
use the dynamic Java type of the object to determine the conversion. No
extra interpretation of the prepared statement logic is required by
setObject. It is merely a more generic way of setting parameters on the Java
side instead of implying extra interpretation logic.

I guess striktly speaking the spec does not say what should happend when a
driver maps a Java String to SQL VARCHAR and the corresponding parameter is
expected by the database to be numeric. It is probably just like passing a

select count(*) from table where integercol='3'

to a database. Should it do the implicit conversion?

Either the JDBC drivers for the databases I mentioned earlier do the
conversion or the database backends do it on the server side. Any way, this
works in all cases. PostgreSQL is the first database to break our
application due to this behaviour. We have had problems on earlier versions
of MySQL because of lack of subselect support etc. but never these issues.

I really love what I have seen of PostgreSQL until now and I am seriously
considering making it our preferred database backend. The ability to run on
both Linux and Windows is great, performance is excellent (I tried a quite
large database) even when I am still inserting my GUID keys as varchar data
and the admin tool is very good also.

Telling my users to use an old JDBC driver is not an option though...

Regards,

Silvio Bierman


@-----Original Message-----
@From: pgsql-jdbc-owner@postgresql.org
@[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
@Sent: Tuesday, March 08, 2005 2:10 PM
@To: Guillaume Cottenceau
@Cc: Silvio Bierman; PostgreSQL JDBC
@Subject: Re: [JDBC] PreparedStatement#setString on non-string parameters
@
@
@That makes sense.
@
@I just checked my reference and I can't find any document that suggests
@that setString should do a conversion.
@
@setObject is required to by the spec.
@
@Dave
@
@Guillaume Cottenceau wrote:
@
@>Dave Cramer <pg 'at' fastcrypt.com> writes:
@>
@>
@>
@>>Silvio,
@>>
@>>I presume you are talking about setObject ??? I can't find a
@>>setString(non-string param)
@>>
@>>
@>
@>He says "XML data (..) many numeric values are available as
@>Strings" so I think the SQL column is numeric but the rest is
@>string (a java string containing the string representation of the
@>numeric value).
@>
@>
@>
@
@--
@Dave Cramer
@http://www.postgresintl.com
@519 939 0336
@ICQ#14675561
@
@
@---------------------------(end of broadcast)---------------------------
@TIP 4: Don't 'kill -9' the postmaster


Re: FW: PreparedStatement#setString on non-string parameters

From
Oliver Jowett
Date:
Silvio Bierman wrote:

> Either the JDBC drivers for the databases I mentioned earlier do the
> conversion or the database backends do it on the server side. Any way, this
> works in all cases. PostgreSQL is the first database to break our
> application due to this behaviour. We have had problems on earlier versions
> of MySQL because of lack of subselect support etc. but never these issues.

I'd suggest using CAST in your SQL -- that in theory should work
everywhere and reflects your application's intent (to interpret a string
as a numeric value).

The problem with reverting to the old way of doing parameters (direct
text substitution into the query) is that we cannot take advantage of
most of the new stuff in the V3 protocol -- that means no server-side
prepared statement reuse, no low-overhead transfer of large parameters,
and reduced support for cursor-based resultsets.

-O

Re: FW: PreparedStatement#setString on non-string parameters

From
Antony Paul
Date:
Does this new stuff added in 8.0 driver adds to performance ?. I had
this setString() problem and I tested 7.4.x driver which works and
performs better than 8.0 driver.



On Wed, 09 Mar 2005 10:47:34 +1300, Oliver Jowett <oliver@opencloud.com> wrote:
> Silvio Bierman wrote:
>
> > Either the JDBC drivers for the databases I mentioned earlier do the
> > conversion or the database backends do it on the server side. Any way, this
> > works in all cases. PostgreSQL is the first database to break our
> > application due to this behaviour. We have had problems on earlier versions
> > of MySQL because of lack of subselect support etc. but never these issues.
>
> I'd suggest using CAST in your SQL -- that in theory should work
> everywhere and reflects your application's intent (to interpret a string
> as a numeric value).
>
> The problem with reverting to the old way of doing parameters (direct
> text substitution into the query) is that we cannot take advantage of
> most of the new stuff in the V3 protocol -- that means no server-side
> prepared statement reuse, no low-overhead transfer of large parameters,
> and reduced support for cursor-based resultsets.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


--
rgds
Antony Paul
http://www.geocities.com/antonypaul24/

Re: FW: PreparedStatement#setString on non-string parameters

From
"Silvio Bierman"
Date:
Hello Antony,

I honestly don't know. Oliver Jowett told me that the 8.0 driver is the
first to use server side prepared statements, which is good I guess. I do
not know the internals of PostgreSQL server statement caching but usually
using server side prepared statements is faster than emulating prepared
statements in the JDBC driver. In cases where statements are really
exectuted once only and the PreparedStatement is used for parameter
substitution convenience only (which is a very good reason, BTW) emulated
statements are usually faster.

I just tested with the 7.4 driver and that actually works fine. It also
fixed the setString error I got when using the James mail server with the
8.0 driver...

I can not say anything about performance in comparison to the 8.0 driver
yet. Oliver mentioned poor blob-performance in the pre-8 protocol. I am not
very happy with that since we use blobs quite frequently, be it that they
are are never very large.

I will be doing some tests with the 8.0 driver in combination with a
implicit cast versus the 7.4 driver. I will keep you posted...

Regards,

Silvio Bierman



@-----Original Message-----
@From: pgsql-jdbc-owner@postgresql.org
@[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Antony Paul
@Sent: Wednesday, March 09, 2005 4:59 AM
@To: Oliver Jowett
@Cc: Silvio Bierman; PostgreSQL JDBC
@Subject: Re: FW: [JDBC] PreparedStatement#setString on non-string
@parameters
@
@
@Does this new stuff added in 8.0 driver adds to performance ?. I had
@this setString() problem and I tested 7.4.x driver which works and
@performs better than 8.0 driver.
@
@
@
@On Wed, 09 Mar 2005 10:47:34 +1300, Oliver Jowett
@<oliver@opencloud.com> wrote:
@> Silvio Bierman wrote:
@>
@> > Either the JDBC drivers for the databases I mentioned earlier do the
@> > conversion or the database backends do it on the server side.
@Any way, this
@> > works in all cases. PostgreSQL is the first database to break our
@> > application due to this behaviour. We have had problems on
@earlier versions
@> > of MySQL because of lack of subselect support etc. but never
@these issues.
@>
@> I'd suggest using CAST in your SQL -- that in theory should work
@> everywhere and reflects your application's intent (to interpret a string
@> as a numeric value).
@>
@> The problem with reverting to the old way of doing parameters (direct
@> text substitution into the query) is that we cannot take advantage of
@> most of the new stuff in the V3 protocol -- that means no server-side
@> prepared statement reuse, no low-overhead transfer of large parameters,
@> and reduced support for cursor-based resultsets.
@>
@> -O
@>
@> ---------------------------(end of broadcast)---------------------------
@> TIP 9: the planner will ignore your desire to choose an index
@scan if your
@>       joining column's datatypes do not match
@>
@
@
@--
@rgds
@Antony Paul
@http://www.geocities.com/antonypaul24/
@
@---------------------------(end of broadcast)---------------------------
@TIP 3: if posting/reading through Usenet, please send an appropriate
@      subscribe-nomail command to majordomo@postgresql.org so that your
@      message can get through to the mailing list cleanly


Re: FW: PreparedStatement#setString on non-string parameters

From
Antony Paul
Date:
The performance difference I noticed when it is scanning a table of
150k records with a long query containing a lot of or conditions. The
difference is around 10-12 seconds. So I think it is not the time
taken to prepare the statement. I wasnt interested in further
investigation as we dropped the plan of upgrading still 8.x becomes
9-12 months old.

rgds
Antony Paul


On Wed, 9 Mar 2005 10:08:10 +0100, Silvio Bierman
<sbierman@jambo-software.com> wrote:
> Hello Antony,
>
> I honestly don't know. Oliver Jowett told me that the 8.0 driver is the
> first to use server side prepared statements, which is good I guess. I do
> not know the internals of PostgreSQL server statement caching but usually
> using server side prepared statements is faster than emulating prepared
> statements in the JDBC driver. In cases where statements are really
> exectuted once only and the PreparedStatement is used for parameter
> substitution convenience only (which is a very good reason, BTW) emulated
> statements are usually faster.
>
> I just tested with the 7.4 driver and that actually works fine. It also
> fixed the setString error I got when using the James mail server with the
> 8.0 driver...
>
> I can not say anything about performance in comparison to the 8.0 driver
> yet. Oliver mentioned poor blob-performance in the pre-8 protocol. I am not
> very happy with that since we use blobs quite frequently, be it that they
> are are never very large.
>
> I will be doing some tests with the 8.0 driver in combination with a
> implicit cast versus the 7.4 driver. I will keep you posted...
>
> Regards,
>
> Silvio Bierman
>
> @-----Original Message-----
> @From: pgsql-jdbc-owner@postgresql.org
> @[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Antony Paul
> @Sent: Wednesday, March 09, 2005 4:59 AM
> @To: Oliver Jowett
> @Cc: Silvio Bierman; PostgreSQL JDBC
> @Subject: Re: FW: [JDBC] PreparedStatement#setString on non-string
> @parameters
> @
> @
> @Does this new stuff added in 8.0 driver adds to performance ?. I had
> @this setString() problem and I tested 7.4.x driver which works and
> @performs better than 8.0 driver.
> @
> @
> @
> @On Wed, 09 Mar 2005 10:47:34 +1300, Oliver Jowett
> @<oliver@opencloud.com> wrote:
> @> Silvio Bierman wrote:
> @>
> @> > Either the JDBC drivers for the databases I mentioned earlier do the
> @> > conversion or the database backends do it on the server side.
> @Any way, this
> @> > works in all cases. PostgreSQL is the first database to break our
> @> > application due to this behaviour. We have had problems on
> @earlier versions
> @> > of MySQL because of lack of subselect support etc. but never
> @these issues.
> @>
> @> I'd suggest using CAST in your SQL -- that in theory should work
> @> everywhere and reflects your application's intent (to interpret a string
> @> as a numeric value).
> @>
> @> The problem with reverting to the old way of doing parameters (direct
> @> text substitution into the query) is that we cannot take advantage of
> @> most of the new stuff in the V3 protocol -- that means no server-side
> @> prepared statement reuse, no low-overhead transfer of large parameters,
> @> and reduced support for cursor-based resultsets.
> @>
> @> -O
> @>
> @> ---------------------------(end of broadcast)---------------------------
> @> TIP 9: the planner will ignore your desire to choose an index
> @scan if your
> @>       joining column's datatypes do not match
> @>
> @
> @
> @--
> @rgds
> @Antony Paul
> @http://www.geocities.com/antonypaul24/
> @
> @---------------------------(end of broadcast)---------------------------
> @TIP 3: if posting/reading through Usenet, please send an appropriate
> @      subscribe-nomail command to majordomo@postgresql.org so that your
> @      message can get through to the mailing list cleanly
>
>


--
rgds
Antony Paul
http://www.geocities.com/antonypaul24/

Re: FW: PreparedStatement#setString on non-string parameters

From
Oliver Jowett
Date:
Antony Paul wrote:
> The performance difference I noticed when it is scanning a table of
> 150k records with a long query containing a lot of or conditions. The
> difference is around 10-12 seconds. So I think it is not the time
> taken to prepare the statement. I wasnt interested in further
> investigation as we dropped the plan of upgrading still 8.x becomes
> 9-12 months old.

There are some performance implications of pushing parameter management
to the server side: under 7.4.x, a prepared statement does not gain the
benefit of knowing the concrete parameter values used in a particular
query execution, which can affect the plan chosen (if a concrete value
for a parameter isn't known, the index selectivity estimates may not be
as good).

In theory this should not be an issue if you set prepareThreshold=0
(either globally on the connection, or via setPrepareTheshold on the
statement in question) to prevent reuse of server statements, and use an
8.0.x server. 8.0 defers the planning of unnamed (not reused) statements
until actual parameter values are available, which may improve the plan
chosen for that particular execution.

If it's not that, I'd need a testcase to be able to investigate this
further.

-O

Re: FW: PreparedStatement#setString on non-string parameters

From
Oliver Jowett
Date:
Silvio Bierman wrote:

> I can not say anything about performance in comparison to the 8.0 driver
> yet. Oliver mentioned poor blob-performance in the pre-8 protocol. I am not
> very happy with that since we use blobs quite frequently, be it that they
> are are never very large.

The protocol actually changed in 7.4, but the JDBC driver was not
updated to use all the features of the new protocol until the 8.0
release cycle.

-O

Re: FW: PreparedStatement#setString on non-string parameters

From
Guillaume Cottenceau
Date:
"Silvio Bierman" <sbierman 'at' jambo-software.com> writes:

> Either the JDBC drivers for the databases I mentioned earlier do the
> conversion or the database backends do it on the server side. Any way, this
> works in all cases. PostgreSQL is the first database to break our
> application due to this behaviour. We have had problems on earlier versions

But you understand it's not breaking the JDBC spec, though.

> of MySQL because of lack of subselect support etc. but never these issues.
>
> I really love what I have seen of PostgreSQL until now and I am seriously
> considering making it our preferred database backend. The ability to run on
> both Linux and Windows is great, performance is excellent (I tried a quite
> large database) even when I am still inserting my GUID keys as varchar data
> and the admin tool is very good also.
>
> Telling my users to use an old JDBC driver is not an option though...

But telling the developers to follow the JDBC specs is definitely
an option :). Understand me: I know that there can be a real pile
of existing code that did run before, and is broken now by this
change. However, you cannot ask developers implementing a
specification to intentionally break the specification because
others drivers and old version did so.

--
Guillaume Cottenceau