Re: Prepared Statements: Inefficient Type Conversion?

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