Обсуждение: FW: PreparedStatement#setString on non-string parameters
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
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
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/
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
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/
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
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
"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