Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statements: Inefficient Type Conversion?

From: Kris Jurka <books(at)ejurka(dot)com>
To: James House <jhouse(at)part(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Prepared Statements: Inefficient Type Conversion?
Date: 2007-04-16 16:53:55
Message-ID: Pine.BSO.4.64.0704161250550.24838@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Mon, 16 Apr 2007, James House wrote:

> I have a table "upids" that has approx 40 million rows.  One column is named 
> "upid" and is of type NUMERIC.
>
> Now, if I execute the following PreparedStatement, the query time is a minute 
> and a half:
>
>     pstmt = conn.prepareStatement(query);
>     pstmt.setString(1, upid);
>     rs = pstmt.executeQuery();
>
> But with this prepared statement execution it is once again approx 30 
> milliseconds:
>
>     pstmt = conn.prepareStatement(query);
>     pstmt.setLong(1, Long.parseLong(upid));
>     rs = pstmt.executeQuery();
>
> It seems that using the prepared statement to pass the value as a string 
> causes all of the 40 million upid values in the table to convert to string, 
> rather than the string parameter being converted to a number.   Can't the 
> driver be smarter than that?

You can try adding the url parameter stringtype=unspecified which will 
pass the String parameter untyped instead of as a String.

> My problem is that my application code thinks of upid as a string, but the 
> database has been designed for it to be an integer - which in this particular 
> instance of the application the database is coincidentally correct in that 
> all values of upid can be represented as a number, but my application code 
> also has to work in other instances where it is truly a string - hence my 
> prepared statement needs to use setString().  Also, this works fine on Oracle 
> (no performance penalty), for which the application was originally made.
>

You could also convert the upid column to a text type which sounds like it 
might be more correct for your application.

Kris Jurka

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2007-04-16 18:33:43
Subject: Re: Bug in timezone-parsing?
Previous:From: Kris JurkaDate: 2007-04-16 16:45:05
Subject: Re: How to modify my class inherited from java.sql.Array

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group