[postgis-users] Boosting JDBC performance

[David Garnier]

Yep. 
I just need to finish something, and I will work on that. I'm only looking 
for a quick fix, not a redesign of the PostgreSQL driver, so I well everyone 
know.  I'm really looking for some kind of a simple WKB reader.

Best regards,
David Garnier

Le Mercredi 19 Juin 2002 19:14, Rob Hranac a écrit :
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users