Re: FW: Question about the postgres resultset implementation

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(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
>
>
> ---------------------------(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