this is largely because postgres is case insensitive.
all column and table names are folded into lower case unless you
surround them with quotes.
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
> 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()) &&
> 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. :)
> 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.
> ------ 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
519 939 0336
ICQ # 14675561
In response to
pgsql-jdbc by date
|Next:||From: Tornroth, Phill||Date: 2004-10-13 20:30:37|
|Subject: Re: FW: Question about the postgres resultset|
|Previous:||From: Andrew Sykes||Date: 2004-10-13 18:00:26|
|Subject: odd insert problem, textarea \n replaced with <br> gives ^M !|