[postgis-users] Boosting JDBC performance

[david.garnier at etudier-online.com]

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