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

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/