[postgis-users] Boosting JDBC performance

[Rob Hranac]

If you are willing to open source this work, I would be willing to help
out with this as well...have had similar thoughts for a while.  I think
that your plan is right on target.

Rob Hranac

> -----Original Message-----
> From: postgis-users-admin at postgis.refractions.net 
> [mailto:postgis-users-admin at postgis.refractions.net] On 
> Behalf Of Martin Davis
> Sent: Tuesday, June 18, 2002 7:31 PM
> To: postgis-users at postgis.refractions.net
> Subject: RE: [postgis-users] Boosting JDBC performance
> 
> 
> Re: adding WKB support to JTS - I don't see that it would be 
> too difficult to do this (although I haven't spent too much 
> time looking at the WKB spec).  Certainly you need somewhere 
> for the WKB to land once you read it, and JTS offers a good 
> Java object model to aim at.
> 
> Martin Davis, Senior Technical Specialist
> Vivid Solutions Inc.
> Suite #1A-2328 Government Street   Victoria, B.C.   V8T 5G5
> Phone: (250) 385 6040    Fax: (250) 385 6046
> EMail: mbdavis at vividsolutions.com  Web: www.vividsolutions.com
> 
> 
> > -----Original Message-----
> > From: Paul Ramsey [mailto:pramsey at refractions.net]
> > Sent: Tuesday, June 18, 2002 3:45 PM
> > To: postgis-users at postgis.refractions.net
> > Subject: Re: [postgis-users] Boosting JDBC performance
> > 
> > 
> > David,
> > I like your proposal. Moving to using WKB was something I
> > thought would
> > be wise in the future for the JDBC connectivity. If you 
> find good WKB
> > reading code for Java it would be good to share it with the GeoTools
> > people, since they could probably do better jetisoning the current
> > org.postgis objects and just instantiating JTS objects directly from
> > WKB.
> > 
> > Martin Davis,
> > Do you think that a WKB constructor for JTS geometries would be a 
> > reasonable addition to the JTS library?
> > 
> > Paul
> > 
> > david.garnier at etudier-online.com wrote:
> > > 
> > > Hello,
> > > 
> > > Here is a proposition to boost JDBC performance.
> > > 
> > > I'm building a data-intensive Java application using
> > PostGIS as the backend.
> > > Basically my application pulls geometries from PostGIS.
> > It's almost done, but I
> > > ran into the following problem: the performance of the
> > Postgres' JDBC driver is
> > > very, very bad.
> > > 
> > > I used OptimizeIt to mesure the performance, and the
> > application is spending
> > > anywhere from 30% to 90% of its time in the PostgreSQL
> > driver. Also, it
> > > allocates a huge number of Strings. I looked at the source
> > (I love open source
> > > applications!) and the reason of this performance
> > bottleneck is simple: the
> > > PostgreSQL driver parse everything into Strings then parse
> > it back into the
> > > desired type, event if internally each record is stored as
> > a byte[][]. For
> > > example, if you do a getBytes(), it will parse the array
> > into a String then
> > > back into a byte[]. This problem is known by the PostgreSQL
> > community, and it
> > > seems that it isn't easy to fix. See this recent thread for
> > comments on this
> > > problem:
> > > 
> > > http://fts.postgresql.org/db/mw/msg.html?mid=1070659
> > > 
> > > Since I'm looking for a quick fix, I'm came up with the
> > following plan:
> > > 1) Retrieve the data in WKB format using the AsBinary
> > function instead of the
> > > AsText function.
> > > 2) Get the data from the subclassed ResultSet, using a
> > getRaw() method
> > > returning a byte[], for example.
> > > 3) Parse this byte[] into a geometry, probably a simple
> > holder for floats
> > > coordinates.
> > > 
> > > It is possible to subclass the PostgreSQL driver to get
> > direct access to the
> > > internal byte array. From here its a simple matter of
> > parsing the array into a
> > > geometry using the WKB specification.
> > > 
> > > So before I go ahead and code this, my final questions are:
> > > * Did someone already solved this problem?
> > > * If not, does someone know of a Java class that would read
> > WKB? I found some
> > > open source Java WKB writers, but no readers.
> > > * Does someone see a problem with this plan?
> > > 
> > > Best regards,
> > > David Garnier
> > > 
> > > _______________________________________________
> > > postgis-users mailing list postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > -- 
> >       __
> >      /
> >      | Paul Ramsey
> >      | Refractions Research
> >      | Email: pramsey at refractions.net
> >      | Phone: (250) 885-0632
> >      \_
> > 
> > _______________________________________________
> > postgis-users mailing list postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> 
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>