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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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