Skip site navigation (1) Skip section navigation (2)

Re: FW: Question about the postgres resultset implementation

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: "Tornroth, Phill" <ptornroth(at)intellidot(dot)net>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: FW: Question about the postgres resultset implementation
Date: 2004-10-13 20:22:53
Message-ID: 1097698973.7985.375.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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(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


In response to

pgsql-jdbc by date

Next:From: Tornroth, PhillDate: 2004-10-13 20:30:37
Subject: Re: FW: Question about the postgres resultset
Previous:From: Andrew SykesDate: 2004-10-13 18:00:26
Subject: odd insert problem, textarea \n replaced with <br> gives ^M !

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group