[postgis-users] Boosting JDBC performance

[Martin Davis]

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
>