FW: Question about the postgres resultset implementation

I wrote Peter about the JDBC driver implementation of findColumn and he
suggested I go here for help.

I know the implementation that the Postgres driver uses (iterating through
an array, doing case insensitive searches on each field name) is common and
probably came from an old reference driver, but it's slow.

I'm using an ORM tool that currently asks for all values by their column
name (Hibernate) and I'm actually pestering both sides to see if I can get
some improvements. Hibernate should really use indexes, but the postgres
driver should really do a better job than it's doing now.

I did some tinkering of my own and found that one of the problems with
making the driver less cumbersome is the fact that findColumn() is currently
case insensitive. I don't know if this is required by the jdbc spec, but it
seems all the field names come back lower case (in the fields[] array), and
so I could replace the implementation without understanding how to prevent
the case of the field names from changing (changing from the case they were
sent in as).

At any rate, if case insensitivity could be thrown out then a very fast
implementation could be worked out. As is, the following code was a marked
improvement:

int i;

final int flen = fields.length;
final String lowerCaseColumnName = columnName.toLowerCase();

for (i = 0 ; i < flen; ++i)
if ((fields[I].getName().hashCode() == lowerCaseColumnName.hashCode()) &&
(fields[i].getName().equalsIgnoreCase(columnName)))
return (i + 1);

The int comparisons to hashCode() values are much faster than case
insensitive String compares. The use of a HashMap would be even better,
especially for very wide resultsets.

Anyways, if we could make a change to the driver I'd be very pleased. And
I'm sure I'm not the only one. Actually, the level 4 Oracle driver has a
very similar implementation that's causing Hibernate users some pain, so
speeding up the Postgres implementation would actually make it a much more
performant option with that particular tool. :)

Thanks,
Phill

P.S. I'm not subscribed to the list (nor do I know how to be), so please
include me in any replies.

------ Forwarded Message
From: Peter Mount <peter(at)retep(dot)org(dot)uk>
Reply-To: <peter(at)retep(dot)org(dot)uk>
Date: Wed, 13 Oct 2004 08:44:20 +0100
To: "Tornroth, Phill" <ptornroth(at)intellidot(dot)net>
Subject: Re: Question about the postgres resultset implementation

Tornroth, Phill wrote:

>I noticed that getting values from the Postgres JDBC ResultSet by column
>name was expensive. I looked at the implementation, and findColumn(String)
>does the following:
>
> int i;
>
> final int flen = fields.length;
> for (i = 0 ; i < flen; ++i)
> if (fields[i].getName().equalsIgnoreCase(columnName))
> return (i + 1);
>
>
>If you're getting results by name for a query with 30 columns and a hundred
>rows, you'll do 4650 case insensitive string comparisons. I believe it would
>be much, much faster to use a Hashtable for this task. Is there a reason the
>implementation is as it is?
>
>
I think that was a legacy from the original two drivers, and as it's
always worked it's stayed there. I agree with you a Map of some sort
(HashMap would be ideal, Hashtable would be overkill as it's syncronized
and technically only one thread should access a ResultSet) would help.

NB: I've not been part of the drivers development for some three years
now, so it's best to ask on the jdbc list.

Peter

>
>Phill
>
>

------ End of Forwarded Message