Re: JDBC slow performance on resultSet iteration? - Mailing list pgsql-jdbc

Just an quick info update: i'm using the postgresql-9.2-1003-jdbc4 driver

On 2013/11/05, at 16:09, Rui Pedro Leal wrote:

> Hello everyone,
>
> I'm having slow JDBC performance on iterating a resultSet obtained from a somewhat simple query.
>
> The query, although using PostGIS functions, is pretty straight forward:
>
> SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons, countfatals, countdrunks, density
> FROM accidents_5
> WHERE (_geometry && ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603, 4326) )
> ORDER BY _pk_id
>
> The results are the following:
> - Executing the query directly from pgAdmin: ~2807ms
> - Executing from JVM + JDBC: 4184ms
>
> The code i'm executing is pretty much standard:
>
> -------------------- code --------------------
>
>       public static void main(final String[] args) throws Exception {
>
>               String sql = "SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons, countfatals, countdrunks, density";
>               sql += " FROM accidents_5";
>               sql += " WHERE (_geometry && ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603, 4326) )";
>               sql += " ORDER BY _pk_id";
>
>               System.out.println(sql);
>
>               Long time = System.currentTimeMillis();
>               try {
>                       Connection connection = DataStoreInfo.getDataStores().get(0);
>                       connection.setAutoCommit(false);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - connection in "
>                                       + (System.currentTimeMillis() - time) + "ms.");
>
>                       Statement st = connection.createStatement(
>                                       ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
>                                       ResultSet.CLOSE_CURSORS_AT_COMMIT);
>                       st.setFetchSize(250);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - statement in " + (System.currentTimeMillis() - time)
>                                       + "ms.");
>
>                       ResultSet resultSet = st.executeQuery(sql);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - done in " + (System.currentTimeMillis() - time)
>                                       + "ms.");
>
>                       GeometryFactory geofact = new GeometryFactory(new PrecisionModel(),4326);
>                       WKBReader wkbReader = new WKBReader(geofact);
>
>                       String id;
>                       String spatialType;
>                       Geometry geometry;
>                       String attribute;
>                       Long count;
>                       String reference;
>                       Map<String, Double> properties;
>                       String granularSynthString = "GranularSynthesis";
>
>                       while (resultSet.next()) {
>                               id = resultSet.getString(1);
>                               spatialType = resultSet.getString(2);
>
>                               // geometry = wkbReader.read(resultSet.getBytes(3)); // ignored the WKBReader and the results are about the same.
>                               attribute = resultSet.getString(4);
>                               count = resultSet.getLong(5);
>                               reference = resultSet.getString(6);
>
>                               properties = new HashMap<String, Double>();
>                               Double aux = resultSet.getDouble(7);
>                               properties.put("countpersons", aux);
>                               aux = resultSet.getDouble(8);
>                               properties.put("countfatals", aux);
>                               aux = resultSet.getDouble(9);
>                               properties.put("countdrunks", aux);
>                               aux = resultSet.getDouble(10);
>                               properties.put("density", aux);
>                       }
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - done & iterated in "
>                                       + (System.currentTimeMillis() - time) + "ms.");
>
>                       resultSet.close();
>                       st.close();
>                       connection.commit();
>                       connection.close();
>               } catch (SQLException exception) {
>                       exception.printStackTrace();
>               // } catch (ParseException exception) {  // ignored from WKBreader
>               // exception.printStackTrace();
>               }
>
>               System.out.println("[End " + Thread.currentThread().getId()
>                               + "] - done in " + (System.currentTimeMillis() - time) + "ms.");
>       }
>
> -------------------- end code --------------------
>
> Although i'm executing this on slow MacbookPro (2.6 core duo, 2Gbs RAM but SSD) and have a 9.1.2 postgres, i've also tested this on a recent retina MBP and the ratio between pgAdmin and JDBC execution is similar.
>
> Is this expected? Can someone point if i'm doing something terrible wrong?
>
> I'm not concerned about the query performance per-se (i know it CAN be optimized), but the differences just using JDBC and iterating the resultSet are really annoying.
>
> Thanks in advance for any help.
>
> Kind regards,
>
> Rui Leal
>
>



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc