[postgis-users] Boosting JDBC performance

[Paul Ramsey]

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
     \_