Обсуждение: Prepared Statements: Inefficient Type Conversion?

I have a table "upids" that has approx 40 million rows.  One column is
named "upid" and is of type NUMERIC.


Both of the following queries (ran as a statement) execute in equivalent
time, 32 milliseconds:


    select vdi_id, leaf_category_id, vdi_key1, vdi_key2, vdi_key3 from
upids where upid = 12239984;

    select vdi_id, leaf_category_id, vdi_key1, vdi_key2, vdi_key3 from
upids where upid = '12239984';

(the subtle difference is that the value being passed is a number in one
case, a string in the other case).

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?


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.


Thanks for any pointers that may help me around this.

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

Kris Jurka wrote:
>> 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.
FWIW, that didn't seem to make a difference - actually, it takes even
longer to execute.

Thanks anyway.  We'll try to get the datatype of the column changed.

On Mon, 16 Apr 2007, James House wrote:

>> You can try adding the url parameter stringtype=unspecified which will pass
>> the String parameter untyped instead of as a String.
>
> FWIW, that didn't seem to make a difference - actually, it takes even longer
> to execute.
>

That's odd I was expecting to see something like this:

jurka=# CREATE TABLE test (a numeric);
CREATE TABLE
jurka=# CREATE INDEX test_idx_a ON test(a);
CREATE INDEX
jurka=# EXPLAIN SELECT * FROM test WHERE a = '1'::text;
                       QUERY PLAN
------------------------------------------------------
  Seq Scan on test  (cost=0.00..29.65 rows=7 width=32)
    Filter: ((a)::text = '1'::text)
(2 rows)

Get changed to something like this:

jurka=# EXPLAIN SELECT * FROM test WHERE a = '1'::unknown;
                                QUERY PLAN
-------------------------------------------------------------------------
  Bitmap Heap Scan on test  (cost=4.30..14.45 rows=7 width=32)
    Recheck Cond: (a = 1::numeric)
    ->  Bitmap Index Scan on test_idx_a  (cost=0.00..4.30 rows=7 width=0)
          Index Cond: (a = 1::numeric)
(4 rows)

Perhaps you have a JDBC driver that is too old to understand the
stringtype URL parameter?  Looks like it is only available in 8.2 and on.
So if you don't have an 8.2 driver it won't do anything.

Kris Jurka

Kris Jurka wrote:
> Perhaps you have a JDBC driver that is too old to understand the
> stringtype URL parameter?  Looks like it is only available in 8.2 and
> on. So if you don't have an 8.2 driver it won't do anything.

That's it we're using 8.1.4 database, so I'm using the 8.1.4 driver.