Re: FW: Question about the postgres resultset implementation - Mailing list pgsql-jdbc

Phill,

this is largely because postgres is case insensitive.
all column and table names are folded into lower case unless you
surround them with quotes.

Dave
On Wed, 2004-10-13 at 11:04, Tornroth, Phill wrote:
> 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@retep.org.uk>
> Reply-To: <peter@retep.org.uk>
> Date: Wed, 13 Oct 2004 08:44:20 +0100
> To: "Tornroth, Phill" <ptornroth@intellidot.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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com