Thread: JDBC driver build 215 slow when index contains column of type BIGINT

Hello!

I use PostgreSQL 7.4.6 and the JDBC-driver build 215. I have
a table of the form

create table business_case (
  bc_id BIGINT PRIMARY KEY,
  ...
);

To update a row, I use a PreparedStatement of the form

update business_case set ... where bc_id=?

I use setLong() to set the value of bc_id, but the update
is slow, as the index on bigint does not get used.

Changing the JDBC-driver to build 310 increases the speed dramatically:
160 updates with build 215 consumed more than 10 minutes, with build 310
just about 3 seconds.

Have I to do a typecast to ::bigint with build 215 in the PreparedStatement
to get the same performance as with build 310? That would be bad, as I
had to
form the PreparedStatement incompatible to other databases.

Regards,
Roland.

--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com

Roland Walter schrieb:

...

>
> I use setLong() to set the value of bc_id, but the update
> is slow, as the index on bigint does not get used.
>
> Changing the JDBC-driver to build 310 increases the speed dramatically:
> 160 updates with build 215 consumed more than 10 minutes, with build 310
> just about 3 seconds.

...

Forgot to mention that the table contains about 1.5 million rows.


--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com

Roland Walter wrote:
> I use PostgreSQL 7.4.6 and the JDBC-driver build 215. [...]

> Have I to do a typecast to ::bigint with build 215 in the PreparedStatement
> to get the same performance as with build 310?

Against a 7.4.x server, yes.

> That would be bad, as I
> had to
> form the PreparedStatement incompatible to other databases.

Why can't you use build 310? It's intended to work against 7.4.x servers
as well as 8.0.x.

-O

Oliver Jowett schrieb:

> Roland Walter wrote:
>
>> I use PostgreSQL 7.4.6 and the JDBC-driver build 215. [...]
>
>
>> Have I to do a typecast to ::bigint with build 215 in the
>> PreparedStatement
>> to get the same performance as with build 310?
>
>
> Against a 7.4.x server, yes.
>
>> That would be bad, as I had to
>> form the PreparedStatement incompatible to other databases.
>
>
> Why can't you use build 310? It's intended to work against 7.4.x
> servers as well as 8.0.x.
>
Will use it now.

I wanted to avoid finding the places in the application where setString
is used for BIGINT values.

But im just doing that now.

--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com